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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • 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