CODEWITHSUNDEEP
c#.netsqlsql-serverstored-procedures
gunwin
gunwin

Reputation: 4832

SQL Stored Procudure returning no value

I have to following sproc:

USE [CW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[addCustomer]
@firstname VARCHAR(50) = '',
@lastname VARCHAR(50) = '',
@email VARCHAR(50) = '',
@password VARCHAR(50) = ''


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
SELECT * FROM customer WHERE email = @email

IF (@@ROWCOUNT = 0)
    BEGIN
        -- Add to database.
        INSERT INTO customer (firstname, lastname, email, [password]) 
        VALUES (@firstname, @lastname, @email, @password);
        SELECT @@IDENTITY;
    END
ELSE
    BEGIN
        -- Don't add already registered
        SELECT customer.customerID FROM customer WHERE email = @email;
    END


END

It's to add a customer to a table. If their email is already in the table, it returns the id for that customer. If the email is not in the table already, it creates it and returns the new id.

The problem is, when its creating the new record, it returns null. But when I execute the sproc in Management Studio it shows a correct return value.

Here's the code:

            SqlCommand cmd0 = new SqlCommand();
            cmd0.Connection = conn;
            cmd0.CommandType = CommandType.StoredProcedure;
            cmd0.CommandText = "addCustomer";
            cmd0.Parameters.AddWithValue("@firstname", firstname);
            cmd0.Parameters.AddWithValue("@lastname", lastname);
            cmd0.Parameters.AddWithValue("@email", email);
            cmd0.Parameters.AddWithValue("@password", password);

            var scaled = cmd0.ExecuteScalar();
            customerID = scaled.ToString();

customerID is null when the email is unique.

Upvotes: 0

Views: 188

Answers (4)

Scott Bruns
Scott Bruns

Reputation: 1981

Grant,

Using @@ROWCOUNT is not the proper way to make sure the customer has not already been entered. It only tells you that the customer was not in the table when you checked.

The proper way is to apply a unique constraint on the email column. Then try inserting the customer row. If the row inserts then the customer is entered. If you get a unique violation then the customer already exists and you can select/return the customerID.

Once that works do what Nik already said.

Upvotes: 0

domager
domager

Reputation: 769

Give this a try:

ALTER PROCEDURE [dbo].[addCustomer]
    @firstname VARCHAR(50) = '',
    @lastname VARCHAR(50) = '',
    @email VARCHAR(50) = '',
    @password VARCHAR(50) = ''
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM customer WHERE email = @email)
    INSERT INTO customer (firstname, lastname, email, [password]) 
    VALUES (@firstname, @lastname, @email, @password);

SELECT customer.customerID FROM customer WHERE email = @email;

GO

Upvotes: 2

Mike Miller
Mike Miller

Reputation: 16575

The else is redundant the first select statement is doing the same. I think that executescalar is using the first result set not the second, hence not @@identity.

Use this query first

SELECT customer.customerID FROM customer WHERE email = @email;

and get rid of the else. Then use the return value approach as suggested by the other answer.

Upvotes: 2

Nik
Nik

Reputation: 7273

You're not returning a scalar, you're selecting from a table, which is different. You want a RETURN statement. An alternative is to use an OUTPUT parameter. You can set your parameter in C# to be an output parameter and after you run the procedure, you can read the value from it.

Upvotes: 4

Related Questions