Varchars as IN params ??
-
1) Why is a database website non-searchable?
2) Is there a way to pass in a varhcar pre-prepared list of items and use them as "IN" parameters ?
Example:
procedure X
(@AList varcvhar(255))
Select * from ATable where
AnIntegerColumn IN (@AList)
AList is a string that looks like this:
'1, 2, 5, 7'
-
1) Search is on the top menu bar. It is hokey, but we haven't spent time fixing it as we have other priorities.
2) No. The only workaround is to build dynamic sql
procedure X
(@AList varcvhar(255))
as
declare @cmd varchar(2000)
select @cmd = 'Select * from ATable where
AnIntegerColumn IN (' + @AList + ')'
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
quote:
...Is there a way to pass in a varhcar pre-prepared list of items and use them as "IN" parameters ?Example:
procedure X
(@AList varcvhar(255))
Select * from ATable where
AnIntegerColumn IN (@AList)
AList is a string that looks like this:
'1, 2, 5, 7'
Would something like the following work for you? You'd have to adhere to the comma-delimited, no-imbedded-spaces input parameter string.
CREATE PROCEDURE x
@aList VARCHAR (255)
AS
SELECT *
FROM ATable
WHERE ',' + @Alist + ',' LIKE '%,' + CONVERT (VARCHAR, AnIntegerColumn) + ',%'
-
Non Dynamic option?:
User Defined Function
Create a UDF to take in a string list of ID parameters and cycle through them, adding them to a table variable before returning that table. Then select from this table in the where clause:
Select * from ATable where AnIntegerColumn in (select Id from dbo.UDFTable(@Alist))
-
My original AList which is comman delimited CAN be modified. It's just a string that I create in code - could be in any format but it is a list of integer numbers.
I'm going to try Dave's idea - the "Table" return type may just be the trick. THis way the SP can be stored and optimized (I assume this way is more effecient than pure dynamic SQL). Thanks again - here I go...
-
Thx Dave-
I use this UDF which is called from another SP. My website code calls a SP with a list of Client ID's to view. Then - the SP calls the UDT by using it in the IN() clause.
Works like a charm !!!!!
CREATE FUNCTION dbo.PRN_ClientTypeLister
(
@AList varchar(255)
)
RETURNS @Results table
(
ClientTypeID tinyint NOT NULL
)
AS
BEGIN
DECLARE @Counter int
DECLARE @Temp char(1)
DECLARE @Current varchar(10)
SET @Counter = 1
SET @Current = ''
WHILE (@Counter < (LEN(@AList) + 2))
BEGIN
SET @Temp = SUBSTRING(@AList, @Counter, 1)
IF (@Temp <> ',') AND (@Temp is not null) AND (@Temp <> '')
SET @Current = @Current + @Temp
ELSE
BEGIN
INSERT INTO @Results VALUES(@Current)
SET @Current = ''
END
SET @Counter = @Counter + 1
END
RETURN
END
Test:
SELECT * from dbo.PRN_ClientTypeLister('1,2,23,7,8,9')
-
The two options are as already mentioned both have pros and cons
user defined function
Allows security to be defined at stored procedure level not table level
dynamic sql
Is more performant because the optimiser knows at compile time what the values in the IN statement are.
The performance difference is probably negible but will exist, I would therefore go for the udf because of the security aspect.
If your list is > 8000 bytes then I have a script (yet to approved) that takes in a text variable, it also allows you to return only the tokens you want by position.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Thanks Simon-
I guess the 8000 byte limit is the varchar max length ?? Anyway - yes - my function is basic and lacks alot but works for the moment. A little more robust function is appreciated.
Thanks - B
-
Script should be approved next week. If you want it I can forward it to you
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply