如何从SQl存储过程中获取确切的布尔值？ [英] How to get exact boolean values from SQl stored procedure?

问题描述

我有这个存储过程在Management Studio中正常工作，提取物理路径中存在的所有数据库mdf文件的名称。如果主文件中存在文件名，则返回1，否则返回0.这没关系。

I have this stored procedure which is working fine in Management Studio,extracts the name of all those database mdf files present in physical path. If a file name exists in master files, it returns 1 and if not it returns 0.This is fine.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[GetExistingDatabasePath]    Script Date: 06/19/2014 07:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetExistingDatabasePath]
@enteredDatabaseName varchar(50)
 
AS
BEGIN
IF EXISTS(
SELECT name, physical_name AS current_file_location FROM sys.master_files 
where name=@enteredDatabaseName)
RETURN 1
ELSE
RETURN 0

END

现在，对应sp，我有以下c＃代码。

Now, corresponding to the sp , i have following c# code.

string connectionString = "uid=sa;password=login@234;server=BNGTSIDTPC0738;database=master";
SqlConnection connection = new SqlConnection(connectionString);


//stored procedure to check if the database with this name exist or not in physical drive
SqlCommand cmd = new SqlCommand("GetExistingDatabasePath", connection);
cmd.CommandType = CommandType.StoredProcedure;

//local variable to store data entered by user in textbox
string userSideDatabaseName;

//assigning the database entered in textbox to enter the variable 

Console.WriteLine("Enter the database name");
userSideDatabaseName = Console.ReadLine();

//passing the this text box data to stored procedure parameter
SqlParameter param1 = cmd.Parameters.Add("@enteredDatabaseName", SqlDbType.VarChar, 50);
param1.Value = userSideDatabaseName;
bool IsRowExisting = false;
connection.Open();
//this will return true if there is existence of Database with same name in Physical path
IsRowExisting = Convert.ToBoolean(cmd.ExecuteNonQuery());
connection.Close();
if (IsRowExisting == true)
{

Console.WriteLine(IsRowExisting);
}

问题是代码执行后，无论文件是否存在，它都返回true。

Problem is when the code is executed then it returns true for all cases whether the file exists or not.

推荐答案

SQL Server中的存储过程只能返回整数。如果你需要返回除一个整数之外的任何东西，那么你应该使用这些方法之一（一些由前面的答案解释）：



在你的使用SELECT程序



使用OUTPUT参数



使用用户自定义函数



按如下方式更新您的存储过程和代码：

Stored Procedures in SQL Server can only RETURN integers. If you need to return anything other than a single integer, then you should use one of these methods (some explained by the previous answers):

Use a SELECT in your procedure

Use an OUTPUT Parameter

Use a User Defined Function instead

Update your Stored procedure and code as follows:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[GetExistingDatabasePath] Script Date: 06/19/2014 07:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GetExistingDatabasePath]
@enteredDatabaseName varchar(50)
@Result BIT OUTPUT

AS
BEGIN
IF EXISTS(
SELECT name, physical_name AS current_file_location FROM sys.master_files 
where name=@enteredDatabaseName)
SET @Result = 1
ELSE
SET @Result = 0
 
END

Now, corresponding to the sp , i have following c# code.
string connectionString = "uid=sa;password=login@234;server=BNGTSIDTPC0738;database=master";
SqlConnection connection = new SqlConnection(connectionString);
 

//stored procedure to check if the database with this name exist or not in physical drive
SqlCommand cmd = new SqlCommand("GetExistingDatabasePath", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//local variable to store data entered by user in textbox
string userSideDatabaseName;
 
//assigning the database entered in textbox to enter the variable 
 
Console.WriteLine("Enter the database name");
userSideDatabaseName = Console.ReadLine();

//passing the this text box data to stored procedure parameter
SqlParameter param1 = cmd.Parameters.Add("@enteredDatabaseName", SqlDbType.VarChar, 50);
   SqlParameter param2 = cmd.Parameters.Add("@Result", SqlDbType.Bit);
    param2.Direction = ParameterDirection.Output; 
param1.Value = userSideDatabaseName;
bool IsRowExisting = false;
connection.Open();
//this will return true if there is existence of Database with same name in Physical path
//IsRowExisting = Convert.ToBoolean(cmd.ExecuteNonQuery());
IsRowExisting = Convert.ToBoolean(param2.Value);
connection.Close();
if (IsRowExisting == true)
{
 
Console.WriteLine("Already exists!");
}

这篇关于如何从SQl存储过程中获取确切的布尔值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！