By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

SQL IF...ELSE syntax

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.

Expand|Select|Wrap|Line Numbers
  1. USE [ADC]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[getSchedule]    Script Date: 10/20/2008 11:54:55 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:        Jeffrey Grace
  10. -- Create date: 2008-10-16
  11. -- Description:    Gets scheduled data and returns to PLC
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[getSchedule]
  14.     -- Input
  15.     @facility char(2),
  16.     @line int,
  17.     @shiftin int,
  18.  
  19.     --@sequence int,
  20.     -- Output
  21.     @shiftout int output,    
  22.     @sequence int output,    
  23.     @operator_num char(8) output,
  24.     @operator_name char(30) output,
  25.     @pack_num char(8) output,
  26.     @pack_name char(30) output,
  27.     @item1 char(15) output,
  28.     @item2 char(15) output,
  29.     @item3 char(15) output,
  30.     @item4 char(15) output,
  31.     @cutQty char(10) output,
  32.     @cutTime char(4) output
  33.  
  34. AS
  35. BEGIN
  36.     SET NOCOUNT ON;
  37.     select top 1    shift, operator, pack, item1,
  38.         item2, item3, item4, cutQty, cutTime, sequence
  39.         from schedule where 
  40.             facility=@facility and
  41.             line=@line and
  42.             shift=@shiftin 
  43.  
  44.     if @@ROWCOUNT = 0
  45.     BEGIN
  46.         set @shiftout=0
  47.         set    @sequence=0
  48.         set    @operator_num=''
  49.         set    @operator_name=''
  50.         set    @pack_num=''
  51.         set    @pack_name=''
  52.         set    @item1=''
  53.         set    @item2=''
  54.         set    @item3=''
  55.         set    @item4=''
  56.         set    @cutQty=''
  57.         set    @cutTime=''
  58.     End
  59.  
  60.     Else
  61.     BEGIN        
  62.         set        @shiftout=shift
  63.         set        @operator_num = operator
  64.         set        @pack_num = pack
  65.         set        @item1 = item1
  66.         set        @item2 = item2
  67.         set        @item3 = item3
  68.         set        @item4 = item4
  69.         set        @cutQty = cutQty
  70.         set        @cutTime = cutTime
  71.         set        @sequence = sequence
  72.  
  73.         select @operator_name = emp_name from operators where 
  74.             emp_num=@operator_num
  75.  
  76.         select @pack_name = emp_name from operators where 
  77.             emp_num=@pack_num
  78.     END
  79. END
Oct 20 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
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.


Expand|Select|Wrap|Line Numbers
  1. USE [ADC]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[getSchedule]    Script Date: 10/20/2008 11:54:55 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:        Jeffrey Grace
  10. -- Create date: 2008-10-16
  11. -- Description:    Gets scheduled data and returns to PLC
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[getSchedule]
  14.     -- Input
  15.     @facility char(2),
  16.     @line int,
  17.     @shiftin int,
  18.  
  19.     --@sequence int,
  20.     -- Output
  21.     @shiftout int output,    
  22.     @sequence int output,    
  23.     @operator_num char(8) output,
  24.     @operator_name char(30) output,
  25.     @pack_num char(8) output,
  26.     @pack_name char(30) output,
  27.     @item1 char(15) output,
  28.     @item2 char(15) output,
  29.     @item3 char(15) output,
  30.     @item4 char(15) output,
  31.     @cutQty char(10) output,
  32.     @cutTime char(4) output
  33.  
  34. AS
  35. BEGIN
  36.     SET NOCOUNT ON;
  37.  
  38.     select top 1 @shiftout=shift, @operator_num = operator, @pack_num = pack, @item1 = item1,
  39.         @item2 = item2, @item3 = item3, @item4 = item4, @cutQty = cutQty, @cutTime = cutTime, @sequence = sequence,
  40.         @operator_name = op_name.emp_name, @pack_name = pk_name.emp_name
  41.         from schedule s
  42.     left join operators op_name on emp_num = s.operator
  43.         left join operators pk_name on emp_num = s.pack
  44.         where facility=@facility and line=@line andshift=@shiftin 
  45.  
  46.     if @@ROWCOUNT = 0
  47.         select @shiftout=0, @sequence=0, @operator_num='',@operator_name='',@pack_num='',@pack_name='',@item1='',@item2='',@item3='',@item4='',@cutQty='', @cutTime=''
  48. END
  49.  
  50.  
Happy coding!

-- CK
Oct 21 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.