SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 t-sql 2008 parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

295 Posts

Posted - 01/21/2014 :  22:55:02  Show Profile  Reply with Quote
In t-sql 2008 r2 I would like to know how to set a variable value that would be similar to

declare @avar varchar(3)

set @avar in ('ABS', YUR', 'UNV', 'MED')

The @avar character can be one or all of the values listed above.

I want to be able to set a variable like this since the user can select one, 2, 3, or 4 of the values listed above in an ssrs 2008 r2 report.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/22/2014 :  07:39:14  Show Profile  Reply with Quote
just declare variable as of type varchar with longer length (3000 for example). then just link to multivalued parameter in SSRS and then values will get passed as comma separated list as 'ABS, YUR, UNV, MED'
then in your query either use a string parsing udf to get individual values and do a join
ie like

SELECT ...
FROM othertables ..
JOIN dbo.PserValues(@avar,',')f
on f.Val = t.<yoursearchfield>

see parsevalues here
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

or use string pattern based serach as


SELECT ...
FROM othertables ..
WHERE ',' + @avar + ',' LIKE '%,' +  t.<yoursearchfield> + ',%'


and you'll get table filtered by all passed values




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jassie
Constraint Violating Yak Guru

295 Posts

Posted - 01/22/2014 :  09:41:20  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

just declare variable as of type varchar with longer length (3000 for example). then just link to multivalued parameter in SSRS and then values will get passed as comma separated list as 'ABS, YUR, UNV, MED'
then in your query either use a string parsing udf to get individual values and do a join
ie like

SELECT ...
FROM othertables ..
JOIN dbo.PserValues(@avar,',')f
on f.Val = t.<yoursearchfield>

see parsevalues here
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

or use string pattern based serach as


SELECT ...
FROM othertables ..
WHERE ',' + @avar + ',' LIKE '%,' +  t.<yoursearchfield> + ',%'


and you'll get table filtered by all passed values




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

jassie
Constraint Violating Yak Guru

295 Posts

Posted - 01/22/2014 :  09:41:40  Show Profile  Reply with Quote
Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2014 :  07:14:58  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000