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

Question

I created a stored procedure that takes one argument, the name of the table, and returns 1 if it exists in the database, 0 if it is not. In SQL Server Management Studio, testing my stored procedure works exactly as I would like, however, I had problems getting this value for use in my C # program.

My parameters seem to be ExecuteScalar() , ExecuteNonQuery() or ExecuteReader() , none of which are suitable for the task, and I cannot get them to get the result of my stored procedure.

I tried to assign 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 one that selects either 0 (the table does not exist) or 1 (the table exists)

 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 expect only one row, one 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 0 if the table does not exist, or 1 if it exists.

Answer 2

Use this:

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

Your stored procedure should return 0 or 1.