我有以下存储过程来基于 2 个参数从表中获取数据:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dmitry Kreslavskiy
-- Create date: 2015-01-12
-- Description: spGetCreditBenchmarkCurves
-- =============================================
/* spGetCreditBenchmarkCurves
* Get the list of credit benchmark spread records, if necessary fixing
* currency and latest ValueDate <= RunDate.
* param[in] RunDate Run date of the search (uses the last date up to
* this time), NULL = all
* param[in] Ccy Use only this currency
* return Table (Ccy, ValueDate, TenorSize, TenorUnit, Value)
*/
ALTER PROCEDURE [dbo].[spGetCreditBenchmarkCurves]
(
@RunDate date = NULL,
@Ccy varchar(3) = NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @RunDate IS NULL
IF @Ccy IS NULL
SELECT *
FROM [dbo].CreditBenchmarkCurves
ELSE -- @Ccy is supplied
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE Ccy = @Ccy
ELSE -- @RunDate is supplied
/* It could be that the table does not have any valid data on @RunDate,
* so find the latest date before @RunDate with valid data, and store
* this in @ExactDate, to use it in a query directly.
*
* (Same thing could be done using an INNER JOIN instead of 2 selects,
* but it is much clearer code to do it step by step. Also, I have a
* feeling this implementation is faster as well.)
*/
DECLARE @ExactDate date
SET @ExactDate = (SELECT MAX(ValueDate)
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate <= @RunDate)
IF @Ccy IS NULL
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate = @ExactDate
ELSE -- @Ccy is supplied
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate = @ExactDate AND
Ccy = @Ccy
END
GO
此代码偶尔会返回一个值表,如预期的那样:
EXEC dbo.spGetCreditBenchmarkCurves @RunDate = '2015-02-27', @Ccy = 'AUD'
但是传递默认运行日期会得到一个完整表和一个空表:
EXEC dbo.spGetCreditBenchmarkCurves @RunDate = NULL, @Ccy = 'AUD'
EXEC dbo.spGetCreditBenchmarkCurves
错误是什么,如何使用这样的代码执行 2 个SELECT
语句?谢谢
此代码
ALTER PROCEDURE [dbo].[spGetCreditBenchmarkCurves]
(
@RunDate date = NULL,
@Ccy varchar(3) = NULL
)
AS
BEGIN
IF @RunDate IS NULL
IF @Ccy IS NULL
SELECT *
FROM [dbo].CreditBenchmarkCurves
ELSE -- @Ccy is supplied
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE Ccy = @Ccy
ELSE
DECLARE @ExactDate date
SET @ExactDate = (SELECT MAX(ValueDate)
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate <= @RunDate)
IF @Ccy IS NULL
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate = @ExactDate
ELSE -- @Ccy is supplied
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate = @ExactDate AND
Ccy = @Ccy
END
等于:
ALTER PROCEDURE [dbo].[spGetCreditBenchmarkCurves]
(
@RunDate date = NULL,
@Ccy varchar(3) = NULL
)
AS
BEGIN
IF @RunDate IS NULL
BEGIN
IF @Ccy IS NULL
BEGIN
SELECT * FROM [dbo].CreditBenchmarkCurves
END
ELSE BEGIN
SELECT * FROM [dbo].CreditBenchmarkCurves WHERE Ccy = @Ccy
END
END
ELSE BEGIN
DECLARE @ExactDate date
END
SET @ExactDate = (SELECT MAX(ValueDate)
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate <= @RunDate)
IF @Ccy IS NULL
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate = @ExactDate
ELSE -- @Ccy is supplied
SELECT *
FROM [dbo].CreditBenchmarkCurves
WHERE ValueDate = @ExactDate AND
Ccy = @Ccy
END
如果没有BEGIN END
只有DECLARE @ExactDate date
被视为 ELSE 块。所以实际上如果@RunDate IS NULL
选择将发生 2 次。
用开始/结束包装你的 if else 条件
IF @RunDate IS NULL
BEGIN
/* your code for if */
END
ELSE
BEGIN
/* your code for else */
END
注意:
- BEGIN 和 END 定义了一系列 Transact-SQL 语句,这些语句一起执行。
- 如果。。。ELSE 构造可以批量使用,在存储过程中,和即席查询
参考 1:MSDN(如果不是)
参考 2:MSDN(如果其他)具有开始结束