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

c#, sql-server, stored-procedures

Solution

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.

Problem

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.

Original source

Related problems