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

ELSE IF statements

P: 11
Hi I have a stored procedure which returns DBNULL if "Line" is empty rather than returning "EquipmentNumber":

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[sp_get_distinct_lines]
  2. @Department varchar(50)
  3.  
  4. AS
  5.  
  6. IF EXISTS (SELECT Line FROM Equipment WHERE Department = @Department)
  7.  
  8. SELECT DISTINCT Line FROM Equipment WHERE Department = @Department
  9.  
  10. ELSE 
  11.  
  12. SELECT DISTINCT EquipmentNumber FROM Equipment WHERE Department = @Department
  13.  
Error messege "Unable to cast object of type 'System.DBNull' to type 'System.String'."
Mar 14 '09 #1
Share this Question
Share on Google+
1 Reply


P: 11
OK, found the answer myself....

Expand|Select|Wrap|Line Numbers
  1. IF (SELECT COUNT(Line) FROM Equipment WHERE Department = @Department) = 0
  2. BEGIN 
  3. SELECT DISTINCT EquipmentNumber FROM Equipment WHERE Department = @Department
  4. END
  5.  
  6. ELSE
  7. BEGIN
  8. SELECT DISTINCT Line FROM Equipment WHERE Department = @Department
  9. END
Mar 15 '09 #2

Post your reply

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