0

I am currently working with C#, SQL Server and writing a Web API application.

I am creating SQL Server stored procedures and have several working, including inserting into the database, along with updating entries in the database. However, I am now trying to work with outputs, and am struggling with something simple.

I am looking to pass in an email address, and return the row id for the first match.

Here is the stored procedure:

CREATE PROCEDURE USER_PROCEDURE_GET_ID
    @Email VARCHAR(200),
    @id int Out
AS
    SELECT * 
    FROM [dbo].[Users]
    WHERE Email = @Email

    SET @id = id

    RETURN @id

Here is the C# code

public int GetID( string emailAddress )
{
// Create an SQL command
SqlCommand command = new SqlCommand();
command.Connection = this.sqlConnection;
// Set the command type to use a stored procedure
command.CommandType = CommandType.StoredProcedure;
// The name of the stored procedure
command.CommandText = "USER_PROCEDURE_GET_ID";

// Add the email address
command.Parameters.Add( new SqlParameter( "@email", emailAddress ) );

// Add the output param
SqlParameter outputParam = new SqlParameter( "@id", SqlDbType.Int )
{
    Direction = ParameterDirection.Output
};
command.Parameters.Add( outputParam );

// Create the reader
SqlDataReader reader = null;

// Execute the command
try
{
    reader = command.ExecuteReader();
}
catch (InvalidCastException ex)
{
}
catch (SqlException ex)
    {
}
catch (InvalidOperationException ex)
{
}
catch (IOException ex)
{
}
finally
{
       reader.Close();
    }

Debug.Write( "user id: " + outputParam.Value.ToString() );

return 0;
}

I am aware that i am am not handling exceptions properly and that I'm not doing anything with the value returned. At this point I don't get a value returned and also get an error:

System.NullReferenceException: Object reference not set to an instance of an object.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
PersuitOfPerfection
  • 1,009
  • 1
  • 15
  • 28
  • At what point do you get the error? Is it when you call ExēcuteReader? – Paul Michaels Jun 27 '14 at 17:25
  • But when you set the @ID where do you get that ID in the sp? – Steve Jun 27 '14 at 17:26
  • @PM, exactly yeah. Steve I am getting the ID from the database field, i assume. I'm just coming into C# from other languages and my only previous experience with SQL was with PHP.. So i'm a bit new to this atm. – PersuitOfPerfection Jun 27 '14 at 17:26
  • My wild guess is you get nothing returned, and then try to read what it is and it's null. Have you tried grabbing the values before the cmd.ExecuteReader() and then running that query? What does it return? Also you should be able to do just return @@IDENTITY but reading this http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row thread would probably be worth while for deciding that. – Nyra Jun 27 '14 at 17:27

2 Answers2

3

You need to set the @id in the select clause and you don't return it either (it's an out parameter).

CREATE PROCEDURE USER_PROCEDURE_GET_ID
@Email varchar(255),
@id int Out
AS

SELECT @id = id FROM [dbo].[Users]
WHERE Email = @Email

This really doesn't need a stored procedure though. Just query it.

Community
  • 1
  • 1
dotjoe
  • 26,242
  • 5
  • 63
  • 77
  • 1
    That did the trick! Also a stupid mistake was me setting email to int rather than a VARCHAR, silly typo. Thanks everyone for the extremely fast responses! – PersuitOfPerfection Jun 27 '14 at 17:31
  • Also.. may i ask why I should avoid stored procedures for this ? I've been reading up on SQL Injection prevention and it seemed stored procedures where the way to go. Unless I am just getting a little carried away with it, and don't need to use it for query type statements? – PersuitOfPerfection Jun 27 '14 at 17:33
  • As long as you use parameters (SqlParameter) in your queries, you don't need to worry about sql injection. You could still have injection problems if you used a stored procedure without parameters. Example `string sql = "exec USER_PROCEDURE_GET_ID '" + emailString + "'";` – dotjoe Jun 27 '14 at 17:36
  • That is what I assumed, but a friend of mine advised using stored procedures + parameters. Thanks for the extra advice, really appreciated. – PersuitOfPerfection Jun 27 '14 at 17:38
1

I'm not sure what you have here really works correctly (not sure how you are setting the @id output parameter), but I also believe there is a simpler way to do what you are asking. When you say you just want a single value of the first row, it sounds like you want to be using ExecuteScalar instead of a reader with an output parameter, because there's some weird requirements around output parameters and result sets that I don't remember.

I would say what you should do is this.

object result = command.ExecuteScalar("SELECT TOP 1 Id FROM [dbo].[Users] WHERE Email = @Email");
if (!DBNull.Value.Equals(result)) return (int)value;

// return whatever value you want for "no-match" here
Darren Kopp
  • 76,581
  • 9
  • 79
  • 93