July 18, 2009 at 2:50 am
I am trying to write a procedure to do the following:
SELECT * FROM table1
WHERE PROGRAM_ID IN (2,3,5,10)
This is my Procedure:
ALTER PROCEDURE [dbo].procname
-- Add the parameters for the stored procedure here
@program_id varchar
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM QUICK_ANALYSIS WHERE PROGRAM_ID IN (@program_id)
When I execute the procedure
procname @program_id IN '2,5'
all I get are program ids in 2. Can someone please help me and point what I am doing wrong.
July 18, 2009 at 3:03 am
July 18, 2009 at 3:45 am
Here is a way to accomplish your task. First, you need the following function (or one similar):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by a.N) as N
from
a3 as a
cross join a2 as b),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit
Once you have that, then your stored procedure, and the call to it would look like this:
ALTER PROCEDURE [dbo].procname
-- Add the parameters for the stored procedure here
@program_id varchar(8000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
qa.*
FROM
QUICK_ANALYSIS qa
inner join dbo.DelimitedSplit(@program_id, ',') ds
on (qa.PROGRAM_ID = ds.Item)
END -- Procedure dbo.procname
GO
exec dbo.procname '2,5' -- execute procedure
Hope this helps.
July 18, 2009 at 8:12 am
Thanks, this works
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply