英文:
Stored Procedure with Multiple Queries and Parameters
问题
我正在使用Microsoft SQL Server和Java。
我打算创建一个全局存储过程函数，它将从一个表中复制记录到另一个表中。
我的当前存储过程：
create procedure callProcedure(@newTable varchar(100),@tableField varchar(8000),@table varchar(100))asbeginDELETE FROM @newTable;SET IDENTITY_INSERT @newTable ON;INSERT INTO @newTable(@tableField) SELECT @tableField FROM @table;SET IDENTITY_INSERT @newTable OFF;end
当我执行这个存储过程时，我收到以下错误消息：
SQL错误 [102] [S0001]：'@newTable' 附近有语法错误。
替换参数后的示例最终查询：
EXEC callProcedure(@newTable = 'EMPLOYEE_1', @tableField = 'ID, NAME, STATUS', @table = 'EMPLOYEE')
请帮助解决这个问题。
谢谢。
英文:
I'm using Microsoft SQL Server and Java for this.
I'm intending to create a global procedure function, in which it will copy the records from 1 table to another table.
My current procedure:
create procedure callProcedure(@newTable varchar(100),@tableField varchar(8000),@table varchar(100))asbeginDELETE FROM @newTable;SET IDENTITY_INSERT @newTable ON;INSERT INTO @newTable(@tableField) SELECT @tableField FROM @tableSET IDENTITY_INSERT @newTable OFF;end
When I execute the procedure.
This is the error that I received.
> SQL Error [102] [S0001]: Incorrect syntax near '@newTable'.
Sample final queries after being replaced with parameters
EXEC callProcedure(@newTable = 'EMPLOYEE_1', @tableField = 'ID, NAME, STATUS', @table = 'EMPLOYEE')
Please help with this.
Thank you.
EXEC callProcedure(@newTable = 'EMPLOYEE_1', @tableField = 'ID, NAME, STATUS', @table = 'EMPLOYEE')
will return the result.
答案1
得分: 2
以下是代码的翻译部分：
请将存储过程的主体更改如下：ALTER PROCEDURE callProcedure(@newTable varchar(100),@tableField varchar(8000),@table varchar(100))ASBEGINDECLARE @query NVARCHAR(MAX)SET @query = 'DELETE FROM ' + @newTable + ';';SET @query += 'SET IDENTITY_INSERT ' + @newTable + ' ON;';SET @query += 'INSERT INTO ' + @newTable + '(' + @tableField + ') SELECT ' + @tableField + ' FROM ' + @table + ';';SET @query += 'SET IDENTITY_INSERT ' + @newTable + ' OFF;';EXEC (@query)END
英文:
Please change the body of the stored procedure as follows:
ALTER PROCEDURE callProcedure(@newTable varchar(100),@tableField varchar(8000),@table varchar(100))ASBEGINDECLARE @query NVARCHAR(MAX)SET @query = 'DELETE FROM ' + @newTable + ';';SET @query += 'SET IDENTITY_INSERT ' + @newTable + ' ON;';SET @query += 'INSERT INTO ' + @newTable + '(' + @tableField + ') SELECT ' + @tableField + ' FROM ' + @table + ';';SET @query += 'SET IDENTITY_INSERT ' + @newTable + ' OFF;';EXEC (@query)END
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论