SQL IF...ELSE syntax

[Jeffy]

I'm trying to create an if/else case in a stored procedure where if te record is not found, it returns blank values, and if it is found I get the real values. But when I try to execute the SP update I get column does not exist for the else clause (all columns starting with shift through sequence)

I'm sure this is a simple problem, and I apologize in advance.

USE [ADC]
GO
/****** Object:  StoredProcedure [dbo].[getSchedule]    Script Date: 10/20/2008 11:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Jeffrey Grace
-- Create date: 2008-10-16
-- Description:    Gets scheduled data and returns to PLC
-- =============================================
ALTER PROCEDURE [dbo].[getSchedule]
    -- Input
    @facility char(2),
    @line int,
    @shiftin int,
 
    --@sequence int,
    -- Output
    @shiftout int output,    
    @sequence int output,    
    @operator_num char(8) output,
    @operator_name char(30) output,
    @pack_num char(8) output,
    @pack_name char(30) output,
    @item1 char(15) output,
    @item2 char(15) output,
    @item3 char(15) output,
    @item4 char(15) output,
    @cutQty char(10) output,
    @cutTime char(4) output
 
AS
BEGIN
    SET NOCOUNT ON;
    select top 1    shift, operator, pack, item1,
        item2, item3, item4, cutQty, cutTime, sequence
        from schedule where 
            facility=@facility and
            line=@line and
            shift=@shiftin 
 
    if @@ROWCOUNT = 0
    BEGIN
        set @shiftout=0
        set    @sequence=0
        set    @operator_num=''
        set    @operator_name=''
        set    @pack_num=''
        set    @pack_name=''
        set    @item1=''
        set    @item2=''
        set    @item3=''
        set    @item4=''
        set    @cutQty=''
        set    @cutTime=''
    End
 
    Else
    BEGIN        
        set        @shiftout=shift
        set        @operator_num = operator
        set        @pack_num = pack
        set        @item1 = item1
        set        @item2 = item2
        set        @item3 = item3
        set        @item4 = item4
        set        @cutQty = cutQty
        set        @cutTime = cutTime
        set        @sequence = sequence
 
        select @operator_name = emp_name from operators where 
            emp_num=@operator_num
 
        select @pack_name = emp_name from operators where 
            emp_num=@pack_num
    END
END

[ck9663]

SQL Server does not always work like DBF or recordset processing ;)

Try the following.....I did not test this for any error but you should get the idea of what I'm trying to say. Also, depending on your requirement, change the type of JOIN as necessary.

USE [ADC]
GO
/****** Object:  StoredProcedure [dbo].[getSchedule]    Script Date: 10/20/2008 11:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Jeffrey Grace
-- Create date: 2008-10-16
-- Description:    Gets scheduled data and returns to PLC
-- =============================================
ALTER PROCEDURE [dbo].[getSchedule]
    -- Input
    @facility char(2),
    @line int,
    @shiftin int,
 
    --@sequence int,
    -- Output
    @shiftout int output,    
    @sequence int output,    
    @operator_num char(8) output,
    @operator_name char(30) output,
    @pack_num char(8) output,
    @pack_name char(30) output,
    @item1 char(15) output,
    @item2 char(15) output,
    @item3 char(15) output,
    @item4 char(15) output,
    @cutQty char(10) output,
    @cutTime char(4) output
 
AS
BEGIN
    SET NOCOUNT ON;
 
    select top 1 @shiftout=shift, @operator_num = operator, @pack_num = pack, @item1 = item1,
        @item2 = item2, @item3 = item3, @item4 = item4, @cutQty = cutQty, @cutTime = cutTime, @sequence = sequence,
        @operator_name = op_name.emp_name, @pack_name = pk_name.emp_name
        from schedule s
    left join operators op_name on emp_num = s.operator
        left join operators pk_name on emp_num = s.pack
        where facility=@facility and line=@line andshift=@shiftin 
 
    if @@ROWCOUNT = 0
        select @shiftout=0, @sequence=0, @operator_num='',@operator_name='',@pack_num='',@pack_name='',@item1='',@item2='',@item3='',@item4='',@cutQty='', @cutTime=''
END
 
 

Happy coding!

-- CK