using IN operator in a stored procedure
-
Hi
I'm having a problem using the IN operator within a stored procedure (I'm assuming that you can). I'm working with a list box on an access front-end, and if multiple records are selected from the list then I get a string something like ('99', '100', '101') where each number represents attribute ID (PK) from tbl. I then want to run the sp below inserting the string after the IN operator; exec spGoToMultiRec STRINGHERE. I've tried declaring variable idList as int, char, varchar, etc but keep getting errors.
CREATE PROCEDURE dbo.spGoToMultiRec @idList int
AS
SELECT *
FROM tbl
WHERE ID IN (@idList)
GO
Any help would be greatly appreciated.
Thanks
Brian
-
The IN clause will not take a comma delimited list even though that is the way it looks when you type in a list manually.
There are a couple of ways I have dealt with this but for your purposes you would probably want to use a function that returns a Table type variable.
First of all you would pass your list in as a string (varchar). Then create a function as follows to "split" the id's into actual rows in a table variable and then select all the rows as your function return value:
CREATE FUNCTION Split
(@List varchar(1000))
RETURNS @Results table
(Item varchar(1000))
AS
begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Delim char(1)
declare @Word varchar(1000)
set @IndexStart = 1
set @IndexEnd = 0
set @Length = len(@List)
set @Delim = ','
while @IndexStart <= @Length
begin
set @IndexEnd = charindex(@Delim, @List, @IndexStart)
if @IndexEnd = 0
set @IndexEnd = @Length + 1
set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1
INSERT INTO @Results
SELECT @Word
end
return
end
Then rewrite your IN clause as follows:
CREATE PROCEDURE dbo.spGoToMultiRec @idList varchar(1000)
AS
SELECT *
FROM tbl
WHERE ID IN (SELECT * FROM dbo.Split(@idList))
GO
Hope this helps.
-
PS. You'll probably have to modify the table variable to return the datatype that matches your ID field.
EC
-
Another option is to dynamically build the sql statement.
declare @sql varchar(2000)
set @sql='select * from tbl where id in (' + @idlist +')'
EXEC (@sql)
If the ids are integer, then you will want to remove the qoutes prior to building the string.
Brian
-
If you are using SQL Server 2000 then you can use XML to pass the data in
Making an assumption that your inbound XML looks like this:
<ids>
<id>1001</id>
<id>347</id>
<id>109</id>
<id>203</id>
<id>873</id>
</ids>
Declare your procedure something like
CREATE PROCEDURE dbo.spGoToMultiRec_XML
--I usually append XML to my SP names so I know that I am dealing with XML in the SP
(
@idListXML ntext --I usually append XML to the variable
 
--this is a close parenthesis, but it thinks it is a smilieAS
set nocount on
DECLARE @h int
EXEC sp_xml_preparedocument @h OUTPUT, @idListXML
SELECT *
FROM tbl
WHERE ID IN (SELECT ProductID
FROM OPENXML (@h, ids/id)
WITH (ProductID int))
EXEC sp_xml_removedocument @h
I have switched several procedures that accepted a varchar(XXX) value and created dynamic SQL and have perceived a performance enhancement.
Chris
*disclaimer
Since I do not have SQL Server 2000 at home, I have not tested the actual code provided. You should be able to get it working correctly though.
-
Ever so often the same question with the same answer. Read this
http://www.sommarskog.se/dynamic_sql.html
and
http://www.sommarskog.se/arrays-in-sql.html
and http://www.rac4sql.net/xp_execresultset.asp
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply