Sample stored procedure. SQL Server 2008+
CREATE PROCEDURE [dbo].[usp_audit_select_criticalresults] @exams dbo.ExamIDType READONLY AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT DISTINCT x.ExamID, case WHEN sap2.ParameterValue is not null then CONVERT(bit,sap2.ParameterValue) else CONVERT(bit,0) END FROM @exams x LEFT JOIN SessionActivityParameters sap ON sap.ParameterName='ExamId' AND x.ExamID = sap.ParameterValue LEFT JOIN SessionActivityParameters sap2 ON sap2.ParameterName='confirmed' AND sap.SessionActivityId =sap2.SessionActivityId LEFT JOIN SessionActivity sa ON sa.SessionActivityId = sap.SessionActivityId WHERE (sa.AttributeId='55CD62C2-AED5-4216-A3C6-FABD0183E130' or sa.AttributeId is null) END
Executing stored procedure with test data in T-SQL:
DECLARE @exams dbo.ExamIDType; INSERT INTO @exams (ExamID) SELECT '00993736' UNION SELECT '00993737' UNION SELECT '00993738' UNION SELECT '00993749' UNION SELECT '00993750' UNION SELECT '00993766' UNION SELECT '00993767' --SELECT * FROM @exams exec usp_audit_select_criticalresults @exams
No comments:
Post a Comment