[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Passing a list of values through a declared variable

Posted on 2012-08-28
7
Medium Priority
?
424 Views
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

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.
0
Comment
Question by:britpopfan74
7 Comments
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 200 total points
ID: 38343153
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...
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 200 total points
ID: 38343976
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) )
as
Begin
    declare @strCmd varchar(1000) = ''
    set @strCmd = @strCmd + ' select Name, Field2, Field3 '
    set @strCmd = @strCmd + ' from dbo.ExpertsList '
    set @strCmd = @strCmd + ' where Name ' + @strParam
    exec(@strCmd)
End

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!

hth

Mike
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 38344260
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:britpopfan74
ID: 38345412
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38345433
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

0
 

Author Comment

by:britpopfan74
ID: 38345672
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
0
 

Author Comment

by:britpopfan74
ID: 38345692
re-reading, I see you have the dbo.ParmsToList function from the article -- sorry

if you could just answer on the LIKE placement

Thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 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