我的Select
语句单独工作很好。但是我可以用一个stored procedure
来代替多个程序吗?
ALTER PROCEDURE [dbo].[zPrintAll]
@PrintVariable varchar(10),
@SuppliedKey varchar(15)
AS
BEGIN
IF(@PrintVariable='ONE')
BEGIN
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.key=Table2.key
WHERE Table2.key='@SuppliedKey'
END
IF(@PrintVariable='TWO')
BEGIN
SELECT *
FROM Table3 INNER JOIN Table4 ON Table3.key=Table4.key
WHERE Table3.key='@SuppliedKey'
END
END
编辑:这是一个详细的打印功能。当我提供PersonID时,我会进入每个部门,并在该部门打印他们的账单。我将打印一张至少有20个部门的主账单。如果My Print变量显示"Clothings",我将从"Clothing department"中打印PID所需的字段。如果My Print变量显示"Shoes",我将打印PID的"Shoe department"中所需的字段,依此类推。我已经有了大量的过程,不想再添加一堆Select
语句。
我为一个基于标志"更新"或"删除"的过程做了这件事,但它在这里似乎不起作用。
ALTER PROCEDURE [dbo].[zPrintAll]
@PrintVariable varchar(10),
@SuppliedKey varchar(15)
AS
BEGIN
BEGIN
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.key=Table2.key
UNION ALL
SELECT *
FROM Table3 INNER JOIN Table4 ON Table3.key=Table4.key
WHERE @SuppliedKey = CASE WHEN @PrintVariable='ONE' THEN Table2.key
WHEN @PrintVariable='TWO' THEN Table3.key ELSE '' END
END
END
查询没有错,但当我用变量替换静态键时,语法错误。
ALTER PROCEDURE [dbo].[zPrintAll]
@PrintVariable varchar(10),
@SuppliedKey varchar(15)
AS
BEGIN
IF(@PrintVariable='ONE')
BEGIN
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.key=Table2.key
WHERE Table2.key=@SuppliedKey
END
IF(@PrintVariable='TWO')
BEGIN
SELECT *
FROM Table3 INNER JOIN Table4 ON Table3.key=Table4.key
WHERE Table3.key=@SuppliedKey
END
END
因此,它是@SuppliedKey
而不是'@SuppliedKey'