473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

IF condition in Stored Procedures

shek124
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
6 21213
amitpatel66
2,367 Expert 2GB
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
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
2,367 Expert 2GB
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
8,127 Expert 4TB
Where is the Then key word in your code ?
Mar 13 '08 #5
shek124
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
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

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

Similar topics

1
by: Robin Tucker | last post by:
I have several instances of "magic number" variables (tinyints). In my program, I have assigned an enumeration to make the meaning clear, such as: enum Condition { Green = 0, Yellow, Red }
17
by: serge | last post by:
How can i delete all user stored procedures and all table triggers very fast in a single database? Thank you
3
by: tekanet | last post by:
Hello folks, I usually use this way to store more procedures inside a single SP: CREATE PROCEDURE usp_MyProc (@usp_mode int) AS IF @usp_mode = 1 BEGIN END
9
by: Ralph Cramden | last post by:
I'm writing a VB6 app which calls several stored procedures on my SQL Server DB. The first stored procedure must complete its inserts before the second stored procedure can query the modified...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
0
by: chaitanya02 | last post by:
Hi All, Please let me know, if we could write Stored Procedures in MS Access. I have a 4 tables like this: sql1-account id, name sql2-account id,email id sql3-account id, cstart date, cend...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.