February 13, 2012 at 7:36 am
Is there a way to set @variable = IS NULL?
I need it to use in a select query... and trying to stay away from dynamic sql.
ty
February 13, 2012 at 7:40 am
Like this?
declare @MyVar varchar(10)
set @MyVar = null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 7:45 am
Hmm not really.
I got this query..
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b IS NULL
Is it possible to get same results with
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b = @MyVar
February 13, 2012 at 7:49 am
memymasta (2/13/2012)
Hmm not really.I got this query..
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b IS NULL
Is it possible to get same results with
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b = @MyVar
Not really sure what you are after but I think this should be close.
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b = @MyVar OR b.b IS NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 13, 2012 at 7:50 am
I think you're going to need to use an OR
SELECT a.a
FROM a
LEFT JOIN b
ON a.a=b.b
WHERE b.b = @MyVar
OR (b.b IS NULL AND @MyVar IS NULL )
February 13, 2012 at 7:53 am
@Recurs1on & @sean Lange
That helped ty very much 🙂
February 13, 2012 at 7:55 am
memymasta (2/13/2012)
Hmm not really.I got this query..
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b IS NULL
Is it possible to get same results with
Select a.a from a LEFT JOIN b ON a.a=b.b WHERE b.b = @MyVar
Here's a few ways
--Method 1
SELECT a.a
FROM a
LEFT JOIN b ON a.a = b.b
WHERE b.b = COALESCE(@MyVar, b.b)
--Method 2
IF @MyVar IS NULL
BEGIN
SELECT a.a
FROM a
LEFT JOIN b ON a.a = b.b
END
ELSE
BEGIN
SELECT a.a
FROM a
LEFT JOIN b ON a.a = b.b
WHERE b.b = @MyVar
END
--Method 3
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT a.a' + CHAR(13) + CHAR(10) + 'FROM a' + CHAR(13) + CHAR(10) + 'LEFT JOIN b ON a.a = b.b'
IF @MyVar IS NOT NULL
BEGIN
SET @sql = @sql + CHAR(13) + CHAR(10) + 'WHERE b.b = @MyInVar'
EXECUTE sp_executesql @sql, N'@MyInVar INT', @MyInVar = @MyVar
END
ELSE
BEGIN
EXECUTE sp_executesql @sql
END
Hmm, I may have misread the OP. Ignore my post.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply