CREATE PROCEDURE insert_single_selected_two
@word varchar(10),
@grade varchar(10),
@user_name varchar(40),
@number numeric(10,0)
AS
begin
set @[email protected]_name+'_single_selected'
insert into @user_name
select top @number * from single_select
where [email protected] and [email protected]
and id not in (select id from @user_name) order by newid()
end
RETURN
直接报错：@number附近有错
@number就两个地方，偶想不到哪里出的错。
顺便再问问，[email protected] and [email protected]？我担心的是会不会因为没有单引号而导致出错
------解决方案--------------------
- SQL code
CREATE PROCEDURE insert_single_selected_two@word varchar(10),@grade varchar(10),@user_name varchar(40),@number numeric(10,0)ASbegindeclare @str varchar(2000)set @str=''set @[email protected]_name+'_single_selected'set @str='insert into [email protected]_name+'select top '+ltrim(@number)+ ' * from single_select where [email protected] and [email protected]+ and id not in (select id from [email protected]_name+') order by newid()end'exec(@str)RETURN存储过程中要想传入表名的时候不能直接使用，你得先把语句拼接起来，然后动态执行
------解决方案--------------------
当表名为动态时,需要使用动态SQL.
- SQL code
CREATE PROCEDURE insert_single_selected_two@word varchar(10),@grade varchar(10),@user_name varchar(40),@number numeric(10,0)ASbeginset @[email protected]_name+'_single_selected'declare @sql as varchar(1000)set @sql = 'insert into ' + @user_name + 'select top ' + ltrim(@number) + ' * from single_select where 级别=''' + @grade + ''' and 关键字= ''' + @word + '''and id not in (select id from ' + @user_name + ') order by newid()'exec(@sql)endRETURN