November 27, 2002 at 1:34 pm
I am trying to get a string literal of comma seperated values to be parsed as a list of values by the "IN" statement. Here's my sp. @param1 has a literal of seperated values, I am trying to get each value evaluated seperatly, but the T-SQL treats the whole string as a literal. ANyone have a work around? This parameter would be dynamic in that the values are never known ahead of time and there could be 0-n which prevents me from running a sp multiple times from program code for scaling reasons. Thanks!
ALTER PROCEDURE dbo.StoredProcedure1
@param1 varchar (1400)
AS
/* CANNOT USE A 'LIKE' EXPRESSION: I WANT TO FIND ALL RECORDS WHERE
ANY OF THESE COMMA SEPERATED VALUES APPEAR IN FIELD FNAME*/
SET @Param1 = 'joe,john,jay'
SELECT FULLNAME FROM USERS WHERE fname IN (@param1)
RETURN
November 27, 2002 at 2:25 pm
Declare @SList Varchar(1400),
@SFor Varchar(100)
-- Assumption |(pipe char) never in data!!!
Set @SList = '|joe|john|jay|'
Set @SFor='john'
Select Case When CharIndex('|'+@SFor+'|',@SList)>0
Then 'Yes'
Else 'No' End
November 28, 2002 at 2:52 am
Try using dynamic SQL.....
ALTER PROCEDURE dbo.StoredProcedure1
@param1 varchar (1400)
AS
DECLARE @sql varchar(8000)
SET @Param1 = 'joe,john,jay'
-- if using pipe delimiters include a replace statement
SET @Param1 = replace(@Param1, '|', ',')
SET @sql = 'SELECT FULLNAME FROM USERS WHERE fname IN(' + @param1 + ')'
EXEC (@sql)
RETURN
November 28, 2002 at 3:17 am
In addition to my reply above you may also need to ensure that you have apostrophe's around the text values in your @Param1 variable.
Something like this should do it....
SET @Param1 = char(39) + replace(@Param1, ',', char(39) + ',' + char(39)) + char(39)
November 28, 2002 at 3:20 am
BKelly's suggestion (in the other parallel forum) of using the following...
SELECT FullName
FROM Users
WHERE CHARINDEX(fname, @Param1) > 0
...is flawed in that if 'john' is in the search @Param, the results will return any value which contains 'john' (i.e. johnney, johnathon)...even if these names arn't spelt correctly, I think you get the idea.
November 28, 2002 at 4:31 am
WHERE CHARINDEX(','+fname+',', ','+@Param1+',') > 0
will solve that
Far away is close at hand in the images of elsewhere.
Anon.
November 28, 2002 at 4:46 am
The CHARINDEX method is also being performed on every row of your table and is unable to make use of any indexes since it is non-SARGable. (i.e. calculated)
If you didn't want to use dynamic SQL you could also create a UDF which accepted a pipe or comma delimited list and returned a table variable containing the values.
Then use it as follows....
SELECT fullname
FROM users
WHERE fname in(select strValue from dbo.ConvertStringToTable (@Param1))
November 28, 2002 at 6:20 am
Ack. That's true about selecting against anything that starts with john. Thanks for the catch, Paul, and the fix, David. But I agree in that it is non-SARGable. Probably would be better to do the UDF (or a temp table in 7.0).
Using Dynamic SQL means the user has to have permissions directly against the table... generally to be avoided from a security perspective if you're implementing stored procedures since one of the reasons for using a stored procedure in this manner would be to prevent direct access. If you are going to use dynamic SQL, sp_executesql is probably better than EXEC. Tends to avoid the recompile in a stored procedure due to the dynamic SQL query. Granted, execution plan may be generated on the dynamic SQL query, but it stops it on the stored procedure which could serialize it.
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 2, 2002 at 10:06 am
Thanks all for your valuable input. My apologies for the dual post, I submitted the first accidentally.
The UDF idea sounds plausible, so I will give this a try and see how it works.
BTW: I did write this an sp using sp_executesql using dynamic statements and it worked, but performance was absolutely horrible and the effort was wasted due to the amount time spent, amount and complexity of T-SQL involved, poor performance and ulitmately ended up with a non maintainable solution.(I learned something however: I will never do that again!)
Again, thanks for your ideas!
Ken
December 8, 2002 at 1:39 pm
You can create a user-defined function that does the compare and returns 1 or 0 depending on found/not found.
Then use the UDF in your WHERE clause
select *
from table
where dbo.UDFComparelist(ColumnToCompare) = 1
It would be slower than molasses in january but would work.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply