Solved

T-SQL stored procedure that accepts null values

Posted on 2011-09-25
7
243 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 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