May 4, 2004 at 12:54 pm
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
May 4, 2004 at 1:32 pm
Been there done that. Unfortunately, it does not work as easy as doing an IN if you are not using dynamic sql.
I appologize to the creator of this method, I have forgot his name from these forums, but here was a suggestion that I have previously gotten.
declare @t table (
i int
)
insert into @t (i) values (1)
insert into @t (i) values (2)
insert into @t (i) values (3)
insert into @t (i) values (4)
insert into @t (i) values (5)
declare @j-2 varchar(10)
set @j-2 = '1,3,5,'
select * from @t WHERE CharIndex(',' + cast(i as varchar) + ',' , ',' + @j-2) > 0
May 4, 2004 at 2:26 pm
Brian, I replied to your other post but will reply here also.
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
You'll probably have to tweak the function to return the proper data type.
Hope this helps.
May 5, 2004 at 7:29 am
Similar to the above solution, you could create a temp table in a business object, populate it with the entries selected, and write the stored procedure to use the temp table. If it's common I might be inclined to use the function, but the way I've described above works very well for us. It's even fast.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply