'Dynamic' Stored Procedure

Question

I am very new to stored procedures and need some help.

I am trying to create a 'dynamic' stored procedure. When a parameter is NOT NULL then a certain part of the SQL should be added. This is what I have until now.

    SELECT        
    TCId, 
    ENVId, 
    UId, 
    MTId, 
    TestSetName, 
    TestCaseName, 
    InterchangeSeqNo, 
    InstructionSeqNo, 
    TransactionSeqNo, 
    TestCaseDescription
FROM XML_TEST_SET_OVERVIEW
WHERE (ENVId = @MyENVId)
SELECT CASE @MyUId 
    WHEN IS NOT NULL THEN (AND UId = @MyUId)
END
SELECT CASE @MyMTId
    WHEN IS NOT NULL THEN (AND MTId = @MyMTId)
END
SELECT CASE @MyTestSetName
    WHEN IS NOT NULL THEN (AND TestSetName = @MyTestSetName) 
END
SELECT CASE @MyTestCaseName
    WHEN  IS NOT NULL THEN  (AND TestCaseName = @MyTestCaseName) 
END
SELECT CASE @MyInterchangeSeqNo
    WHEN  IS NOT NULL THEN  (AND InterchangeSeqNo = @MyInterchangeSeqNo) 
END
SELECT CASE @MyInstructionSeqNo
    WHEN  IS NOT NULL THEN (AND InstructionSeqNo = @MyInstructionSeqNo) 
END
SELECT CASE @MyTransactionSeqNo
    WHEN  IS NOT NULL THEN (AND TransactionSeqNo = @MyTransactionSeqNo)
END  
ORDER BY ENVId, UId, MTId, TestSetName, TestCaseName, InterchangeSeqNo, InstructionSeqNo, TransactionSeqNo

Any help is appreciated

read this: http://stackoverflow.com/a/3415629/65223 – KM. Dec 20 '13 at 18:18 — KM., Dec 20 '13 at 18:18

score 1 · Accepted Answer · answered Dec 20 '13 at 18:14

I have to guess at data types here, and I'll let you fill in the extra fluff.

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... 
  FROM dbo.XML_TEST_SET_OVERVIEW -- always use schema prefix
  WHERE ENVId = @MyENVId'

    + CASE WHEN @MyUId IS NOT NULL THEN 
           N' AND UId = @MyUId' ELSE '' END
    + CASE WHEN @MyMTId IS NOT NULL THEN 
           N' AND MTId = @MyMTId' ELSE '' END
    + CASE WHEN @MyTestSetName IS NOT NULL THEN 
           N' AND TestSetName = @MyTestSetName' ELSE '' END 
      ...
    + CASE WHEN @MyTransactionSeqNo IS NOT NULL THEN
           N' AND TransactionSeqNo = @MyTransactionSeqNo' ELSE '' END

    + N' ORDER BY ENVId, UId, ...;';

EXEC sp_executesql @sql, 
    N'@MyENVId INT, @MyUId INT, @MyMTId INT, 
      @MyTestSetName NVARCHAR(32), ... , @MyTransactionSeqNo INT',
    @MyENVId, @MyUId, @MyMTId, @MyTestSetName, ... , @MyTransactioNSeqNo;

Thanks Aaron. You helped me tremendously. – Rico Strydom Dec 21 '13 at 08:46 — Rico Strydom, Dec 21 '13 at 08:46

'Dynamic' Stored Procedure

1 Answers1

Linked