Solved

T-SQL stored procedure that accepts null values

Posted on 2011-09-25
7
238 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 48
Sql server function help 15 39
What is this datetime? 1 20
Parse this column 6 27
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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