IF condition in Stored Procedures

[shek124]

How to use the if condition in stored Procedures..

i.e. if the parameter value is null , it executes the one stored procedure
otherwise it executes the other one.

is it possible in stored procedures..


Thanks in advance

[amitpatel66]

How to use the if condition in stored Procedures..

i.e. if the parameter value is null , it executes the one stored procedure
otherwise it executes the other one.

is it possible in stored procedures..


Thanks in advance

Yes it is possible to perform in stored procedures.

Simple syntax would be:

[code=sql]

IF <condition> THEN
call proc1
ELSE
call proc2;

[/code]

[shek124]

ok thanks .. it works very well if not use the Then Keyword in Stored procedure

For example

Create Procedure Sp_test
@podtlsid numeric(9)
as
If @podtlsid <> 0
Update Po_Pending_Trn set Pending_Status= 'Cleared' where Po_Dtls_Trn_Fk= @podtlsid
else
Update Po_Pending_Trn set Pending_Status= 'NotCleared' where Po_Dtls_Trn_Fk= @podtlsid

go



If im using Then , It shows the error
[HTML]Incorrect syntax near the keyword 'Then'[/HTML]

[amitpatel66]

ok thanks .. it works very well if not use the Then Keyword in Stored procedure

For example

Create Procedure Sp_test
@podtlsid numeric(9)
as
If @podtlsid <> 0
Update Po_Pending_Trn set Pending_Status= 'Cleared' where Po_Dtls_Trn_Fk= @podtlsid
else
Update Po_Pending_Trn set Pending_Status= 'NotCleared' where Po_Dtls_Trn_Fk= @podtlsid

go



If im using Then , It shows the error
[HTML]Incorrect syntax near the keyword 'Then'[/HTML]

I only provided a pseudo code for you to get an idea. If your code without TEHN is working, you can go ahead with that syntax.

[debasisdas]

Where is the Then key word in your code ?

[shek124]

When im Using like that , i got the error

If @prodid<> 0 then
exec Sp1
else
exec Sp2



Thanks for your Reply

[Amjad381]

Looks like you are missing a BEGIN END keyword. Pasted this from somewhere else, hope that helps :)

CREATE PROCEDURE [dbo].[INN_getPhotos]
(
@categoryID int = NULL
)
AS

IF @categoryID IS NULL
BEGIN
SELECT
photo_id,
photo_name,
photo_thumb_nam e,
photo_caption,
photo_byte_size ,
active_photo,
category_id

FROM
[Photo]
END

ELSE
BEGIN
SELECT
photo_id,
photo_name,
photo_thumb_nam e,
photo_caption,
photo_byte_size ,
active_photo,
category_id

FROM
[Photo]

WHERE
category_id = @categoryID
END
GO