代码为:CREATE procedure test_proc1( @IndexId char(100)) as declare @Temp table (id int ,name char(255),son int null) insert @Temp values(@IndexId,'initname',1) update @Temp set name=(select TOP 1 name from target where id=@IndexId) select * from @Temp GOCREATE procedure test_proc2( ) as declare @Temp table (id int ,name char(255),son int null) insert @Temp exec test_proc1 GO
create table #table(id int identity,txt varchar(1000)) insert into #table(txt) exec master..xp_cmdshell 'dir c:\*.' select * from #table用存储过程插入数据 在INSERT 语句中可以通过执行存储过程来取得要插入的数据所插入的数据是存储 过程中SELECT 语句所检索的结果集使用存储过程插入数据的语法如下 INSERT [INTO] { table_name WITH ( <table_hint_limited> [...n]) | view_name | rowset_function_limited } { [(column_list)] EXECUTE procedure 其中procedure 既可以是一个已经存在的系统存储过程或用户自定义的存储过程也 可以在INSERT 语句中直接编写存储过程 例11-4 对每个部门求员工工资总额并把结果存入department_info 表中 use pangu insert into department_info(dept_id, d_wage) execute ('select dept_id, sum(e_wage) from employee group by dept_id') select * from department_info 运行结果如下 (7 row(s) affected) dept_id d_chief_name d_location e_num d_wage ------- -------------------- -------------------------------------------------- ------ ------------ 1001 dbo NULL NULL 15000.0000 1002 dbo NULL NULL 19500.0000
CREATE function test_proc1(@IndexId varchar(100)) returns @Temp table(id int ,name varchar(255),son int null) as begin insert @Temp values(@IndexId,'initname',1) update @Temp set name=(select TOP 1 name from target where id=@IndexId) return end GOCREATE procedure test_proc2 as select * from test_proc1('aaaa') GO
declare @sql varchar(8000) --你的sql语句 set @sql = 'select getdate() as sysdate from sysobjects' exec('select * into ##temp from (' + @sql + ') as a')select * from ##temp --结果在这里。把他放入你定义的表变量中就好。 drop table ##temp
insert #表 exec 过程名
as
declare @Temp table (id int ,name char(255),son int null)
insert @Temp values(@IndexId,'initname',1)
update @Temp set name=(select TOP 1 name from target where id=@IndexId)
select * from @Temp
GOCREATE procedure test_proc2( )
as
declare @Temp table (id int ,name char(255),son int null)
insert @Temp exec test_proc1
GO
insert into #table(txt) exec master..xp_cmdshell 'dir c:\*.'
select * from #table用存储过程插入数据
在INSERT 语句中可以通过执行存储过程来取得要插入的数据所插入的数据是存储
过程中SELECT 语句所检索的结果集使用存储过程插入数据的语法如下
INSERT [INTO]
{ table_name WITH ( <table_hint_limited> [...n])
| view_name
| rowset_function_limited }
{ [(column_list)]
EXECUTE procedure
其中procedure 既可以是一个已经存在的系统存储过程或用户自定义的存储过程也
可以在INSERT 语句中直接编写存储过程
例11-4 对每个部门求员工工资总额并把结果存入department_info 表中
use pangu
insert into department_info(dept_id, d_wage)
execute ('select dept_id, sum(e_wage)
from employee
group by dept_id')
select * from department_info
运行结果如下
(7 row(s) affected)
dept_id d_chief_name d_location e_num d_wage
------- -------------------- -------------------------------------------------- ------ ------------
1001 dbo NULL NULL 15000.0000
1002 dbo NULL NULL 19500.0000
returns @Temp table(id int ,name varchar(255),son int null)
as
begin
insert @Temp values(@IndexId,'initname',1)
update @Temp set name=(select TOP 1 name from target where id=@IndexId)
return
end
GOCREATE procedure test_proc2
as
select * from test_proc1('aaaa')
GO
set @sql = 'select getdate() as sysdate from sysobjects'
exec('select * into ##temp from (' + @sql + ') as a')select * from ##temp --结果在这里。把他放入你定义的表变量中就好。
drop table ##temp