Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Unanswered: Stored procedure returns only null

    This stored procedure and the code that calls it runs, however I get null results. If anyone can point out my error I'd appreciate it,

    Pete
    Code:
    CREATE PROCEDURE GETEMPLOYEE
            -- declare required input parameter: the employee ID number, an integer
            @id int,
           
            -- declare the output paramters
            
            @employee_id int OUTPUT,
            @name_last  varchar(50) OUTPUT,
            @name_first  varchar(50) OUTPUT
    
    
            -- Return codes: 
            -- 0 == Success
            -- 1 == NULL value found
            -- 2 == No Records for requested ID exist
            -- 3 == Wrong data type
            -- 4 == System error
            -- 5 == Record for requested id exists
    AS
    
    IF @id IS NULL
            BEGIN 
                    PRINT 'Error: you must specify a value'
                    RAISERROR('NULL values are not allowed.', 16,1)
                    RETURN(1)
            END
    ELSE
            BEGIN
                     
                    IF(
                            SELECT COUNT(*) FROM Employee emp
                            WHERE emp.Emp_ID = @id
                         ) != 1
                            RAISERROR('No records match requested id', 16,1)                       
                             RETURN(2)                                      
            
                   
            END
    
    
    -- get the record for the requested ID number
    -- assign to OUTPUT
    SELECT @employee_id = emp.Emp_ID
    ,@name_last = emp.Last_Name
    ,@name_first = emp.First_Name 
    FROM Employee emp
    WHERE emp.Emp_ID = @id
    
    
    -- If server error 
    IF @@ERROR != 0
            BEGIN 
                    RAISERROR('ERROR in the server', 16, 1)
                    RETURN(4)
            END
    ELSE
            BEGIN
            -- if the query returned a null value
                    IF @employee_id = NULL
                            BEGIN
                            RAISERROR('The query returned a null value', 16,1)
                            RETURN(1)
                            END
    
                    ELSE
    
            -- we have a good query
            
                    RETURN(0)
    END
    GO
    
    DECLARE @rcode int,  @employee_id int,  @name_last varchar, @name_first varchar
    EXECUTE @rcode = GETEMPLOYEE @id = 5,
    @employee_id = @employee_id OUTPUT,
    @name_last = @name_last OUTPUT,
    @name_first =  @name_first OUTPUT
    
    SELECT EmpID = @employee_id, EMP_Name = @name_last  +  @name_first

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    I hope this works:::
    alter PROCEDURE GETEMPLOYEE
    -- declare required input parameter: the employee ID number, an integer
    @id int,

    -- declare the output paramters

    @employee_id int OUTPUT,
    @name_last varchar(50) OUTPUT,
    @name_first varchar(50) OUTPUT


    -- Return codes:
    -- 0 == Success
    -- 1 == NULL value found
    -- 2 == No Records for requested ID exist
    -- 3 == Wrong data type
    -- 4 == System error
    -- 5 == Record for requested id exists
    AS

    IF @id IS NULL
    BEGIN
    PRINT 'Error: you must specify a value'
    RAISERROR('NULL values are not allowed.', 16,1)
    -- RETURN(1)
    END
    ELSE
    BEGIN

    IF(
    SELECT COUNT(*) FROM Employee emp
    WHERE emp.Emp_ID = @id
    ) != 1
    RAISERROR('No records match requested id', 16,1)
    -- RETURN(2)


    END


    -- get the record for the requested ID number
    -- assign to OUTPUT
    SELECT @employee_id = emp.Emp_ID
    ,@name_last = emp.Last_Name
    ,@name_first = emp.First_Name
    FROM Employee emp
    WHERE emp.Emp_ID = @id


    -- If server error
    IF @@ERROR != 0
    BEGIN
    RAISERROR('ERROR in the server', 16, 1)
    -- RETURN(4)
    END
    ELSE
    BEGIN
    -- if the query returned a null value
    IF @employee_id = NULL
    BEGIN
    RAISERROR('The query returned a null value', 16,1)
    -- RETURN(1)
    END

    ELSE

    -- we have a good query

    RETURN(0)
    END
    GO

  3. #3
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    Thanks, but no luck....

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    forgive me for changing some much at once but I am an old codger that need things my way... That said, try this:

    Code:
    create table #Employee (Emp_ID int, Last_Name varchar(50), First_Name varchar(50))
    insert into #Employee values (1,'Donald','Duck')
    insert into #Employee values (2,'Mickey','Mouse')
    insert into #Employee values (3,'John','Doe')
    insert into #Employee values (4,'Jane','Doe')
    go
    if object_id('GETEMPLOYEE') is not null drop procedure GETEMPLOYEE
    go
    CREATE PROCEDURE GETEMPLOYEE
            -- declare required input parameter: the employee ID number, an integer
            @id int,
           
            -- declare the output paramters
            
            @employee_id int OUTPUT,
            @name_last  varchar(50) OUTPUT,
            @name_first  varchar(50) OUTPUT
            -- Return codes: 
            -- 0 == Success
            -- 1 == NULL value found
            -- 2 == No Records for requested ID exist
            -- 3 == Wrong data type
            -- 4 == System error
            -- 5 == Record for requested id exists
    AS
    IF @id IS NULL BEGIN 
       PRINT 'Error: you must specify a value'
       RAISERROR('NULL values are not allowed.', 16,1)
       RETURN(1)
    END
    
    -- Check for valid @id
    IF not exists(SELECT * FROM #Employee WHERE Emp_ID = @id) begin
        RAISERROR('No records match requested id', 16,1)                       
        RETURN(2)                                      
    end
    
    -- get the record for the requested ID number
    -- assign to OUTPUT
    SELECT @employee_id = Emp_ID
         , @name_last = Last_Name
         , @name_first = First_Name 
      FROM #Employee
     WHERE Emp_ID = @id
    
    -- If server error 
    IF @@ERROR != 0 BEGIN 
       RAISERROR('ERROR in the server', 16, 1)
       RETURN(4)
    END
    
    -- ?? Check for valid Employee ID ??   
    IF @employee_id is NULL BEGIN
       RAISERROR('The query returned a null value', 16,1)
       RETURN(1)
    END
    
    RETURN(0)
    
    GO
    DECLARE @rcode int,  @employee_id int,  @name_last varchar(50), @name_first varchar(50)
    EXECUTE @rcode = GETEMPLOYEE @id = 1, @employee_id = @employee_id OUTPUT, @name_last = @name_last OUTPUT, @name_first =  @name_first OUTPUT
    SELECT @employee_id as EmpID, cast(@name_last  + ' ' +  @name_first as varchar) as 'EMP_Name'
    
    EXECUTE @rcode = GETEMPLOYEE @id = 2, @employee_id = @employee_id OUTPUT, @name_last = @name_last OUTPUT, @name_first =  @name_first OUTPUT
    SELECT @employee_id as EmpID, cast(@name_last  + ' ' +  @name_first as varchar) as 'EMP_Name'
    
    EXECUTE @rcode = GETEMPLOYEE @id = 3, @employee_id = @employee_id OUTPUT, @name_last = @name_last OUTPUT, @name_first =  @name_first OUTPUT
    SELECT @employee_id as EmpID, cast(@name_last  + ' ' +  @name_first as varchar) as 'EMP_Name'
    
    EXECUTE @rcode = GETEMPLOYEE @id = 4, @employee_id = @employee_id OUTPUT, @name_last = @name_last OUTPUT, @name_first =  @name_first OUTPUT
    SELECT @employee_id as EmpID, cast(@name_last  + ' ' +  @name_first as varchar) as 'EMP_Name'
    Obveously you will need to change the reference to '#Employee' to 'Employee'
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Stored procedure returns only null

    Take o look at this fragment from your code:



    "BEGIN
    -- if the query returned a null value
    IF @employee_id = NULL "?????????????

    change @employee_id=NULL with @employee_id is null



    IONUT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •