July 28, 2005 at 8:22 am
Got a question. I have a VB.NET app that is sending a comma-separated list of values into a stored procedure. In the SP, the query wants to check a field against that list of values passed in in the list.
I currently do this in another SP using dynamic SQL (I know). I was wondering if there is a way to do it without dynamic SQL and without having to break the list down into a temp table or something.
EXAMPLE:
-- Assume @parm is the parameter passed in
DECLARE @parm varchar(100)
SET @parm = '''ABC'',''XYZ'',''JKL'''
SELECT *
FROM dbo.MyTable
WHERE checkfield IN @parm
Just wanted to check. I keep trying to write better queries and procedures so I wanted to know if there is a way to avoid the dynamic SQL.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 28, 2005 at 9:13 am
I notice in the example that each value contained in the parm is delimited by a single-quote. Could this work for you?
SELECT *
FROM dbo.MyTable
WHERE @parm LIKE '%' + CHAR (39) + checkfield + CHAR (39) + '%'
(I prefer using CHAR (39) when I need the pattern of a single-quote because multiple concatenated single-quotes give me a headache!)
July 28, 2005 at 9:42 am
My apologies, I may not have made my example clear.
Assume this is my procedure:
CREATE PROC usp_CheckList(
@parm varchar(100))
AS
SELECT *
FROM dbo.MyTable
WHERE checkfield IN (@parm)
GO
The value I am passing into the procedure is:
'ABC','XYZ','JKL'
Is there a way to make this work without using dynamic SQL?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 28, 2005 at 11:17 am
So you can't change the code of your procedure?
I guess I don't understand why you need to use the IN <list> approach to get what you want. The method I suggested ought to work, if the goal is to select the rows having a value represented in the parm. But if you're required to use the IN list approach... then no, I don't think there is any way to do that. Or rather, no easy way.
You could, e.g., write a table function that returns a table with the delimited entries parsed out into separate rows, and then your IN statement could test for inclusion in (SELECT * FROM dbo.f_parse (@parm)) or something like that.
Or you could create a temp table and just parse out the values into that, manually.
If I read you correctly, you're dealing with a limitation in T-SQL. It's more a question of binding times (if I'm using the proper term) -- at which point does the code become bound to an object? To pick a more obvious case, you can easily pass in variable values, but you can't pass in actual code (unless it's run dynamically). There is no way, for example, for this to run:
CREATE PROCEDURE p_foo @p_parm VARCHAR (8000) = 'SELECT'
AS
@p_parm * FROM titles
GO
The procedure doesn't know what it's compiling and won't know what you want it to run. The same thing happens with the IN (
) statement. It is expecting a value or a list of values, not a literal *representing* a list of values.
Hope this helps.
July 28, 2005 at 1:25 pm
Similar to another post...
CREATE PROC usp_CheckList(
@parm varchar(100))
AS
SELECT *
FROM dbo.MyTable
WHERE '%,''' + checkfield + ''',%' LIKE (',' + @parm + ',')
GO
Not a very efficient query, though.
K. Brian Kelley
@kbriankelley
July 28, 2005 at 3:36 pm
Thanks for the help. I actually ended up building a UDF to handle this because we have this scenario many times in our development. In many of the apps we build, our users want to be able to select multiple items in a list so building a somewhat generic UDF will help in this immensely.
The code for the function I built at this time is as follows:
CREATE FUNCTION swn_udf_Gbl_Split_Comma_List_50(
@parm varchar(8000))
RETURNS @table table(value varchar(50))
AS
BEGIN
DECLARE @single_value varchar(50),
@pos int
SET @parm = LTRIM(RTRIM(@parm))+ ','
SET @pos = CHARINDEX(',', @parm, 1)
IF REPLACE(@parm, ',', '') <> ''
BEGIN
WHILE @pos > 0
BEGIN
SET @single_value = LTRIM(RTRIM(LEFT(@parm, @pos - 1)))
IF @single_value <> ''
BEGIN
INSERT INTO @table
VALUES (@single_value) --Use Appropriate conversion
END
SET @parm = RIGHT(@parm, LEN(@parm) - @pos)
SET @pos = CHARINDEX(',', @parm, 1)
END
END
RETURN
END
This will work for any lists where values are varchars and none of the individual values are longer than 50 characters. I am going to build some more like this to handle different lengths as well as for some other common data types. I'm doing this to keep them generic.
Thanks for the help.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 29, 2005 at 7:27 am
Below is a string-to-table function that I found a long time ago (maybe on SCC). It converts a comma-delimited string to a table.
CREATE FUNCTION dbo.fnStringToTable(@sText varchar(8000))
RETURNS @retArray TABLE (value sql_variant)
AS
BEGIN
DECLARE @sDelim varchar(1), @sCurVal varchar (50), @idx smallint, @remaining Smallint
SET @sDelim = ','
IF RIGHT (@sText, 1)<> @sDelim
SET @sText = @sText + @sDelim
WHILE LEN (@sText) > 0
BEGIN
SET @idx = CHARINDEX (@sDelim, @sText)
SET @sCurVal = LTRIM(LEFT(@sText, @idx - 1))
INSERT @retArray VALUES (@sCurVal)
IF @idx = LEN(@sText)
SET @sText = ''
ELSE
BEGIN
SET @remaining = LEN(@sText) - @idx
SET @sText = SUBSTRING(@sText, @idx + 1, @remaining)
END -- IF
END -- WHILE
RETURN
END
Then you can do something like
SELECT * FROM tblMyStuff WHERE checkfield in dbo.StringToTable(@MyCommaDelimitedList)
Might need a bit of tweaking, but it works like a champ...
May 7, 2010 at 10:31 am
declare @string as varchar(60)
SELECT @string = ',stringval1,stringval2,stringval3,'
--you must have quotes at the beginning and the end of the string
SELECT *
FROM dbo.Table
WHERE CHARINDEX(',' + columnA + ',', @string, 0) > 0
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply