SQL Server stored procedure that returns a boolean if table exists, c# implementation

Question

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.

Answer 1

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.