在执行存储过程命令 SQL Server 中使用表列

Question

我有一个命令：

exec [Database].[dbo].DepartmentChangeLevel , Department, Date, Level;

我目前手动并显式指定值，如下所示：

exec [Database].[dbo].DepartmentChangeLevel , 'Catering', '20180101 08:01:00', 3;

但是，我想运行相同的命令并使用表中的值 - 如下所示：

exec [Database].[dbo].DepartmentChangeLevel 
select 
[Department], [Date], [Level]
from
[Database].[dbo].[DepartmentChange] 

我收到的错误消息是

需要未提供的参数@department

请帮忙

Answer 1

您可以改用游标：

在表上创建一个游标，并将所有三列传递到三个变量中 并在光标中执行进程。

例：

declare db_cursor cursor 
for select 
[Department], [Date], [Level]
from
[Database].[dbo].[DepartmentChange];
declare @department int --your column data type
,@date datetime --your column data type
,@level varchar(10) --your column data type
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @department, @date, @level;
WHILE @@FETCH_STATUS = 0  
BEGIN  
exec [Database].[dbo].DepartmentChangeLevel @department, @date, @level;
DELETE FROM [Database].[dbo].DepartmentChangeLevel ---or you can store values from DepartmentChangeLevel table into temporary table  then delete those values from temporary table
WHERE [Department]=@department
and [Date]=@date
and [Level]=@level
FETCH NEXT FROM db_cursor INTO @department,@date, @level
END
CLOSE db_cursor;
DEALLOCATE db_cursor;