Solved

T-SQL stored procedure that accepts null values

Posted on 2011-09-25
7
235 Views
Last Modified: 2012-05-12
Hi Experts,

I need to write a stored procedure that has two input values. The first input value is an integer and will always be input. The 2nd input param is a varchar and it might or might not be passed in. I need to check in the stored proc to see if this value is not null, then have an additional AND statement. I have this currently but it doesn't work:

@processid int,
@userid varchar(8)

select * from tableA
where processid = @processid
if @userid is not null
   and userid = '@userid'
0
Comment
Question by:roger_v
7 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 350 total points
ID: 36596302
Yes, that will not work. You need to either use the IF statement to have two different SQL queries executed or in the where clause do something like:

(@userid is null or userid = @userid)

Kevin
0
 
LVL 3

Expert Comment

by:afsarsal
ID: 36596326
as I understand it sould be as following;

if (@userid is not null and userid = '123')
begin
   //your statement
end
0
 
LVL 5

Assisted Solution

by:bitref
bitref earned 75 total points
ID: 36596433
You should either use Dynamic SQL to build the query syntax according to the conditions then execute it later or write 2 complete T-SQL statements to run each according to the equivalent condition.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:roger_v
ID: 36596534
@bitref:

What do you mean by "dynamic sql"? How do I do that?

@afsarsal:

That way gives an error.

@mwvisa:

I'll give that a shot and see if it works.
0
 
LVL 5

Expert Comment

by:bitref
ID: 36597145
Dynamic SQL allows forming a string for the SQL statement you need, then using EXECUTE command to execute it.

For ex.:
Declare @SqlStmt nvarchar(max);
Select @SqlStmt = 'Select .....';
Select @SqlStmt = @SqlStmt + ' Where .....';
then
Execute (@SqlStmt);
0
 
LVL 5

Expert Comment

by:bitref
ID: 36597158
0
 
LVL 9

Assisted Solution

by:anillucky31
anillucky31 earned 75 total points
ID: 36597165
this will help you without Dynamic SQL.

select * from tableA
where processid = @processid
and ( @userid is null or userid = @userid)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question