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

Cases in Select, Where, and OrderBy Dynamic Clause

dcharnigo
P: 20
Well I am at it again with my complex statements, I need some guidance on this one, in case you cannot tell I am a C++/C# programmer and not used to SQL, I think it is clear what I am trying to accomplish by what I have but ask if you need me to clarify:

Expand|Select|Wrap|Line Numbers
  1. PROCEDURE [dbo].[spGetCIFSearch] (
  2.     @type int,
  3.     @max int,
  4.     @data varchar(25),
  5.     @flag int
  6. )
  7. AS
  8. SET NOCOUNT ON
  9.  
  10. SELECT * INTO ##Temp FROM zCIFRecord AS C
  11. WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
  12. AND CIFUpdateActivity NOT LIKE 'D'
  13.  
  14. BEGIN
  15. IF @flag = 0
  16.     BEGIN
  17.         SELECT COUNT(*)
  18.     END
  19. ELSE
  20.     BEGIN
  21.         SELECT (@max)
  22.             CIFPan, 
  23.             CIFMemNum, 
  24.             CIFLName, 
  25.             CIFFName, 
  26.             CIFInitial 
  27.     END
  28.  
  29. FROM ##Temp
  30.  
  31. WHERE 
  32.     CASE
  33.         WHEN @type = '1' THEN CIFFName LIKE @data
  34.         WHEN @type = '2' THEN CIFLName LIKE @data
  35.         WHEN @type = '3' THEN CIFFName+' '+CIFLName LIKE @data
  36.         WHEN @type = '4' THEN CIFPan LIKE @data
  37.     END
  38. ORDER BY
  39.     CASE
  40.         WHEN @type = '1' THEN CIFFName DESC
  41.         WHEN @type = '2' OR '3' THEN CIFLName DESC
  42.         WHEN @type = '4' THEN CIFPan ASC
  43.     END
  44. END
  45.  
  46. DROP TABLE ##Temp
  47.  
Clearly I have several syntax errors, is it even possible to do what I want?
Apr 22 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Well I am at it again with my complex statements, I need some guidance on this one, in case you cannot tell I am a C++/C# programmer and not used to SQL, I think it is clear what I am trying to accomplish by what I have but ask if you need me to clarify:

Expand|Select|Wrap|Line Numbers
  1. PROCEDURE [dbo].[spGetCIFSearch] (
  2.     @type int,
  3.     @max int,
  4.     @data varchar(25),
  5.     @flag int
  6. )
  7. AS
  8. SET NOCOUNT ON
  9.  
  10. SELECT * INTO ##Temp FROM zCIFRecord AS C
  11. WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
  12. AND CIFUpdateActivity NOT LIKE 'D'
  13.  
  14. BEGIN
  15. IF @flag = 0
  16.     BEGIN
  17.         SELECT COUNT(*)
  18.     END
  19. ELSE
  20.     BEGIN
  21.         SELECT (@max)
  22.             CIFPan, 
  23.             CIFMemNum, 
  24.             CIFLName, 
  25.             CIFFName, 
  26.             CIFInitial 
  27.     END
  28.  
  29. FROM ##Temp
  30.  
  31. WHERE 
  32.     CASE
  33.         WHEN @type = '1' THEN CIFFName LIKE @data
  34.         WHEN @type = '2' THEN CIFLName LIKE @data
  35.         WHEN @type = '3' THEN CIFFName+' '+CIFLName LIKE @data
  36.         WHEN @type = '4' THEN CIFPan LIKE @data
  37.     END
  38. ORDER BY
  39.     CASE
  40.         WHEN @type = '1' THEN CIFFName DESC
  41.         WHEN @type = '2' OR '3' THEN CIFLName DESC
  42.         WHEN @type = '4' THEN CIFPan ASC
  43.     END
  44. END
  45.  
  46. DROP TABLE ##Temp
  47.  
Clearly I have several syntax errors, is it even possible to do what I want?
Possible? yes. Not that way.

Build your query dynamically like:

Expand|Select|Wrap|Line Numbers
  1. declare @strquery varchar(500)
  2.  
  3. set @strquery = 'select count(*) from .....'
  4.  
  5. exec (@strquuery)
  6.  
  7.  
-- CK
Apr 22 '08 #2

dcharnigo
P: 20
Thanks, that was a real "Duh" after I read your post. I was even going to title it dynamic query building! Thanks again.
Apr 22 '08 #3

Post your reply

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