t-sql 2008 parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql 2008 parameter

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-01-21 : 22:55:02

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

52326 Posts

Posted - 2014-01-22 : 07:39:14

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


[code]
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

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-01-22 : 09:41:20

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

[code]
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

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-01-22 : 09:41:40

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 07:14:58

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources