January 21, 2003 at 8:47 am
Hello ALL:
I am writing a SP to return a set of Rows to a .NET search routinue. The SP accepts the Page no, No:of rows and a few filter paramters.
I have a Declared a variable of the type table. Since I have to change the Where clause at runtime I am using Dynamic SQL(Sp_executeSQL).I am inserting the records into the the Table declared as variable. and then retrieve rows from them as the output of the SQL.
My problem is that in the dynamic SQL it does not recognize the table I have declared as a variable:
Structure
Create Stored proc usp_getsearch
@FilterbyCompanyId Varchar(1000),
@FilterbyGroup Varchar(1000),
@PAgeNo Int,
@StartRow Int,
@EndRow Int
as
BEgin
Declare @SQLstring as nVarchar(4000)
Declare @TableResult as Table (RowId Identity Int,
CompanyId varchar(10),
Groupid varchar(10),
Amt Money)
Select @SQlString= "Insert into @ResultTable
Select * from XYZ"
sp_executesql @sqlstring
End
The error I get Must declare variable @Resulttable.
Appreciate if someone could respond immediately.
January 21, 2003 at 9:00 am
To answer your question the table variable must be decalred inside the dynamic sql. But there is a problem, getting that table variable back out. AFAIK the only way you can do what you are trying to do is with a temp table.
As to the dynamic SQL, you can do WITHOUT it.
See :
How Dynamic SQL Can Be Static SQL
http://qa.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp
How Dynamic SQL Can Be Static SQL Part 2
http://qa.sqlservercentral.com/columnists/rmarda/howdynamicsqlcanbestatic2.asp
Dynamic String Compare (W/O the dynamic sql)
http://qa.sqlservercentral.com/scripts/contributions/632.asp
Does Short Circuiting work with T-SQL AND / OR?
http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=360
Using these techniques I avoid dynamic sql and temp tables as much as I can.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply