Quote:
Originally Posted by 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?
Possible? yes. Not that way.
Build your query dynamically like:
- declare @strquery varchar(500)
-
-
set @strquery = 'select count(*) from .....'
-
-
exec (@strquuery)
-
-
-- CK
