By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,928 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,928 IT Pros & Developers. It's quick & easy.

IF condition in Stored Procedures

shek124
P: 50
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
Mar 13 '08 #1
Share this Question
Share on Google+
6 Replies


amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. IF <condition> THEN
  3. call proc1
  4. ELSE
  5. call proc2;
  6.  
  7.  
Mar 13 '08 #2

shek124
P: 50
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]
Mar 13 '08 #3

amitpatel66
Expert 100+
P: 2,367
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.
Mar 13 '08 #4

debasisdas
Expert 5K+
P: 8,127
Where is the Then key word in your code ?
Mar 13 '08 #5

shek124
P: 50
When im Using like that , i got the error

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




Thanks for your Reply
Mar 13 '08 #6

P: 1
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_name,
photo_caption,
photo_byte_size,
active_photo,
category_id

FROM
[Photo]
END

ELSE
BEGIN
SELECT
photo_id,
photo_name,
photo_thumb_name,
photo_caption,
photo_byte_size,
active_photo,
category_id

FROM
[Photo]

WHERE
category_id = @categoryID
END
GO
Apr 20 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.