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:

PROCEDURE [dbo].[spGetCIFSearch] (
    @type int,
    @max int,
    @data varchar(25),
    @flag int
)
AS
SET NOCOUNT ON
 
SELECT * INTO ##Temp FROM zCIFRecord AS C
WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
AND CIFUpdateActivity NOT LIKE 'D'
 
BEGIN
IF @flag = 0
    BEGIN
        SELECT COUNT(*)
    END
ELSE
    BEGIN
        SELECT (@max)
            CIFPan, 
            CIFMemNum, 
            CIFLName, 
            CIFFName, 
            CIFInitial 
    END
 
FROM ##Temp
 
WHERE 
    CASE
        WHEN @type = '1' THEN CIFFName LIKE @data
        WHEN @type = '2' THEN CIFLName LIKE @data
        WHEN @type = '3' THEN CIFFName+' '+CIFLName LIKE @data
        WHEN @type = '4' THEN CIFPan LIKE @data
    END
ORDER BY
    CASE
        WHEN @type = '1' THEN CIFFName DESC
        WHEN @type = '2' OR '3' THEN CIFLName DESC
        WHEN @type = '4' THEN CIFPan ASC
    END
END
 
DROP TABLE ##Temp
 

Clearly I have several syntax errors, is it even possible to do what I want?

[ck9663]

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:

PROCEDURE [dbo].[spGetCIFSearch] (
    @type int,
    @max int,
    @data varchar(25),
    @flag int
)
AS
SET NOCOUNT ON
 
SELECT * INTO ##Temp FROM zCIFRecord AS C
WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
AND CIFUpdateActivity NOT LIKE 'D'
 
BEGIN
IF @flag = 0
    BEGIN
        SELECT COUNT(*)
    END
ELSE
    BEGIN
        SELECT (@max)
            CIFPan, 
            CIFMemNum, 
            CIFLName, 
            CIFFName, 
            CIFInitial 
    END
 
FROM ##Temp
 
WHERE 
    CASE
        WHEN @type = '1' THEN CIFFName LIKE @data
        WHEN @type = '2' THEN CIFLName LIKE @data
        WHEN @type = '3' THEN CIFFName+' '+CIFLName LIKE @data
        WHEN @type = '4' THEN CIFPan LIKE @data
    END
ORDER BY
    CASE
        WHEN @type = '1' THEN CIFFName DESC
        WHEN @type = '2' OR '3' THEN CIFLName DESC
        WHEN @type = '4' THEN CIFPan ASC
    END
END
 
DROP TABLE ##Temp
 

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:

declare @strquery varchar(500)
 
set @strquery = 'select count(*) from .....'
 
exec (@strquuery)
 
 

-- CK

[dcharnigo]

Thanks, that was a real "Duh" after I read your post. I was even going to title it dynamic query building! Thanks again.