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.