saving records returned from stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

saving records returned from stored procedure

Author

Topic

kaisdd
Starting Member

17 Posts

Posted - 2004-11-14 : 07:13:08

hey,

for a search tool on my website i have a stored procedured wich uses a list of search words as input params, build a parameterized sql string and execute it with sp_executesql

sp_executesql, returns records found (IDs only)

at a second step i would like to save the record ids as a comma separated list in a search result table. iÂ´m using this table as a cache when the user pages trough his search result.

i have puzzled about the second step for hours but it won't work.
how can i save the result from sp_executesql in a variable or something? couldÂ´t anybody help?

thaaaaaaaaaanks!

kaisdd
Starting Member

17 Posts

Posted - 2004-11-14 : 08:12:16

i fixed it out
i combine the ids in the select
the proc now returns a output param in the form 1,2,3,4 etc...

DECLARE @rtnVal VarChar(1000)
SET @rtnVal = ''
SET @strSQL = ' SELECT @rtnVal = @rtnVal + CONVERT(VARCHAR, NewsID, 12) + "," FROM ' + @strSearchTable + ' WHERE ' + @strSQL + @strSearchFilter
EXEC sp_executesql @strSQL, N'@Search nVarChar(128),@rtnVal VarChar(1000) OUTPUT', @strSearchText, @rtnVal OUTPUT
SELECT @rtnVal

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-14 : 13:17:41

That will give a spare comma at the end - to get rid of it
SET @rtnVal = null
SET @strSQL = ' SELECT @rtnVal = coalesce(@rtnVal + '', '', '''') + CONVERT(VARCHAR, NewsID, 12) FROM '

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kaisdd
Starting Member

17 Posts

Posted - 2004-11-15 : 03:22:14

yes, thank you

kaisdd
Starting Member

17 Posts

Posted - 2004-11-15 : 12:57:02

öhm... there is another question where i stuck

is it somehow posible to build up a dynamic variable name

i split up my search words and save them to variables
@strSearchWord1
@strSearchWord2
@strSearchWord3
etc...

i do some more operations on these variables later and if i use more than 3 searchwords its a lot of ugly an repeating code

is it posible, to do all this in a loop an append the number only?
table vars are not so good, because later in sp_executesql i have to append the variables

EXEC sp_executesql @strSQL, N'@Search1 VarChar(128), @Search2 VarChar(128),@strResultIDs VarChar(8000) OUTPUT, @RecordCount Int OUTPUT', @strSearch1, @strSearch2, @strResultIDs OUTPUT, @RecordCount OUTPUT

thank you in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources