how to call out parameter in if and else condition

Posted by Shanky11 under Others on 10/1/2012 | Points: 10 | Views : 856 | Status : [Member] | Replies : 5
what is error in this code???????????????
i want that if emailid is already exist
then set try again otherwise set successfully
CREATE PROCEDURE SP_Registration(in regid int,IN user_type varchar(255),IN user_name varchar(255),
IN email_id varchar(255),
IN pass_word varchar(255),
IN moblie bigint,
IN landline bigint,
IN current_city int,
OUT messages varchar(100)
)
BEGIN
IF EXISTS(SELECT email_id from registration where email_id ='email_id');
IF ROW_COUNT()>0 THEN
set message='try again';
end if;
ELSE
insert INTO registration(registration_id,user_type,user_name,email_id,pass_word,moblie,landline,current_city) values (regid,user_type,user_name,email_id,pass_word,moblie,landline,current_city);
IF ROW_COUNT()> 0 THEN
set message='succcessfully inserted';
end if;
end;
END;




Responses

Posted by: Solijoseph on: 10/1/2012 [Member] Starter | Points: 25

Up
0
Down
SQL Express 2005


USE [master]
GO
/****** Object: StoredProcedure [dbo].[SP_Registration] Script Date: 10/01/2012 15:57:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Registration]
@regid int
,@user_type varchar(255)
,@user_name varchar(255)
,@email_id varchar(255)
,@pass_word varchar(255)
,@moblie bigint
,@landline bigint
,@current_city int


AS
BEGIN
DECLARE @message varchar(100)

IF EXISTS(SELECT email_id FROM registration WHERE email_id =@email_id)
BEGIN
SET @message='try again'
END
ELSE
BEGIN
INSERT INTO registration(registration_id,user_type,user_name,email_id,pass_word,moblie,landline,current_city) VALUES (@regid,@user_type,@user_name,@email_id,@pass_word,@moblie,@landline,@current_city);
IF @@ROWCOUNT> 0
SET @message='succcessfully inserted'
ELSE
SET @message='try again'
END
SELECT @message AS ReturnValue
END


Thanks & Regards
Solimon Joseph

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shanky11 on: 10/1/2012 [Member] Starter | Points: 25

Up
0
Down
thi s is not working

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Solijoseph on: 10/1/2012 [Member] Starter | Points: 25

Up
0
Down

tested with sql server 2008 r2

Thanks & Regards
Solimon Joseph

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vivekjj on: 10/1/2012 [Member] Starter | Points: 25

Up
0
Down
ALTER PROCEDURE [dbo].[SP_Registration] (
@regid int
,@user_type varchar(255)
,@user_name varchar(255)
,@email_id varchar(255)
,@pass_word varchar(255)
,@moblie bigint
,@landline bigint
,@current_city int ,
@message varchar(50) output)


AS
BEGIN
DECLARE @rcount int
set @rcount=0
SELECT email_id FROM registration WHERE email_id =@email_id
BEGIN
if @rcount>0
SET @message='try again'
END
ELSE
BEGIN
INSERT INTO registration(registration_id,user_type,user_name,email_id,pass_word,moblie,landline,current_city) VALUES (@regid,@user_type,@user_name,@email_id,@pass_word,@moblie,@landline,@current_city);
IF @@ROWCOUNT> 0
if @rcount>0
SET @message='succcessfully inserted'
ELSE
SET @message='try again'
END
SELECT @message AS ReturnValue
END

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Solijoseph on: 10/3/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Vivek
How to update @rcount value,
The above code always execute ELSE part only, what is the use of @rcount here..?

Thanks & Regards
Solimon Joseph

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response