create proc [dbo].[P_indefom2]
@from1 varchar(20),
@from2 varchar(20),
@num Varchar(20),
@zd varchar(20),
@WHERE VARCHAR(500)as
BEGIN
declare @SQL Nvarchar(4000)
declare @SQL2 Nvarchar(4000)if object_id('tempdb..#tmp_bk_master')is not null
begin
drop table #tmp_bk_master
end
IF ISNULL(@WHERE ,'')<>''
BEGIN
set @SQL2='INSERT into '+@from2+'('+@zd+') SELECT
top '+@num+' '+@zd+' FROM '+@from1+' '+ @WHEREexec(@SQL2)set @SQL='select * into #tmp_bk_master from '+@from1+' where 1=0'
set @SQL=@SQL+' SET IDENTITY_INSERT #tmp_bk_master ON
insert into #tmp_bk_master ([id],'+@zd+')
select top '+@num+' [id],'+@zd+' FROM '+@from1+'
SET IDENTITY_INSERT #tmp_bk_master OFF' set @SQL=@SQL+' Delete from '+@from1+' where id in (select id from #tmp_bk_master ) '+@WHERE
exec(@SQL)
END
ENDexec P_indefom2 'asd1','asd2','20','UserName,sex','where UserName=''张三'''
运行这个之后会暴
(1 行受影响)
消息 156,级别 15,状态 1,第 4 行
关键字 'where' 附近有语法错误。...不知道哪里语法错了
@from1 varchar(20),
@from2 varchar(20),
@num Varchar(20),
@zd varchar(20),
@WHERE VARCHAR(500)as
BEGIN
declare @SQL Nvarchar(4000)
declare @SQL2 Nvarchar(4000)if object_id('tempdb..#tmp_bk_master')is not null
begin
drop table #tmp_bk_master
end
IF ISNULL(@WHERE ,'')<>''
BEGIN
set @SQL2='INSERT into '+@from2+'('+@zd+') SELECT
top '+@num+' '+@zd+' FROM '+@from1+' '+ @WHEREexec(@SQL2)set @SQL='select * into #tmp_bk_master from '+@from1+' where 1=0'
set @SQL=@SQL+' SET IDENTITY_INSERT #tmp_bk_master ON
insert into #tmp_bk_master ([id],'+@zd+')
select top '+@num+' [id],'+@zd+' FROM '+@from1+'
SET IDENTITY_INSERT #tmp_bk_master OFF' set @SQL=@SQL+' Delete from '+@from1+' where id in (select id from #tmp_bk_master ) '+@WHERE
exec(@SQL)
END
ENDexec P_indefom2 'asd1','asd2','20','UserName,sex','where UserName=''张三'''
运行这个之后会暴
(1 行受影响)
消息 156,级别 15,状态 1,第 4 行
关键字 'where' 附近有语法错误。...不知道哪里语法错了
--try
alter proc [dbo].[P_indefom2]
@from1 varchar(20),
@from2 varchar(20),
@num Varchar(20),
@zd varchar(20),
@WHERE VARCHAR(500)as
BEGIN
declare @SQL Nvarchar(4000)
declare @SQL2 Nvarchar(4000)if object_id('tempdb..#tmp_bk_master')is not null
begin
drop table #tmp_bk_master
end
IF ISNULL(@WHERE ,'')<>''
BEGIN
set @SQL2='INSERT into '+@from2+'('+@zd+') SELECT
top '+@num+' '+@zd+' FROM '+@from1+' where '+ @WHERE
exec(@SQL2)set @SQL='select * into #tmp_bk_master from '+@from1+' where 1=0'
set @SQL=@SQL+' SET IDENTITY_INSERT #tmp_bk_master ON
insert into #tmp_bk_master ([id],'+@zd+')
select top '+@num+' [id],'+@zd+' FROM '+@from1+'
SET IDENTITY_INSERT #tmp_bk_master OFF' set @SQL=@SQL+' Delete from '+@from1+' where id in (select id from #tmp_bk_master ) and '+@WHERE
print @sql
exec(@SQL)
END
END