none
Return value from Stored Procedure RRS feed

  • Question

  • The following code always return False even though it finds a record, what am I doing wrong?

            protected bool GetUID(string sUserID)
            {
                bool exists;
    
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("CheckExistingID", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter param = new SqlParameter();
                        cmd.Parameters.Add(new SqlParameter("@given_id", sUID));
                        param.Direction = ParameterDirection.Input;
                        cmd.Connection = con;
                        con.Open();
                        var result = cmd.ExecuteScalar();
                        
                        exists = result != null ? (int)result > 0 : false;
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                            con.Close();
                    }
                }
                if (exists)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
    
    
    ********************* Stored Procedure*********************
    
    ALTER PROCEDURE [dbo].[CheckExistingEntityTest]  
    (  
      @given_id as varchar(9)  
    )  
    AS  
    BEGIN
    SET NOCOUNT ON;
    DECLARE @Exists INT
    
    IF EXISTS(
    	SELECT top 1 FirstName, LastName from Users
    				WHERE UserID = @given_id
    	)
    BEGIN
    	SET @Exists = 1
          END
          ELSE
          BEGIN
                SET @Exists = 0
          END
     
          RETURN @Exists
    END
    
    

    Thursday, May 21, 2020 3:16 PM

Answers

All replies

  • Perhaps

    CREATE PROCEDURE dbo.DoesTableExist (@TableName NVARCHAR(100))
    AS 
    BEGIN
        IF EXISTS (SELECT * FROM sys.tables WHERE Name = @TableName)
            SELECT 1
        ELSE
            SELECT 0  
    END

    https://stackoverflow.com/questions/17819610/sql-server-stored-procedure-that-returns-a-boolean-if-table-exists-c-sharp-impl


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Shayaan Thursday, May 21, 2020 6:34 PM
    Thursday, May 21, 2020 3:56 PM
    Moderator
  • To get the result returned by RETURN statement, you can define a parameter (any name) that has a ParameterDirection.ReturnValue direction.

    But since you use ExecuteScalar, consider the next body of stored procedure too:

       SELECT TOP(1) 1 FROM . . .

    Then:

       var result = cmd.ExecuteScalar();

       exists = result != null;

       . . .


    • Edited by Viorel_MVP Thursday, May 21, 2020 6:10 PM
    Thursday, May 21, 2020 6:08 PM
  • thanks, it worked.
    Thursday, May 21, 2020 6:34 PM