October 20, 2008 at 11:56 am
I'm trying to create an if/else case in a stored procedure where if the 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.
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 1shift, 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
October 20, 2008 at 12:32 pm
You can't use Set @var = column without a table reference. You want something like this:
[font="Courier New"]SELECT TOP 1
@shiftout = ISNULL(S.shift, 0),
@operator_num = ISNULL(S.operator, ''),
@pack_num = ISNULL(S.pack, ''),
@item1 = ISNULL(S.item1, ''),
@item2 = ISNULL(S.item2, ''),
@item3 = ISNULL(S.item3, ''),
@item4 = ISNULL(S.item4, ''),
@cutQty = ISNULL(S.cutQty, ''),
@cutTime = ISNULL(S.cutTime, ''),
@sequence = ISNULL(S.sequence, 0),
@operator_name = ISNULL(O.emp_name, ''),
@pack_name = ISNULL(O.emp_name, '')
FROM
schedule S JOIN
operators O ON
S.operator = O.emp_num JOIN
operators P ON
S.operator_num = P.emp_num
WHERE
facility=@facility AND
line=@line AND
shift=@shiftin
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2008 at 12:41 pm
Jack,
Thanks...I'll give that a shot. I appreciate the help while trying to learn the syntax!
Jeff
October 20, 2008 at 12:49 pm
you should directly move the data into your varriables:
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
from schedule where
facility=@facility and
line=@line and
shift=@shiftin
if @@ROWCOUNT = 0
BEGIN
select @shiftout=0
, @sequence=0
, @operator_num=''
, @operator_name=''
, @pack_num=''
, @pack_name=''
, @item1=''
, @item2=''
, @item3=''
, @item4=''
, @cutQty=''
, @cutTime=''
End
Else
BEGIN
select top 1 @operator_name = emp_name from operators where
emp_num=@operator_num
select top 1 @pack_name = emp_name from operators where
emp_num=@pack_num
END
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 20, 2008 at 12:52 pm
In fact, Jacks solution is better, except for the isnull testing.
There it depends if your columns are allowed to contain nulls or not.
And you should still perform :
if @@ROWCOUNT = 0
BEGIN
select @shiftout=0
, @sequence=0
, @operator_num=''
, @operator_name=''
, @pack_num=''
, @pack_name=''
, @item1=''
, @item2=''
, @item3=''
, @item4=''
, @cutQty=''
, @cutTime=''
End
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 20, 2008 at 1:40 pm
Guys,
Thanks alot...once I put the two together, it worked like a charm, and gave me some more good insight!
I really appreciate it!
Jeff
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply