Search This Blog

Thursday, July 5, 2012

Using stored proc with table parameters


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: