Variable usage in Where clause

Question

Post reply

Variable usage in Where clause

Hardy21

SSCrazy Eights

Points: 9708
More actions
October 18, 2010 at 3:34 am

#241735

Hello,
Please find the below TSQL Code:
declare @table1 table
( SourceId INT,
RecordStatus NVARCHAR(20),
ParentRecordId NVARCHAR(5))
insert into @table1
values (1, 'Start', 1), (2, 'In Progress', 1), (3, 'In Progress', 2),
(4, 'On Hold', 2), (5, 'Completed', 1), (6, 'Completed', 2)
Declare @TargetIDs varchar(max)
select @TargetIDs = COALESCE(@TargetIDs + ',','') + CONVERT(VARCHAR(20),ParentRecordId)
FROM @table1
WHERE
ParentRecordId in (1,2,3,4,5,6) and RecordStatus in ('In Progress', 'On Hold')
print 'First Print statement'
print 'target-' + @TargetIDs
Declare @parentRecordIds varchar(max)
set @parentRecordIds = '1,2,3,4,5,6'
set @TargetIDs = ''
select @TargetIDs = COALESCE(@TargetIDs + ',','') + CONVERT(VARCHAR(20),ParentRecordId)
FROM @table1
WHERE
ParentRecordId in (@parentRecordIds) and
RecordStatus in ('In Progress', 'On Hold')
print 'Second Print statement'
print 'target-' + @TargetIDs
When I fire the query, sql displays following result:
First Print statement
target-1,2,2
Second Print statement
target-
I would like to know why @TargetIDs doesn't populated after second print statement ?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Its most likely do to with the use of the variable in the IN clause.

You are now asking

WHERE ParentRecordId in ("1,2,3,4,5,6")

So you will only get a match is ParentRecordId = "1,2,3,4,5,6".

Investigate one of the many CSV split routines

Clear Sky SQL
My Blog[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

As Dave pointed out, the IN predicate doesn't work that way.

You may also concatenate in a safer way, using FOR XML PATH.

Concatenating variables in the SELECT list doesn't always work and is not always sorted consistently.

DECLARE @table1 TABLE

( SourceId INT,

RecordStatus NVARCHAR(20),

ParentRecordId NVARCHAR(5))

INSERT INTO @table1

VALUES (1, 'Start', 1), (2, 'In Progress', 1), (3, 'In Progress', 2),

(4, 'On Hold', 2), (5, 'Completed', 1), (6, 'Completed', 2)

DECLARE @TargetIDs VARCHAR(MAX)

SET @TargetIDs = STUFF((

SELECT ',' + CONVERT(VARCHAR(20),ParentRecordId) AS [text()]

FROM @table1

WHERE ParentRecordId IN (1,2,3,4,5,6) AND RecordStatus IN ('In Progress', 'On Hold')

ORDER BY 1

FOR XML PATH('')

)

, 1, 1, SPACE(0));

PRINT 'First Print statement'

PRINT 'target-' + @TargetIDs

DECLARE @parentRecordIds TABLE (

ParentRecordId int

)

INSERT INTO @ParentRecordIds VALUES (1),(2),(3),(4),(5),(6)

SET @TargetIDs = ''

SET @TargetIDs = STUFF((

SELECT ',' + CONVERT(VARCHAR(20),ParentRecordId) AS [text()]

FROM @table1

WHERE ParentRecordId IN (SELECT ParentRecordId FROM @parentRecordIds) AND RecordStatus IN ('In Progress', 'On Hold')

ORDER BY 1

FOR XML PATH('')

)

, 1, 1, SPACE(0));

PRINT 'Second Print statement'

PRINT 'target-' + @TargetIDs

-- Gianluca Sartori

Paul White SSC Guru Points: 150467 More actions · Answer 3

Gianluca Sartori (10/18/2010)
As Dave pointed out, the IN predicate doesn't work that way.

If something like IN (@variable) is needed, Erland Sommarskog covers the options extremely well:

http://www.sommarskog.se/arrays-in-sql-2005.html

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

Hardy21

SSCrazy Eights

Points: 9708

October 18, 2010 at 6:41 am

#1238365

Thanks