If statements in stored procedures

[transparent]

I have a stored procedure with a parameter @user_id

ie:

CREATE PROCEDURE usp_document_latest

@user_id int -- Top level category of requested path

is it possible to check if the parameter is empty or holds a specific value?

 

[codestorm]

CREATE PROCEDURE usp_document_latest
@user_id int = null
...

now if a user id is not passed, it defaults to null, which is easy to test for.
and if a non integer is attempted to pass in, it'll throw an error.
sorry bout the crud writing, shift key died. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[transparent]

cheers do you know how the 'if else' syntax works ie

if @user_id is not null

begin
.........

end if

This works fine, however I can't get else statements to work

 

[SQLSister]

if @user_id is not null

begin
.........

end

Else

Begin

......

End

If is slightly different than in many other languages. When you write an If statement, it will consider whatever is on the nesxt line as the command it will execute if the if condition is met. To get more than one line to be recognized you must use begin, then the commands, then end. This still ahs you with the first block of the if, it does not end the if statement per se as an End if statment does. Therefore, the else comes after the first set of begin-end statements. As a good practice, I a;ways use the begin and end even if I am only using one line right now because that may change in the future and it makes it easier to maintain and to tell exactly what is in the if. Otherwise it is easy to have errors becasue you forgot to use the begin and end block and meant to put more than one line in the if.

 

[transparent]

Cheers for your reply,howvever given:

CREATE PROCEDURE usp_document_latest

@user_id int -- Top level category of requested path

as

if @user_id is not null
begin
...
...
..
end

why does the above not work if I don't pass the parameter @user_id given I'm checking for its existance?

 

[SQLSister]

Try setting null as the default value of the parameter in the definition as codestorm suggested.

 

[mukund]

Hi transparent.
See if this solution works.

Use this statement at the start of your stored proc

SET ANSI_NULLS OFF

This will take care that the NULL values are compared properly.

At the end of the stored proc you can have the following statement.

SET ANSI_NULLS ON

Hope this helps
-Mukund.

 

[transparent]

cheers