exec command in stored procedure depends on condition

[u4umang2001]

Hi

i want to insert the email into a table,
i pass the parameter to the stored procedure

if this email_id doesnt exists in the table
only then it should be inserted into the table...

how to do this using a stored procedure

please help
its urgent!!!

[serkan sendur]

this t-sql below does what you want :

declare @email nvarchar(50)
set @email = 'test'
if not exists (select * from yourtable where email=@email )
insert into yourtable(email) values(@email )

[u4umang2001]

thanks

but i want to display the message also if email exists

then wht can i do for that???

[u4umang2001]

how can i show that message at my .aspx page???

[serkan sendur]

execute the following query in ExecuteScalar mode, and display the return value to the user.

declare @email nvarchar(50)
set @email = 'test'
if not exists (select * from yourtable where email=@email )
begin
insert into yourtable(email) values(@email )
select 'inserted successfully'
end
else
begin
select 'the provided email already exists in our records'
end

[u4umang2001]

no
its not working...

[serkan sendur]

ok i will go step by step :
first create your stored procedure in sql server with the following :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE myInsertProcedure
@email nvarchar(50)
AS
BEGIN

SET NOCOUNT ON;

if not exists (select * from yourtable where email=@email )
begin
insert into yourtable(email) values(@email )
select 'inserted successfully'
end
else
begin
select 'the provided email already exists in our records'
end
END
GO

then go to your aspx page and create the markup below :

lastly double click your button and type the following :
SqlConnection sqlCon = new SqlConnection("Data Source=ALPC28\\SERKANS2005;Initial Catalog=denemeler;Integrated Security=True;Pooling=False");
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom.CommandText = "myInsertProcedure";
sqlCom.Connection = sqlCon;
SqlParameter sqlPar = new SqlParameter("@email", SqlDbType.VarChar);
sqlPar.Value = TextBox1.Text;
sqlCom.Parameters.Add(sqlPar);
string notification;
sqlCon.Open();
notification = sqlCom.ExecuteScalar().ToString();
sqlCon.Close();
Response.Write(notification);

i tried this creating a sample application, it works, just modify your connection string and adjust everything according to your webpage