Text string as variable - 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

General SQL Server Forums

New to SQL Server Programming

Text string as variable

Author

Topic

Eyeman6513
Starting Member

2 Posts

Posted - 2014-05-09 : 14:30:11

I want to run the following code:

Declare @Testnames as Varchar(500)
Set @Testnames = ('CT,MRI')-- List all radiology type

SELECT *
FROM RPT_OUTPATIENT_IMAGING
WHERE service_level2 In (@Testnames)

Nothing comes back.

When I run:

SELECT *
FROM RPT_OUTPATIENT_IMAGING
WHERE service_level2 In ('CT','MRI')

Then I get results.

Any suggestions now to fix the first bit of code? I have found some solutions that include using dynamic SQL and I want to avoid that.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-09 : 14:34:00

You could use a string parsing function like this:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

Eyeman6513
Starting Member

2 Posts

Posted - 2014-05-09 : 15:00:28

Lamprey,

I reviewed the article but I still don't get it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-09 : 15:39:57

If you use the function then you can do something like:

SELECT *
FROM RPT_OUTPATIENT_IMAGING
WHERE service_level2 IN (SELECT Item FROM dbo.DelimitedSplit8k(@Testnames, ',')) 

-- OR

SELECT*
FROM 
	RPT_OUTPATIENT_IMAGING
INNER JOIN
	dbo.DelimitedSplit8k(@Testnames, ',') AS T
	ON RPT_OUTPATIENT_IMAGING.service_level2 = T.Item

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-10 : 00:35:34

Hope this works fine for you..



--------------------------------------------------------------------------------------------------------------------
-- List all radiology type by stuff function(Nothing new i just added ' operator after ,)
--------------------------------------------------------------------------------------------------------------------
Declare @Testnames as Varchar(500)
Set @Testnames  = STUFF (( SELECT Distinct ','''+YourColumnName+'''' FROM TestTable   FOR XML PATH ('')),1,1,'')
--------------------------------------------------------------------------------------------------------------------
--Excute it dynamically
--------------------------------------------------------------------------------------------------------------------
DECLARE @query VARCHAR(MAX)
SET @query =N'SELECT * FROM RPT_OUTPATIENT_IMAGING WHERE service_level2 In ('+@Testnames+')'
EXECUTE (@query)
--------------------------------------------------------------------------------------------------------------------

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-12 : 07:07:57

this is another way of doing it if csv list is small and table is also of medium data volume


Declare	 @Testnames as Varchar(500)
Set	 @Testnames = ('CT,MRI')-- List all radiology type

SELECT	 *
FROM	 RPT_OUTPATIENT_IMAGING
WHERE	 ',' + @Testnames  + ',' LIKE '%,' + service_level2 + ',%'

------------------------------------------------------------------------------------------------------
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