Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

T-SQL stored procedure that accepts null values

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'
roger v
roger v
3 Solutions
Kevin CrossChief Technology OfficerCommented:
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)

as I understand it sould be as following;

if (@userid is not null and userid = '123')
   //your statement
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.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

roger vAuthor Commented:

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


That way gives an error.


I'll give that a shot and see if it works.
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 .....';
Execute (@SqlStmt);
this will help you without Dynamic SQL.

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now