SQL Server Stored Procedure with Multiple Queries and Parameters

qfe3c7zg 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(131)

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))
as
begin
DELETE FROM @newTable;

SET IDENTITY_INSERT @newTable ON;

INSERT INTO @newTable(@tableField) SELECT @tableField FROM @table

SET 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.

sql-server

来源：https://stackoverflow.com/questions/75599157/stored-procedure-with-multiple-queries-and-parameters

1条答案

按热度按时间

sqserrrh1#

Please change the body of the store procedure as follows:

alter procedure callProcedure(@newTable varchar(100),
@tableField varchar(8000),
@table varchar(100))
as
BEGIN
DECLARE @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

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Stored Procedure with Multiple Queries and Parameters

1条答案

相关问题

热门标签

最新问答