Setting a Single Variable for an In Statement

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

Transact-SQL (2005)

Setting a Single Variable for an In Statement

Author

Topic

thedryden
Starting Member

23 Posts

Posted - 2007-12-26 : 12:59:39

I’m want to use a set of variable to define some often changing values in a subsequent SQL statement. The problem is that I want to define the entire contents of an In statement in a Where clause with a single variable. I’ve set up the “correct” value for the variable using Char(39) which represents “'” (see code below). When I put the variable into the statement however I do not get a result. I've tested it, and I can get datetime variables right, but not my nvarchars for my In statements.

What am I doing wrong?

TSQL I’m using to declare and set the variables is as follows:

DECLARE @Start_Date datetime
    , @End_Date datetime
    , @Region nvarchar(75)
    , @EQ_Market_Cluster nvarchar(100)

SET @Start_Date = cast(convert(varchar(8),getdate()-1,1) as datetime)
SET @End_Date = cast(convert(varchar(8),getdate(),1) as datetime)
SET @Region = CHAR(39) + 'NSD' + CHAR(39) + ',' + CHAR(39) + 'NORTHEAST' + CHAR(39) + ',' + CHAR(39) + 'CENTRAL' + CHAR(39) +  ',' + CHAR(39) + 'SOUTHEAST' + CHAR(39) +  ',' + CHAR(39) + 'WEST'  + CHAR(39)
SET @EQ_Market_Cluster =  CHAR(39) + 'NEW ENGLAND'  + CHAR(39) + ','  + CHAR(39) + 'GEORGIA'  + CHAR(39)

PRINT '@Start_Date = ' + cast(convert(datetime,@Start_Date) as varchar(12))
PRINT '@End_Date = ' + cast(convert(datetime,@End_Date) as varchar(12))
PRINT '@Region = ' + @Region
PRINT '@EQ_Market_Cluster = ' + @EQ_Market_Cluster

This is the respose from the print commands when I run the SQL in Aqua:

 Warnings: ---> 
   W (1): @Start_Date = Dec 25 2007 
   W (2): @End_Date = Dec 26 2007 
   W (3): @Region = 'NSD','NORTHEAST','CENTRAL','SOUTHEAST','WEST'
   W (4): @EQ_Market_Cluster = 'NEW ENGLAND','GEORGIA'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 13:19:59

Are you trying to referrence variable directly inside IN like:-

WHERE FIELD IN (@Var)

This wont work i guess.
You might need to use dynamic sql to get this work. build a SQL string as
@Sql='.... WHERE Field IN (' + @Var + ')' and execute it using
EXEC(@Sql)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-26 : 13:55:08

Another option would be to parse the region values into a table variable and inner join that with the table you are searching. One common way is to create a table-valued function. Here is a simple example.

This site has a lot of csv parsing routines but here is a quick one just to illustrate the point:


create function dbo.parseCSV(@regions nvarchar(75))
returns @tb table (region nvarchar(20))
as
begin
	insert @tb (region)
	select	substring(r, number+1, charindex(',', r, number+2) - number-1)
	from	(select ',' + @regions + ',' as r) as fullStr
	cross join master..spt_values
	where type = 'P'
	and	substring(r, number, 1) = ','
	and	number < len(@regions)
	return
end
go

declare @Region nvarchar(75)
set @region = N'NSD,NORTHEAST,CENTRAL,SOUTHEAST,WEST'

select region from dbo.parseCSV(@region) 

select	myTable.region
from	(select N'NSD' as region) as myTable
join	dbo.parseCSV(@region) r on r.region = myTable.region

go
drop function dbo.parseCSV
go

output:
region               
-------------------- 
NSD
NORTHEAST
CENTRAL
SOUTHEAST
WEST

region 
------ 
NSD

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources