September 11, 2011 at 11:37 am
Hi
Please can you advise the correct syntax when using a variable with a select statement that uses the IN operator with a variable
some set up data:
USE [tempdb]
GO
--===== Conditionally drop all the temp tables to make reruns easier in SSMS
IF OBJECT_ID('tempdb..TBL_TST','U') IS NOT NULL DROP TABLE TBL_TST;
CREATE TABLE [dbo].[TBL_TST](
[MonthDate] [datetime2](7) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TBL_TST]([MonthDate])
SELECT '20080101 00:00:00.000' UNION ALL
SELECT '20080101 00:00:00.000' UNION ALL
SELECT '20080101 00:00:00.000' UNION ALL
SELECT '20080101 00:00:00.000' UNION ALL
SELECT '20080301 00:00:00.000' UNION ALL
SELECT '20090101 00:00:00.000' UNION ALL
SELECT '20090201 00:00:00.000' UNION ALL
SELECT '20090101 00:00:00.000' UNION ALL
SELECT '20090201 00:00:00.000' UNION ALL
SELECT '20090101 00:00:00.000' UNION ALL
SELECT '20090201 00:00:00.000' UNION ALL
SELECT '20090101 00:00:00.000' UNION ALL
SELECT '20090201 00:00:00.000' UNION ALL
SELECT '20090301 00:00:00.000'
Run following code
SELECT DISTINCT MONTHDATE
FROM TBL_TST
WHERE( (CONVERT(varchar( 8 ) , MONTHDATE , 3)
IN( '01/01/09','01/02/09')))
this works as expected and provides following results:
2009-01-01 00:00:00.0000000
2009-02-01 00:00:00.0000000
So set up a sutiable variable
DECLARE @MONTHDATE VARCHAR (MAX)
SELECT @monthdate =
STUFF((
SELECT DISTINCT ',''' + CONVERT(varchar( 8 ) , MONTHDATE , 3) + ''''
FROM TBL_TST
WHERE
(MONTHDATE > CONVERT(datetime , '2008-12-01 00:00:00' , 102))
AND
(MONTHDATE < CONVERT(datetime , '2009-03-01 00:00:00' , 102))
FOR XML PATH( '' )) , 1 , 1 , '' );
PRINT @MONTHDATE ----==== this appears to give correct results
the variable returns
'01/01/09','01/02/09'
I want to use the output from the variable above (@monthdate) in the following statement
DECLARE @MONTHDATE VARCHAR (MAX)
SELECT @monthdate =
STUFF((
SELECT DISTINCT ',''' + CONVERT(varchar( 8 ) , MONTHDATE , 3) + ''''
FROM TBL_TST
WHERE
(MONTHDATE > CONVERT(datetime , '2008-12-01 00:00:00' , 102))
AND
(MONTHDATE < CONVERT(datetime , '2009-03-01 00:00:00' , 102))
FOR XML PATH( '' )) , 1 , 1 , '' );
SELECT MONTHDATE
FROM TBL_TST
WHERE( (CONVERT(varchar( 8 ) , MONTHDATE , 3)
IN ( ' + @Monthdate + ' )))
but this doesnt return any values...???
have tried various syntax versions and still havent cracked this...
thoughts and comments will be appreciated
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 11, 2011 at 11:49 am
In short, you can't do that. IN takes either a resultset via a subquery or a list of literal values, not a variable.
Options:
* Dynamic SQL (beware SQL injection)
* A splitter function that takes the variable and returns a table. Jeff Moden's got a good one, either at article or script here (google should find it)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2011 at 4:59 am
many thanks for clarification....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 14, 2011 at 12:19 pm
It's fairly easy to convert a string into something that can be used like an IN predicate. Here's an article on it.
http://www.sqlservercentral.com/articles/T-SQL/73838/
Todd Fifield
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply