如果表存在，则返回布尔值的 SQL Server 存储过程，c# 实现
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17819610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server stored procedure that returns a boolean if table exists, c# implementation
提问by danbroooks
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.
我创建了一个存储过程，它接受一个参数，一个表的名称，如果它存在于数据库中，则返回 1，否则返回 0。在 SQL Server Management Studio 测试中，我的存储过程完全按照我的意愿工作，但是我无法在我的 C# 程序中获取该值。
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.
我的选项似乎是ExecuteScalar()，ExecuteNonQuery()或ExecuteReader()，它们似乎都不适合该任务，我什至无法让它们检索我的存储过程的结果。
I have tried assigning my parameter with both cmd.Parameters.AddWithValueand cmd.Parameters.Addagain to no avail.
我试图赋予我的两个参数cmd.Parameters.AddWithValue，并cmd.Parameters.Add再次无果。
采纳答案by marc_s
Assuming you have a stored procedure like this which selects either a 0 (table does not exist) or 1 (table does exist)
假设您有一个这样的存储过程，它选择 0（表不存在）或 1（表确实存在）
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:
然后您可以编写此 C# 代码来获取该值 - 使用，.ExecuteScalar()因为您只期望单行单列：
// 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 resultwill contain either a 0if the table doesn't exist - or 1, if it does exist.
现在result将包含 a0如果表不存在 - 或者1如果它确实存在。
回答by Jon Raynor
Use this:
用这个：
var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
Your stored procedure should return 0 or 1.
您的存储过程应该返回 0 或 1。
