I have a stored procedure that I need to pass a parameter into a case statement with IN in the the where

Question

I have a stored procedure that I need to pass a parameter into a case statement with IN in the the where

Northern Monkey

Ten Centuries

Points: 1017
More actions
June 9, 2011 at 2:45 am

#238866

I have a field in the where that’s an INT I tried the case below using 28,30 or 28 ‘,’30 I just can’t get it to return data if I use just one number ie 30 it returns data
My question is how can I get this working as I need to say in the where depending on the parameter passed ie
AD.TABLENAMEID IN (28,30)
OR
AD.TABLENAMEID IN (11,48,132)
Ect Ect
Thanks in advance for any replys
(
@FROMDATE DATETIME,
@DATATYPE VARCHAR
)
AS
DECLARE @EndTime DATETIME
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
CREATE TABLE #CHANGESINCELAST (
CHANGEDATE DATETIME NULL,
HOURCHANGED INT NULL,
OPERATION VARCHAR (10) NULL,
FULLNAME NVARCHAR (150) NULL,
COLUMNNAME NVARCHAR (200) NULL,
OLDVALUE NVARCHAR (MAX) NULL,
NEWVALUE NVARCHAR (MAX) NULL,
BOOKINGID INT NULL)
INSERT INTO #CHANGESINCELASTDFP
select CAST(AH.TransactionDate AS DATE) AS CHANGEDATE, DATEPART(HOUR,AH.TransactionDate) AS HOUR, AH.Operation,AU.FULLNAME, TC.COLUMNNAME,
AD.OldValue,AD.NewValue, BLI.BOOKINGID
from AUDITHEADER AS AH
LEFT OUTER JOIN APPLICATIONUSER AS AU ON AH.ApplicationUserID = AU.APPLICATIONUSERID
LEFT OUTER JOIN AUDITDETAIL AS AD ON AH.AuditHeaderID = AD.AuditHeaderID AND CAST (AD.TABLENAMEID AS VARCHAR) IN (CASE
WHEN @DATATYPE = 'B' THEN ('30' + ',' + '28')
WHEN @DATATYPE = 'C' THEN ('11' + ',' + '48' + ',' + '132')
WHEN @DATATYPE = 'P' THEN ('114')
WHEN @DATATYPE = 'I' THEN ('82')
END)
AND (AD.TABLECOLUMNID = 774 or AD.OLDVALUE IS NOT NULL)
LEFT OUTER JOIN TABLECOLUMN AS TC ON AD.TableColumnID = TC.TABLECOLUMNID
LEFT OUTER JOIN BOOKINGLINEISSUE AS BLI ON AD.EntityID = BLI.BOOKINGLINEISSUEID AND CAST (AD.TABLENAMEID AS VARCHAR) IN (CASE
WHEN @DATATYPE = 'B' THEN ('30' + ',' + '28')
WHEN @DATATYPE = 'C' THEN ('11' + ',' + '48' + ',' + '132')
WHEN @DATATYPE = 'P' THEN ('114')
WHEN @DATATYPE = 'I' THEN ('82')
END)
WHERE (AD.NewValue != AD.OldValue and CAST (AD.TABLENAMEID AS VARCHAR) IN (CASE
WHEN @DATATYPE = 'B' THEN ('30' + ',' + '28')
WHEN @DATATYPE = 'C' THEN ('11' + ',' + '48' + ',' + '132')
WHEN @DATATYPE = 'P' THEN ('114')
WHEN @DATATYPE = 'I' THEN ('82')
END)
and AH.TransactionDate >= @FROMDATE)
GROUP BY CAST(AH.TransactionDate AS DATE), DATEPART(HOUR,AH.TransactionDate), AH.Operation,AU.FULLNAME, TC.COLUMNNAME, AD.OldValue,AD.NewValue, BLI.BOOKINGID
UNION ALL
select CAST(AH.TransactionDate AS DATE) AS CASTDATE, DATEPART(HOUR,AH.TransactionDate) AS HOUR, AH.Operation,AU.FULLNAME, TC.COLUMNNAME, AD.OldValue,
AD.NewValue,BLIP.BOOKINGID
from AUDITHEADER AS AH
LEFT OUTER JOIN APPLICATIONUSER AS AU ON AH.ApplicationUserID = AU.APPLICATIONUSERID
LEFT OUTER JOIN AUDITDETAIL AS AD ON AH.AuditHeaderID = AD.AuditHeaderID AND CAST (AD.TABLENAMEID AS VARCHAR) IN (CASE
WHEN @DATATYPE = 'B' THEN ('30' + ',' + '28')
WHEN @DATATYPE = 'C' THEN ('11' + ',' + '48' + ',' + '132')
WHEN @DATATYPE = 'P' THEN ('114')
WHEN @DATATYPE = 'I' THEN ('82')
END)
AND (AD.TABLECOLUMNID = 774 or AD.OLDVALUE IS NOT NULL)
LEFT OUTER JOIN TABLECOLUMN AS TC ON AD.TableColumnID = TC.TABLECOLUMNID
LEFT OUTER JOIN BOOKINGLINEISSUEPOSITION AS BLIP ON AD.EntityID = BLIP.BOOKINGLINEISSUEPOSITIONID AND CAST (AD.TABLENAMEID AS VARCHAR) IN (CASE
WHEN @DATATYPE = 'B' THEN ('30' + ',' + '28')
WHEN @DATATYPE = 'C' THEN ('11' + ',' + '48' + ',' + '132')
WHEN @DATATYPE = 'P' THEN ('114')
WHEN @DATATYPE = 'I' THEN ('82')
END)
WHERE (AD.NewValue != AD.OldValue and CAST (AD.TABLENAMEID AS VARCHAR) IN (CASE
WHEN @DATATYPE = 'B' THEN ('30' + ',' + '28')
WHEN @DATATYPE = 'C' THEN ('11' + ',' + '48' + ',' + '132')
WHEN @DATATYPE = 'P' THEN ('114')
WHEN @DATATYPE = 'I' THEN ('82')
END)
and AH.TransactionDate >= @FROMDATE)
GROUP BY CAST(AH.TransactionDate AS DATE), DATEPART(HOUR,AH.TransactionDate), AH.Operation,AU.FULLNAME, TC.COLUMNNAME, AD.OldValue,AD.NewValue, BLIP.BOOKINGID
SELECT * FROM #CHANGESINCELAST
DROP TABLE #CHANGESINCELAST

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Dynamic SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 2

GilaMonster (6/9/2011)
Dynamic SQL.

Not so fast! Two alternatives here:

(1) Something like (simplified):

WHERE (@datatype = 'B' AND tablenameid IN (30,28))

OR (@datatype = 'C' AND tablenameid IN (11,48,132))

OR etc

(2) My preferred solution - create a table (permanent or temp) to resolve your data types to your table name IDs, and join to that in your query:

'B' 30

'B' 28

'C' 11

'C' 48

'C' 132

etc

As an aside - beware! Your WHERE clause compares values in the outer tables of your joined tables. This means you may be comparing NULLs with NULLs and you may get unexpected results.

John

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 3

Northern Monkey

Ten Centuries

Points: 1017

June 9, 2011 at 4:53 am

#1336162

Thanks John and Gail sorted 🙂

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

Simon

Since Gail and I have taken the time to help you, and in case anyone else is reading this in the future with a similar problem, please will you post back and say how you solved it?

Thanks

John

Northern Monkey Ten Centuries Points: 1017 More actions · Answer 5

John Mitchell-245523 (6/9/2011)
Simon
Since Gail and I have taken the time to help you, and in case anyone else is reading this in the future with a similar problem, please will you post back and say how you solved it?
Thanks
John

No Probs John

Here is the code that works I went with your 2nd option

and once again thanks for the help

Simon

(

@FROMDATE DATETIME,

@DATATYPE VARCHAR

)

AS

DECLARE @EndTime DATETIME

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

CREATE TABLE #PARAMETERVALUE(

VALUE INT NULL)

INSERT INTO #PARAMETERVALUE

VALUES (CASE WHEN @DATATYPE = 'B' THEN 30 WHEN @DATATYPE = 'C' THEN 132 WHEN @DATATYPE = 'P' THEN 114 WHEN @DATATYPE = 'I' THEN 82 END);

INSERT INTO #PARAMETERVALUE

VALUES (CASE WHEN @DATATYPE = 'B' THEN 28 WHEN @DATATYPE = 'C' THEN 48 END);

INSERT INTO #PARAMETERVALUE

VALUES (CASE WHEN @DATATYPE = 'C' THEN 11 END)

CREATE TABLE #CHANGESINCELASTDFP (

CHANGEDATE DATETIME NULL,

HOURCHANGED INT NULL,

OPERATION VARCHAR (10) NULL,

FULLNAME NVARCHAR (150) NULL,

COLUMNNAME NVARCHAR (200) NULL,

OLDVALUE NVARCHAR (MAX) NULL,

NEWVALUE NVARCHAR (MAX) NULL,

BOOKINGID INT NULL)

INSERT INTO #CHANGESINCELASTDFP

select CAST(AH.TransactionDate AS DATE) AS CHANGEDATE, DATEPART(HOUR,AH.TransactionDate) AS HOUR, AH.Operation,AU.FULLNAME, TC.COLUMNNAME,

AD.OldValue,AD.NewValue, BLI.BOOKINGID

from AUDITHEADER AS AH

LEFT OUTER JOIN APPLICATIONUSER AS AU ON AH.ApplicationUserID = AU.APPLICATIONUSERID

LEFT OUTER JOIN AUDITDETAIL AS AD ON AH.AuditHeaderID = AD.AuditHeaderID AND AD.TABLENAMEID IN (SELECT * FROM #PARAMETERVALUE)

AND (AD.TABLECOLUMNID = 774 or AD.OLDVALUE IS NOT NULL)

LEFT OUTER JOIN TABLECOLUMN AS TC ON AD.TableColumnID = TC.TABLECOLUMNID

LEFT OUTER JOIN BOOKINGLINEISSUE AS BLI ON AD.EntityID = BLI.BOOKINGLINEISSUEID AND AD.TABLENAMEID IN (SELECT * FROM #PARAMETERVALUE)

WHERE (AD.NewValue != AD.OldValue AND AD.TABLENAMEID IN (SELECT * FROM #PARAMETERVALUE)

and AH.TransactionDate >= @FROMDATE)

GROUP BY CAST(AH.TransactionDate AS DATE), DATEPART(HOUR,AH.TransactionDate), AH.Operation,AU.FULLNAME, TC.COLUMNNAME, AD.OldValue,AD.NewValue, BLI.BOOKINGID

UNION ALL

select CAST(AH.TransactionDate AS DATE) AS CASTDATE, DATEPART(HOUR,AH.TransactionDate) AS HOUR, AH.Operation,AU.FULLNAME, TC.COLUMNNAME, AD.OldValue,

AD.NewValue,BLIP.BOOKINGID

from AUDITHEADER AS AH

LEFT OUTER JOIN APPLICATIONUSER AS AU ON AH.ApplicationUserID = AU.APPLICATIONUSERID

LEFT OUTER JOIN AUDITDETAIL AS AD ON AH.AuditHeaderID = AD.AuditHeaderID AND AD.TABLENAMEID IN (SELECT * FROM #PARAMETERVALUE)

AND (AD.TABLECOLUMNID = 774 or AD.OLDVALUE IS NOT NULL)

LEFT OUTER JOIN TABLECOLUMN AS TC ON AD.TableColumnID = TC.TABLECOLUMNID

LEFT OUTER JOIN BOOKINGLINEISSUEPOSITION AS BLIP ON AD.EntityID = BLIP.BOOKINGLINEISSUEPOSITIONID AND AD.TABLENAMEID IN (SELECT * FROM #PARAMETERVALUE)

WHERE (AD.NewValue != AD.OldValue AND AD.TABLENAMEID IN (SELECT * FROM #PARAMETERVALUE)

and AH.TransactionDate >= @FROMDATE)

GROUP BY CAST(AH.TransactionDate AS DATE), DATEPART(HOUR,AH.TransactionDate), AH.Operation,AU.FULLNAME, TC.COLUMNNAME, AD.OldValue,AD.NewValue, BLIP.BOOKINGID

SELECT * FROM #CHANGESINCELASTDFP

DROP TABLE #CHANGESINCELASTDFP

DROP TABLE #PARAMETERVALUE