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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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.
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);
For ex.:
Declare @SqlStmt nvarchar(max);
Select @SqlStmt = 'Select .....';
Select @SqlStmt = @SqlStmt + ' Where .....';
then
Execute (@SqlStmt);
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if (@userid is not null and userid = '123')
begin
//your statement
end