Solved

T-SQL stored procedure that accepts null values

Posted on 2011-09-25
7
232 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.​
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now