i有一个存储过程,该过程正在根据参数标准提取数据并将其存储在表中。
当我第一次运行它时,查询将成功执行,但是第二次运行时,它会在MY_NEW_TABLE
已经存在时丢弃错误。
现在问题是我要保留每年和一周的数据。
我必须做什么更改,以便我可以将参数的值传递给表格?
?代码
CREATE PROC usp_myproc
@year, @week
AS
BEGIN
SELECT COL1, COL2
INTO MY_NEW_TABLE
FROM TABLE
WHERE YEAR = @YEAR AND WEEK = @WEEK
END
EXEC usp_myproc @year = 2018, @week = 01
说实话,您可能最好一次创建表格,然后更改存储过程,以便它只能 INSERT INTO
新记录?
但是,要回答您的问题,如果您做了这样的事情,则应该有效:
CREATE PROCEDURE usp_myproc (
@YEAR INT,
@WEEK INT)
AS
BEGIN
IF OBJECT_ID('MY_NEW_TABLE') IS NOT NULL
BEGIN
INSERT INTO MY_NEW_TABLE
SELECT COL1, COL2
FROM [TABLE]
WHERE [YEAR] = @YEAR AND [WEEK] = @WEEK;
END
ELSE
BEGIN
SELECT COL1, COL2
INTO MY_NEW_TABLE
FROM [TABLE]
WHERE [YEAR] = @YEAR AND [WEEK] = @WEEK;
END;
END;
EXEC usp_myproc @YEAR = 2018, @WEEK = 01;