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.

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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

SQL IF...ELSE syntax

Post your reply

Similar topics