?
Solved

T-SQL stored procedure that accepts null values

Posted on 2011-09-25
7
Medium Priority
?
244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1400 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 300 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 9

Assisted Solution

by:anillucky31
anillucky31 earned 300 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

771 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