CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
1110
1110

Reputation: 6829

Pass a list to the stored procedure doesn't work

When I try this query it works:

SELECT *
FROM tbl_Users
WHERE UserId IN (555, 3695, 8787))

But when I put it in stored procedure it get value only if I pass one ID. If I pass more ID's

I got nothing returned from database:

CREATE PROCEDURE myStoredProcedure
    @m_UserIdList varchar(500)
AS    
    SELECT u.*, p.*
    FROM tbl_Users u 
    INNER JOIN tbl_Bunker b 
    ON u.BunkerId = b.Bunker
    WHERE u.UserId IN (@m_UserIdList)

Upvotes: 2

Views: 327

Answers (6)

Louis Ricci
Louis Ricci

Reputation: 21086

Since it wasn't mentioned, you can use the XML data type.

BEGIN
DECLARE @xml XML
SELECT @xml = '<n>1</n><n>2</n><n>3</n>'

SELECT t.n.value('.','int')  FROM @xml.nodes('*') as t(n)
IF 1 IN (SELECT t.n.value('.','int')  FROM @xml.nodes('*') as t(n))
    PRINT('Yep')
IF 4 NOT IN (SELECT t.n.value('.','int')  FROM @xml.nodes('*') as t(n))
    PRINT('It Works')
END

It's not as efficient as table valued columns but it's easier to setup/use for the "Caller".

If the caller already has an array or list of elements to convert them to XML he just has to do a String.Join with "</n><n>" as the delimiter then if the resulting string is not empty prepend "<n>" and append "</n>"

Upvotes: 1

JohnD
JohnD

Reputation: 14747

Yes, it is treating @m_UserIdList as one single value and comparing that to u.UserId. You need to split out the values and test them separately. Oded beat me to the punch, but he is right -- you should use a table-valued param. Here is the link from Oded:

http://www.sommarskog.se/arrays-in-sql-2008.html

This site offers a bunch of different approaches to this problem:

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

One of the approaches would be to use dynamic SQL, however you should be aware of the dangers of doing this. Read this first:

http://www.sommarskog.se/dynamic_sql.html

If you decide to go that route, something like this could work:

DECLARE @SQL varchar(600)

SET @SQL = 
'SELECT u.*, p.*
FROM tbl_Users u
    INNER JOIN tbl_Bunker b ON u.BunkerId = b.Bunker
WHERE u.UserId IN (' + @m_UserIdList+ ')'

EXEC(@SQL)

Upvotes: 1

ipr101
ipr101

Reputation: 24236

You can't pass a string of values to the stored procedure. The most obvious answer would be to make your whole sql query string dynamic, the two links should take you through the various other alternatives that are available -

http://www.sommarskog.se/arrays-in-sql.html

Parameterize an SQL IN clause

Upvotes: 0

Johnie Karr
Johnie Karr

Reputation: 2822

Shark explained it better than I was going to. If I remember right you can do this, but it's been a long time since I had to do what you are after so I might be wrong.

EXEC('SELECT u.*, p.*
      FROM tbl_Users u 
      INNER JOIN tbl_Bunker b ON u.BunkerId = b.Bunker
      WHERE u.UserId IN (' + @m_UserIdList + ')')

Upvotes: 2

user596075
user596075

Reputation:

That's because your select statement is the equivalent of:

SELECT u.*, p.* 
FROM tbl_Users u  
INNER JOIN tbl_Bunker b ON u.BunkerId = b.Bunker 
WHERE u.UserId IN ('555, 3695, 8787')

You're basically searching to see if UserId is a string that literally matches '555, 3695, 8787', which is not what you want.

Upvotes: 4

Oded
Oded

Reputation: 498914

@m_UserIdList is a VARCHAR(500), not a list of values, so this will not work. You can try and parse the passed in string into a table (and there are plenty of ways of doing so - just search this site).

However, since you are using SQL Server 2008, you should take a look at table valued parameters - these allow you to pass a table of values to a stored procedure.

Upvotes: 1

Related Questions