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
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);
SqlCommand cmd = new SqlCommand("GetExistingDatabasePath", connection);
cmd.CommandType = CommandType.StoredProcedure;
string userSideDatabaseName;
Console.WriteLine("Enter the database name");
userSideDatabaseName = Console.ReadLine();
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();
IsRowExisting = Convert.ToBoolean(param2.Value);
connection.Close();
if (IsRowExisting == true)
{
Console.WriteLine("Already exists!");
}