Sp For retrieval of Data

• rshetty12

  SSC Enthusiast

  Points: 124

  More actions

  January 21, 2003 at 8:46 am

  #58564

  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.

• tcartwright

  SSCertifiable

  Points: 5573

  More actions

  January 21, 2003 at 11:45 am

  #446887

  Hmmm, how many times did you post this one?

  Check http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=9422&FORUM_ID=5&CAT_ID=1&Forum_Title=Administration&Topic_Title=Sp+For+retrieval+of+Data+for+%2ENEt

  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