May 12, 2009 at 9:49 am
I'd like to pass a varchar parameter, and in my where clause, i am looking for values in this comma delimited variable for int values.
It looks like something like:
create procedure ....
@codes varchar(50)
select *
from myTable
where myTable.code in @codes
code column is an integer value. How do I do this?! Thanks. @codes would be something like: '7,8,9'
May 12, 2009 at 9:53 am
Try this proc
Credit goes to Tony on this one!!!
create PROC csv_extract
@csv varchar(500),
@is_allow_nulls char(1) = 'Y',
@is_unique_values char(1) = 'N',
@is_debug char(1) = 'N'
AS
BEGIN
/***
Convert a CSV to a TABLE with data validation
***/
SET @csv = ltrim(rtrim(@csv))
IF PATINDEX( '%[^,0-9]%', @csv ) > 0 -- Checks to make sure input is digits or comma to prevent SQL injection
BEGIN
RAISERROR( 'Injection attempt or invalid data.', 16, 1 )
RETURN
END
-- Initial parse of input to make sure general syntax of the csv is valid
-- check we have commas correct
IF LEFT( @csv, 1 ) ',' -- If not front comma then add one
SET @csv = ',' + @csv
IF RIGHT( @csv, 1 ) = ',' -- If last character is a comma then add a NULL after it
SET @csv = @csv + 'NULL'
SET @csv = REPLACE( @csv, ',,', ',NULL,' ) -- Change ,, to NULL
SET @csv = REPLACE( @csv, ',,', ',NULL,' ) -- Need to do it twice for rest of ,, not caught in first one
-- // initial parse is over.
-- Now the donkey work, convert it from a flat CSV structure to a TABLE
-- Simple string replacing, no need for self joins and loops
DECLARE @sql varchar(max)
SET @sql = REPLACE( @csv, ',', CHAR(13) + CHAR(10) + 'insert #csv_split ( csv_value ) values( ' )
SET @sql = REPLACE( @sql, CHAR(13), ' ); @v' + CHAR(13) )
SET @sql = RIGHT( @sql, LEN( @sql ) - 7 ) + ' ); @v'
SET @sql = REPLACE( @sql, '@V', '
if @@error 0 goto err;' )
-- We now have the SQL that will convert CSV to a TABLE, so exec it.
CREATE TABLE #csv_split (
val_position smallint not null IDENTITY,
csv_value int null CHECK( csv_value between 10 and 20 )
)
IF @is_allow_nulls = 'N'
EXEC( 'ALTER TABLE #csv_split ALTER COLUMN csv_value INT NOT NULL' )
IF @is_unique_values = 'Y'
EXEC( 'CREATE UNIQUE INDEX ncui ON #csv_split( csv_value )' )
SET @sql = 'SET NOCOUNT ON; ' + @sql + ';
goto done;
err:
DECLARE @msg varchar(120);
SET @msg = ''Data Error on value position '' + CAST( ( SELECT COUNT(*) FROM #csv_split ) + 1 AS varchar(5) ) + '', processing terminated.'';
RAISERROR( @msg, 16, 1 );
done:'
IF @is_debug = 'Y'
PRINT @sql
EXEC( @sql )
IF @@ERROR 0
RETURN
SELECT *
FROM #csv_split
drop table #csv_split
END
-- TEST SO
/*
exec csv_extract @csv = ',11,12,13,14,12',
@is_allow_nulls = 'Y',
@is_unique_values = 'N',
@is_debug = 'N'
*/
May 13, 2009 at 9:17 am
OK, this returns me a table of integers. How can I use my in clause with a table?
May 13, 2009 at 9:29 am
This function:
/****** Object: UserDefinedFunction [dbo].[DelimitedSplit] Script Date: 05/13/2009 09:25:06 ******/
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),
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
Used this way:
create procedure ....
@codes varchar(50)
select
*
from
myTable
inner join dbo.DelimitedSplit(@codes,',') ds
on (myTable.code = ds.Item)
where
...
May require some adjustments, but that's the gist of it.
May 13, 2009 at 9:35 am
Hi
Two possibilities:
1.) Create a dynamic SQL statement and execute it.
2.) Use a on-demand split and join the data instead of using an IN statement.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
May 13, 2009 at 2:45 pm
Hi
I don't know the values of your @code and @key variables but you cannot use a list of values within your ISNULL clause.
If this doesn't work please provide a complete sample with your @code and @key variables and their values.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
May 13, 2009 at 3:37 pm
Of course if you're lazy like me, you can just do this:
create procedure ....
@codes varchar(50)
select *
from myTable
where Charindex(','+myTable.code+',', ','+@codes+',') > 0
True, this pretty much forces a table scan, but then a lot of these routines end up doing that anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 13, 2009 at 3:38 pm
Is there a reason you are attempting to use dynamic sql instead of the code (or something similar) that I had provided? It would seem to me that would be easier than trying to write dynamic sql.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply