asked on

Passing a list of values through a declared variable

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

DECLARE @FDOS DATETIME
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

DECLARE @POS
& SET @POS ?

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

Thanks so much.

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

DcpKing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

britpopfan74

ASKER

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))
as
Begin
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 %'
End

Guy Hengel [angelIII / a3]

using my function (check the article), the code would go like this:

create procedure MyStoredProc (@strRegions varchar(4))
as
Begin
    -- 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, ',') )
End

Open in new window

britpopfan74

ASKER

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))
as
Begin
-- 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, ','))
End

britpopfan74

ASKER

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

if you could just answer on the LIKE placement

Thanks