469,086 Members | 1,273 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Cases in Select, Where, and OrderBy Dynamic Clause

dcharnigo
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
2 1443
ck9663
2,878 Expert 2GB
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
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.

Similar topics

5 posts views Thread by 73blazer | last post: by
1 post views Thread by Greg Moll | last post: by
6 posts views Thread by alex.kemsley | last post: by
9 posts views Thread by Thom Little | last post: by
2 posts views Thread by shapper | last post: by
2 posts views Thread by paulmitchell507 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.