|
P: 3
|
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
| |
Share this Question
| Expert 2.5K+
P: 2,878
|
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
| | | |  Question stats - viewed: 10280
- replies: 1
- date asked: Oct 20 '08
|
