Sp For retrieval of Data for .NEt

• rshetty12

  SSC Enthusiast

  Points: 104

  More actions

  January 21, 2003 at 8:47 am

  #58979

  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:46 am

  #446888

  http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=9419&FORUM_ID=5&CAT_ID=1&Forum_Title=Administration&Topic_Title=Sp+For+retrieval+of+Data

  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

• max_star

  SSC Journeyman

  Points: 81

  More actions

  January 21, 2003 at 4:52 pm

  #446925

  You still can use a table name as a variable in your SP. All you need to do is to execute your statement using exec():

  declare @chStmt varchar(255)

  @Table varchar(50)

  select @Table = 'Table'

  select @chStmt = 'insert into ' + @Table..

  exec(@chStmt)

• gopi

  SSC Rookie

  Points: 28

  More actions

  January 21, 2003 at 6:40 pm

  #446935

  Hi rshetty,

  I think Max_star has given the right suggestion. However I was wondering about the stmt from BOL Microsoft as follows

  "However, table may not be used in the following statements:

  INSERT INTO table_variable EXEC stored_procedure

  SELECT select_list INTO table_variable statements.

  table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined

  I would use one permanent table to store the results if I can know the table structure before hand to reduce the usage of tempspace.

  Here what Max_star says is the best so far.

  Cheers Gopi