Passing a list of values through a declared variable

Posted on 2012-08-28
Last Modified: 2012-08-29
Hi Experts,

Up to this point, my SQL has been simplistic in that I've done my DECLARE and SET statements where I'm passing just one string --

for example

SET @FDOS = '2012/01/01'

My question is how can I dynamically pass more than one value at runtime?

I keep researching and trying to test how to do dynamically but I haven't been able to figure out based on my not understanding the complexity of some of the UDF and cursors referenced.

So for example, let's say that I know I want to pass the following random variables -- these are points of service strings: '20', '21', '22', '23'

1. Could you advise how you would set up a

& SET @POS ?

2. Also, is it possible to do a LIKE statement (if I want to say where POS LIKE '2%')

Thanks so much.
Question by:britpopfan74
    LVL 51

    Assisted Solution

    send it as one string and use

    declare @ctg as varchar(100) = 'ACC,PRP';
    select * from products
    where  ',' + @Ctg + ',' like '%,'+ctg+',%';
    prdid	prdname	stock	isAvailable	Ctg
    4	HeadPhone	8	0         	ACC
    5	Black Ink MN-2138	27	1         	PRP
    6	COlor Ink CN-2124	18	1         	PRP

    Open in new window

    here I passed "ACC,PRP" as parameter and used in query (like) to get all those records...
    LVL 16

    Assisted Solution

    Are you trying to pass a string into a Stored Procedure or UDF for execution? If so, your "LIKE" use might look like this:

    Call the stored procedure like this, giving it part of the condition you're looking for:

    exec MyStoredProc 'like %king'

    And the sproc might look like this:

    create procedure MyStoredProc ( @strParam varchar(100) )
        declare @strCmd varchar(1000) = ''
        set @strCmd = @strCmd + ' select Name, Field2, Field3 '
        set @strCmd = @strCmd + ' from dbo.ExpertsList '
        set @strCmd = @strCmd + ' where Name ' + @strParam

    Open in new window

    What happens inside the stored proc is that a string of SQL code is composed, incorporating your part, and then executed. The most common use for this is probably to do things llike apply the same piece of code to various tables on demand or, as I've been doing recently, on fields in tables where the field list is not always the same from table to table!


    LVL 142

    Accepted Solution


    Author Comment

    Thank you all for the advice...

    Trying to put together in an example that I could use as a "template"...

    Say I want to retrieve several regions from my vmemberlist table --

    If I try the below, I get error -- do I need to pass these in the exec statement?

    Msg 139, Level 15, State 1, Procedure MyStoredProc, Line 0
    Cannot assign a default value to a local variable.

    create procedure MyStoredProc (@strCmd varchar(4))
        declare @strCmd as varchar(100) = 'NE1,SE1,NW1,MW1'; -- REGIONS WANT TO PASS
        set @strCmd = @strCmd + ' SELECT DISTINCT mem_no, REGION  '
        set @strCmd = @strCmd + ' FROM vmemberlist '
        set @strCmd = @strCmd + ' where REGION' + @strCmd
        exec @strCmd  'like %'
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    using my function (check the article), the code would go like this:
    create procedure MyStoredProc (@strRegions varchar(4))
        -- I comment out this line, as normally the regions are passed by the argument
        -- set @strRegions = 'NE1,SE1,NW1,MW1'; -- REGIONS WANT TO PASS
        SELECT DISTINCT mem_no, REGION  
         FROM vmemberlist '
           where REGION in ( select value from dbo.ParmsToList(@strRegions, ',') )

    Open in new window


    Author Comment

    Sorry I'm slow on the uptake today...

    So in this scenario, I don't want to use the "exec" to call the regions but instead comment out the "set" to call them

    Could you just explain briefly:

    --how to include the LIKE in this SET (assuming that the regions have other letters after the 1s)?

    --what is 'dbo.ParmsToList'?

    Thanks again!

    create procedure MyStoredProc (@strRegions varchar(4))
        -- I comment out this line, as normally the regions are passed by the argument
         set @strRegions = 'NE1,SE1,NW1,MW1'; -- REGIONS WANT TO PASS

        SELECT DISTINCT mem_no, REGION  
         FROM vmemberlist --''
           where REGION in (select value from dbo.ParmsToList(@strRegions, ','))

    Author Comment

    re-reading, I see you have the dbo.ParmsToList function from the article -- sorry

    if you could just answer on the LIKE placement


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now