8

I have created a stored procedure that takes a single argument, the name of a table, and returns 1 if it exists in the database, 0 if it does not. In SQL Server Management Studio testing my stored procedure works exactly as I'd like it to, however I'm having trouble getting that value for use in my C# program.

My options seem to be ExecuteScalar(), ExecuteNonQuery() or ExecuteReader(), none of which seem appropriate for the task, nor can I get them to even retrieve my stored procedure's result.

I have tried assigning my parameter with both cmd.Parameters.AddWithValue and cmd.Parameters.Add again to no avail.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
danbroooks
  • 2,712
  • 5
  • 21
  • 43
  • 1
    Is your stored procedure returning or selecting the result? It is easier if you "select TableExists=1" instead of "return 1". Then you can use ExecuteScalar(). – Bill Gregg Jul 23 '13 at 19:46
  • Yes it is returning. I will look into using a SELECT instead if it makes things easier. – danbroooks Jul 23 '13 at 19:48
  • I prefer an output parameter. But its a personal preference. See the answer to this one. But "yes" to the "use the SELECT" http://stackoverflow.com/questions/10905782/using-stored-procedure-output-parameters-in-c-sharp – granadaCoder Jul 23 '13 at 19:54
  • marc_s answer that is. – granadaCoder Jul 23 '13 at 19:55

2 Answers2

19

Assuming you have a stored procedure like this which selects either a 0 (table does not exist) or 1 (table does exist)

CREATE PROCEDURE dbo.DoesTableExist (@TableName NVARCHAR(100))
AS 
BEGIN
    IF EXISTS (SELECT * FROM sys.tables WHERE Name = @TableName)
        SELECT 1
    ELSE
        SELECT 0  
END

then you can write this C# code to get the value - use .ExecuteScalar() since you're expecting only a single row, single column:

// set up connection and command
using (SqlConnection conn = new SqlConnection("your-connection-string-here"))
using (SqlCommand cmd = new SqlCommand("dbo.DoesTableExist", conn))
{
    // define command to be stored procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // add parameter
    cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 100).Value = "your-table-name-here";

    // open connection, execute command, close connection
    conn.Open();
    int result = (int)cmd.ExecuteScalar();
    conn.Close();
}

Now result will contain either a 0 if the table doesn't exist - or 1, if it does exist.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

Use this:

 var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
 returnParameter.Direction = ParameterDirection.ReturnValue;

Your stored procedure should return 0 or 1.

Jon Raynor
  • 3,804
  • 6
  • 29
  • 43