“动态"存储过程 [英] 'Dynamic' Stored Procedure

问题描述

我对存储过程很陌生，需要一些帮助.

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

我正在尝试创建一个动态"存储过程.当参数为 NOT NULL 时，应添加 SQL 的某个部分.这就是我到目前为止所拥有的.

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

感谢任何帮助

推荐答案

我必须在这里猜测数据类型，我会让你填写多余的信息.

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;

这篇关于“动态"存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！