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.

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
– Bill Gregg, Commented Jul 23, 2013 at 19:46
Yes it is returning. I will look into using a SELECT instead if it makes things easier. — danbroooks
– danbroooks, Commented Jul 23, 2013 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" stackoverflow.com/questions/10905782/… — granadaCoder
– granadaCoder, Commented Jul 23, 2013 at 19:54

marc_s · Accepted Answer · 2013-07-23 20:31:11Z

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.

answered Jul 23, 2013 at 20:31

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Kulat Over a year ago

Yes and you need to cast result as bit if you are returning boolean value in c#

Jon Raynor · Accepted Answer · 2013-07-23 20:32:10Z

1

Use this:

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

Your stored procedure should return 0 or 1.

answered Jul 23, 2013 at 20:32

Jon Raynor

3,9126 gold badges33 silver badges45 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related