January 19, 2010 at 9:12 am
First and foremost, apologies if this has already been answered.
I was wondering if there is a way to split a SP input so the individual sections can be used in a 'in' filter.
As in:
Exec sp_getMyCusts '12,15,25,49' -- The input list is variable.
sp_getMyCusts @input1 as varchar
.
.
.
select CustName, CustAdd from tblCustomer where custID in (@input1)
.
.
.
The custID is of type Int.
Many thanks in advance.
Regards,
Akin
January 19, 2010 at 1:15 pm
I would use the input parameter and a split string function together with a join.
Something like this:
DECLARE @input1 as varchar(50)
SET @input1 = '12,15,25,49'
SELECT * FROM
master..spt_values m inner join dbo.DelimitedSplit(@input1,',') s
ON m.number=s.item
If you don't have a split string table valued function function available yet, search this site for it or have a look at the function I'm using:
create function [dbo].[DelimitedSplit] (
@pString varchar(8000),
@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),
a4 as (select 1 as N
FROM a3 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by N) as N
from
a4),
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
January 19, 2010 at 8:20 pm
Thought I'd throw a different slant on that and also add a couple of optimizations for both VARCHAR(8000) and single character delimiters...
CREATE FUNCTION dbo.DelimitedSplit
/**************************************************************
Purpose:
Split a given string at a given delimiter and return a list
of the split elements (items).
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Does not "trim" elements just in case leading or trailing
blanks are intended.
4. cteTally concept originally by Iztek Ben Gan and
"decimalized" by Lynn Pettis (and others) for a bit of
extra speed and finally redacted by Jeff Moden for a
different slant on readability and compactness.
5. If you don't know how a Tally table can be used to replace
loops, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
**************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values from 1
-- up to 10,000... enough to cover VARCHAR(8000)
WITH
E1(N) AS (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 UNION ALL SELECT 1), --10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
N AS StartPosition,
SUBSTRING(@pDelimiter + @pString, N+1, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply