Sending a list to stored proc (almost got it!!)
-
Hello!
Here is a bit of code that I'm really proud of
CREATE PROCEDURE usp_BuddyTest
DECLARE @List varchar(8000)
DECLARE @memID numeric(9, 0)
DECLARE @Username varchar(18)
DECLARE @tmp int
AS
SET @List = ' 1 2 3 4'
WHILE @List != ''
BEGIN
SET @tmp = CHARINDEX (' ' , @List, 1) + 1
SET @memID = CAST(LEFT(LTRIM(@List), @tmp) AS numeric(9, 0))
SET @List = SUBSTRING(LTRIM(@List), @tmp, LEN(@List))
SELECT Username FROM memUsername WHERE memID = @memID
END
Now, I this really doesn't work because when I run it through query analyzer I get like 10 result sets. How can I put all the Usernames into a single result set? Is it done with a temp table or a cursor?
Thanks!
/Tomi
-
You could dump the SELECT into a temporary table and return the temporary table in one select statement. Alternately, you could dump the list into a temp table and then use JOIN to match up in a single SELECT statement.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Thanks Brian!
Hrmm... How do I dump the Usernames to a temp table? I've never used temp tables in my whole life
/Tomi
-
It'll probably be better to dump the values. Something to the effect of:
CREATE PROCEDURE usp_BuddyTest
DECLARE @List varchar(8000)
DECLARE @memID numeric(9, 0)
DECLARE @Username varchar(18)
DECLARE @tmp int
AS
SET @List = ' 1 2 3 4'
CREATE TABLE #MyList (MemID int)
WHILE @List != ''
BEGIN
SET @tmp = CHARINDEX (' ' , @List, 1) + 1
SET @memID = CAST(LEFT(LTRIM(@List), @tmp) AS numeric(9, 0))
SET @List = SUBSTRING(LTRIM(@List), @tmp, LEN(@List))
INSERT INTO #MyList (MemID) VALUES (@memID)
END
SELECT Username
FROM memUsername
JOIN #MyList ON memUserName.memID = #MyList.memIDK. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
THANK YOU!
Just a follow up question. If I run the query like 10 times a second affect the #MyList table? I mean could I get wrong buddys?
/Tomi
-
If you use a single # sign, no. SQL Server will create a unique table name. To see what I'm talking about, create a # table in Query Analyzer and don't drop it immediately. Run a query against sysobjects in the tempdb database and you should see your temporary table along with a lot of underscores and a number attached to it.
This is how SQL Server keeps temporary tables with a single # sign separate. The ## temporary tables are global and would be seen by any conection trying to use it. Avoid these.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
AAaahh.... I seeeeeee
Thanks Brian!
/Tomi
-
I would avoid the temporary table if possible. Just wrote an answer to a similar question on another forum, I'll cut & paste it here and you can change it for your query.
-- Split array and return all orders with orderid's from array
-- Results are equal to running EXEC ('SELECT OrderID FROM Orders WHERE OrderID IN (' + @indata + ')')
USE Northwind
GO
-- Do this part (i.e. creation of numbers table and index) only once
IF EXISTS ( SELECT * FROM sysobjects WHERE [name] = 'NUMBERS' )
DROP TABLE NUMBERS
GO
CREATE TABLE NUMBERS (n int identity(1,1), foo int)
GO
INSERT INTO NUMBERS (foo)
SELECT a.id
FROM sysobjects a, sysobjects b
GO
CREATE CLUSTERED INDEX ixcNUMBERS ON NUMBERS (n)
GO
-- This is the actual split part
DECLARE @indata varchar(100), @sep char(1)
SET @indata = '10248,10263,10569,11039'
SET @sep = ','
SELECT OrderID
FROM Orders o
INNER JOIN (SELECT LEN(LEFT(@indata,n)) - LEN(REPLACE(LEFT(@indata,n),@sep,'')) + 1 AS i
, CAST(SUBSTRING(@indata,n,CHARINDEX(',',@indata+@sep,n) - n) AS int) AS j
FROM NUMBERS
WHERE n < LEN(@indata)
AND SUBSTRING(@sep+@indata,n,1) = @sep) foo
ON o.OrderID = foo.j
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
tackar...
I'll try it out. So far I've no idea how the query works. But I'll get the hang of it
/Tomi
-
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply