我试图基于一个带有预定义参数的查询创建一个存储过程。
当我重构为创建存储过程并执行存储过程时,它会显示参数未提供。 有人可以告诉我为什么吗?
我知道我错过了一些关键点,但在处理代码后,我需要专家的帮助。
这是我的代码(缩短版):
Alter Procedure [Test]
@StartDate AS varchar(6),
@EndDate AS varchar(6)
AS
Set @StartDate = '201620' --Define start YearWeek
Set @EndDate = (SELECT CAST(DATEPART(YEAR,getdate()) AS varchar(4)) + CAST(DATEPART(WEEK,getdate())-1 AS varchar(2)))
SELECT
*
FROM
(SELECT DISTINCT
[YEAR], [WeekOfYear]
FROM
[dbo].[DimDate]
WHERE
[Year] + [WeekOfYear] BETWEEN @StartDate AND @EndDate) dimd
LEFT JOIN
[Schema].[Table1] qad ON (qad.[Year] + qad.[Week of the Year]) = (dimd.[Year] + dimd.WeekOfYear)
运行该程序时出现以下提示:
消息201,级别16,状态4,过程test,第0行
Procedure or function 'test' expects parameter '@StartDate', which was not supplied.
= null
来为它们指定默认值,然后就可以在不传递参数的情况下执行它们了。现有的答案已经指出了一些其他的修复方法。 - shawnt00