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