SQL Server - How to use If--else condition in Stored Procedure ? EXAMPLE !
Asked By satyanarayan sahoo on 15-May-08 01:53 AM
try this..
Vasanthakumar D replied to satyanarayan sahoo on 15-May-08 01:58 AM
Hi,
suppose you want to run the differnet query depends the count value greater than 0 then ...
declare @Count int
select @Count = count(*) from sampleTable
if @Count > 0
begin
--some query..
end
else
begin
--some query...
end
If--else condition in Stored Procedure
sri sri replied to satyanarayan sahoo on 15-May-08 02:08 AM
Hi,
Check the below stored procedure
CREATE PROCEDURE Test
(
@Id as int
)
As
Begin
If (@ID>0)
Begin
//Some operations
End
Else
Begin
//Some operations
End
End
Sunil Lakshkar replied to satyanarayan sahoo on 15-May-08 02:34 AM
create procedure usp_employee
(
@Emp_Id int,
@Status int
)
As
Begin
If (@Emp_ID != 0 AND @Status =1)
Begin
Select * from EmployeeTable where Emp_Id = @Emp_ID and @Status =1
End
Else
Begin
//write code
End
End
IF...ELSE Condition
Shailendrasinh Parmar replied to satyanarayan sahoo on 15-May-08 02:43 AM
IF condition
BEGIN
'perform task1
END
ELSE
BEGIN
'perform task2
END
Example on using 'if...else' condition in a Stored Procedure...
Rakesh Vikram replied to satyanarayan sahoo on 15-May-08 03:01 AM
CREATE PROCEDURE up_parmsel_state_abbreviation (@state_name VarChar(15),
@state_code Char(2) OUTPUT) AS
-- Declare variables
DECLARE @rc Int
SELECT @state_code = State_Abbreviation_CH
FROM State_T
WHERE State_Name_VC = @state_name
IF @@RowCount > 0
BEGIN
SELECT @rc = 0
RETURN @rc
END
ELSE
BEGIN
SELECT @rc = 1
RETURN @rc
END
Solution
Chirag Bhavsar replied to satyanarayan sahoo on 15-May-08 05:53 AM
IF a > b
begin
'do something
end
ELSE
begin
'do something
end
EXAMPLE:-
IF @chk=5
BEGIN
IF @F > @G
SET @H = 1
ELSE IF @G > @I
SET @H = 2
ELSE
SET @H=3
END
ELSE
PRINT 'Chk is not 5'
How to use If--else condition in Stored Procedure ? and how to return updated column name
ashish mudgal replied to satyanarayan sahoo on 26-Sep-08 02:11 AM
if i update a column in table by store procedure ,then i want to this store procedure return update column name
Fadi replied to Vasanthakumar D on 19-May-10 06:48 PM
hey
what if you want to execute some query depending on value of one of two parameters such like
@CNO int,
@INO int
AS
IF @CNO
BGEIN
END
ELSE
IF @INO
BEGIN
END
I mean if you send @CNO perform a query else if @INO is send perform another query !!
How should I treat it with a situation like this!!??
prasad g replied to Shailendrasinh Parmar on 03-Aug-10 06:22 AM
DECLARE @a INT
SET @a=1
IF(@a = 0)
BEGIN
PRINT 'Correct'
END
ELSE
BEGIN
PRINT 'IN CORRECT'
END
prasad g replied to satyanarayan sahoo on 03-Aug-10 06:42 AM
CREATE PROCEDURE CHECK_LOGIN
(
@Id INT,
@Name VARCHAR(20),
@Age INT
)
AS
BEGIN
IF(@Age>=18)
BEGIN
BEGIN TRAN
INSERT INTO sample(Id,Name,Age)values(@Id,@Name,@Age)
COMMIT TRAN
PRINT 'Inserted Sucessfully . . . '
END
ELSE
BEGIN
BEGIN TRAN
COMMIT TRAN
PRINT 'Age Should be greater than 18 . . .'
END
END
Example
EXEC CHECK_LOGIN 1,'PRASAD',18
prasad g replied to satyanarayan sahoo on 03-Aug-10 06:44 AM
CREATE PROCEDURE CHECK_LOGIN
(
@Id INT,
@Name VARCHAR(20),
@Age INT
)
AS
BEGIN
IF(@Age>=18)
BEGIN
BEGIN TRAN
INSERT INTO sample(Id,Name,Age)values(@Id,@Name,@Age)
COMMIT TRAN
PRINT 'Inserted Sucessfully . . . '
END
ELSE
BEGIN
BEGIN TRAN
COMMIT TRAN
PRINT 'Age Should be greater than 18 . . .'
END
END
Example
EXEC CHECK_LOGIN 1,'PRASAD',18
Venkatesh Prabu replied to satyanarayan sahoo on 12-Oct-11 06:33 AM
create proc pup(@c_id int,@c_pri int)
as
if(@c_pri>12)
update pp set c_pri=@c_pri where c_id=@c_id
else
print' ERROR:the price is not above 12'