CREATE PROCEDURE [dbo].[sptest]
@prm_SqlFront nvarchar(200), --Sql前部[如SELECT PID, PNAME, PSex], 外部的调用,每次不一定一样, 即SELECT List可变,由外部决定
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长AS
Declare @sql nvarchar(400)
Declare @pid char(10)
Declare @pname char(10)
Declare @page int
Declare @psex char(10)Set NoCount onSet @sql = @prm_SqlFront + ' Into #t FROM PerInfo'
exec sp_executesql @SQL
Delete From #tDeclare cur Cursor For
select pid, pname, page, psex From perinfo order by page
Open cur
fetch next from cur into @pid, @pname, @page, @psexwhile @@fetch_status = 0
begin
Set @Sql = ''
while @page > @prm_StartAge and @page <= @prm_EndAge --@prm_StepLong
begin
Set @prm_StartAge = @prm_StartAge + @prm_StepLong
end if @page = @prm_StartAge
begin
Set @sql = 'Insert into #T ' + @prm_SqlFront + ' FROM PerInfo WHERE PAge=' + Str(@prm_StartAge)
if @prm_StartAge < @prm_EndAge
Set @prm_StartAge = @prm_StartAge + @prm_StepLong exec sp_executesql @SQL, N'@prm_SqlFront nVarChar(200) out', @prm_SqlFront out
end fetch next from cur into @pid, @pname, @page, @psex
Endclose cur
Deallocate cur
Select * from #t order by pidGO
--调用
sptest 'SELECT PID, PNAME', 10, 30, 5PID PName
002 B
004 D
005 E
@prm_SqlFront nvarchar(200), --Sql前部[如SELECT PID, PNAME, PSex], 外部的调用,每次不一定一样, 即SELECT List可变,由外部决定
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长AS
Declare @sql nvarchar(400)
Declare @pid char(10)
Declare @pname char(10)
Declare @page int
Declare @psex char(10)Set NoCount onSet @sql = @prm_SqlFront + ' Into #t FROM PerInfo'
exec sp_executesql @SQL
Delete From #tDeclare cur Cursor For
select pid, pname, page, psex From perinfo order by page
Open cur
fetch next from cur into @pid, @pname, @page, @psexwhile @@fetch_status = 0
begin
Set @Sql = ''
while @page > @prm_StartAge and @page <= @prm_EndAge --@prm_StepLong
begin
Set @prm_StartAge = @prm_StartAge + @prm_StepLong
end if @page = @prm_StartAge
begin
Set @sql = 'Insert into #T ' + @prm_SqlFront + ' FROM PerInfo WHERE PAge=' + Str(@prm_StartAge)
if @prm_StartAge < @prm_EndAge
Set @prm_StartAge = @prm_StartAge + @prm_StepLong exec sp_executesql @SQL, N'@prm_SqlFront nVarChar(200) out', @prm_SqlFront out
end fetch next from cur into @pid, @pname, @page, @psex
Endclose cur
Deallocate cur
Select * from #t order by pidGO
--调用
sptest 'SELECT PID, PNAME', 10, 30, 5PID PName
002 B
004 D
005 E
create table PerInfo(PID char(3),PName char(1),PAge int,PSex bit)
--插入数据
insert PerInfo
select '001', 'A', 5, 0
union select '002', 'B', 10, 0
union select'003', 'C', 3, 1
union select'004', 'D', 15, 1
union select'005', 'E', 30, 0
union select'006', 'F', 35, 0
union select'007', 'G', 18, 0
--创建存储过程
create proc dbo.p_PerInfo
@prm_SqlFront varchar(200)
,@prm_StartAge smallint
,@prm_EndAge smallint
,@prm_StepLong smallint
as
declare @str_sql varchar(8000)create table #t(PID char(3),PNAME char(1))while(@prm_StartAge<=@prm_EndAge)
begin
set @str_sql='insert #t select '+@prm_SqlFront+' from PerInfo where PAge='+cast(@prm_StartAge as varchar(4))
exec(@str_sql)
set @prm_StartAge=@prm_StartAge+@prm_StepLong
end
select * from #t
go
--测试
exec dbo.p_PerInfo 'PID, PNAME',10,30,5
--结果
/*
PID PNAME
---- -----
002 B
004 D
005 E(所影响的行数为 3 行)
*/
exec sp_executesql @SQL
Delete From #t@prm_SqlFront 有可能是SELECT a.xxx, b.yyy 'FROM PerInfo a, DetailInfo b
可是
create table #t(PID char(3),PNAME char(1))
你把#t结构固定了啊!!!!!
如果@prm_SqlFront = 'SELECT PSex', 既只要PSex, create table #t还对吗?
create table PerInfo(PID char(3),PName char(1),PAge int,PSex bit)
insert PerInfo select '001','A', 5, 0
union all select '002','B',10, 0
union all select '003','C', 3, 1
union all select '004','D',15, 1
union all select '005','E',30, 0
union all select '006','F',35, 0
union all select '006','G',18, 0
go--存储过程
create proc p_qry
@prm_SqlFront nvarchar(4000), --Sql前部[如SELECT PID, PNAME, PSex], 外部的调用,每次不一定一样, 即SELECT List可变,由外部决定
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长
as
set nocount on
declare @s nvarchar(4000)
select @s=@prm_SqlFront+N' into #t FROM PerInfo WHERE PAge=@prm_StartAge
set @prm_StartAge=@prm_StartAge+@prm_StepLong
while @prm_StartAge<=@prm_EndAge
begin
insert #t '+@prm_SqlFront+N' FROM PerInfo WHERE PAge=@prm_StartAge
set @prm_StartAge=@prm_StartAge+@prm_StepLong
end
select * from #t'
exec sp_executesql @s,
N'@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int',
@prm_StartAge,@prm_EndAge,@prm_StepLong
go--调用
exec p_qry
@prm_SqlFront = 'SELECT PID, PNAME',
@prm_StartAge = 10,
@prm_EndAge = 30,
@prm_StepLong = 5
go--删除测试
drop table PerInfo
drop proc p_qry /*--结果
PID PNAME
---- -----
004 D
005 E
002 B
--*/
create table PerInfo(PID char(3),PName char(1),PAge int,PSex bit)
insert PerInfo select '001','A', 5, 0
union all select '002','B',10, 0
union all select '003','C', 3, 1
union all select '004','D',15, 1
union all select '005','E',30, 0
union all select '006','F',35, 0
union all select '006','G',18, 0
go--存储过程
create proc p_qry
@prm_SqlFront nvarchar(4000), --Sql前部[如SELECT PID, PNAME, PSex], 外部的调用,每次不一定一样, 即SELECT List可变,由外部决定
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长
as
set nocount on
declare @s nvarchar(4000)
select @s=@prm_SqlFront+N' into #t FROM PerInfo WHERE PAge=@prm_StartAge and 1=0
while @prm_StartAge<=@prm_EndAge
begin
insert #t '+@prm_SqlFront+N' FROM PerInfo WHERE PAge=@prm_StartAge
set @prm_StartAge=@prm_StartAge+@prm_StepLong
end
select * from #t'
exec sp_executesql @s,
N'@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int',
@prm_StartAge,@prm_EndAge,@prm_StepLong
go--调用
exec p_qry
@prm_SqlFront = 'SELECT PID, PNAME',
@prm_StartAge = 10,
@prm_EndAge = 30,
@prm_StepLong = 5
go--删除测试
drop table PerInfo
drop proc p_qry /*--结果PID PNAME
---- -----
002 B
004 D
005 E
--*/
create table PerInfo(PID char(3),PName char(1),PAge int,PSex bit)
insert PerInfo select '001','A', 5, 0
union all select '002','B',10, 0
union all select '003','C', 3, 1
union all select '004','D',15, 1
union all select '005','E',30, 0
union all select '006','F',35, 0
union all select '006','G',18, 0
go--存储过程
create proc p_qry
@prm_SqlFront nvarchar(4000), --Sql前部[如SELECT PID, PNAME, PSex], 外部的调用,每次不一定一样, 即SELECT List可变,由外部决定
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长
as
set nocount on
declare @s nvarchar(4000)
select @s=@prm_SqlFront+N' FROM PerInfo
WHERE PAge between @prm_StartAge and @prm_EndAge
and (PAge-@prm_StartAge)%@prm_StepLong=0'
exec sp_executesql @s,
N'@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int',
@prm_StartAge,@prm_EndAge,@prm_StepLong
go--调用
exec p_qry
@prm_SqlFront = 'SELECT *',
@prm_StartAge = 10,
@prm_EndAge = 30,
@prm_StepLong = 5
go--删除测试
drop table PerInfo
drop proc p_qry /*--结果PID PName PAge PSex
---- ----- ----------- ----
002 B 10 0
004 D 15 1
005 E 30 0
--*/
--***** 上面这个是不用循环的 ***--
你这个人真的不错噶! 我到现在只写过一个存储过程, 主要做的其他层的设计.
--@prm_SqlFront nvarchar(4000), --至于這個還沒有調試出來﹐不過可以用動態語句實現其功能
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长
as
declare @count int
declare @id int
select @count=count(*) from PerInfo
set @id=1select identity(int,1,1) as ID, * into #tp from PerInfo
select top 0 * into #temp from #tp--set @prm_SqlFront
while @count>0
begin
insert into #temp(PID,PName)
select PID,PName
from #tp
where( (PAge -@prm_StartAge)%@prm_StepLong=0 )and (PAge between @prm_StartAge and @prm_EndAge) and ID=@id
set @id=@id+1
set @count=@count-1
end
select PID,PName from #tempdrop table #tb
drop table #temp
GO
mschen(Visual【陈】) Lwg0901(伤心人) Hopewell_Go(好的在后頭﹗希望更好﹗﹗)还有最具奉献精神的zjcxc(邹建), Your Skill is power!!!!
create table PerInfo(PID char(3),PName char(1),PAge int,PSex bit)
insert PerInfo select '001','A', 5, 0
union all select '002','B',10, 0
union all select '003','C', 3, 1
union all select '004','D',15, 1
union all select '005','E',30, 0
union all select '006','F',35, 0
union all select '006','G',18, 0
go--存储过程
create proc p_qry
@prm_SqlFront nvarchar(4000), --Sql前部[如SELECT PID, PNAME, PSex], 外部的调用,每次不一定一样, 即SELECT List可变,由外部决定
@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int --年龄步长
as
set nocount on
declare @s nvarchar(4000)
select @s=@prm_SqlFront+N' FROM PerInfo
WHERE PAge between @prm_StartAge and @prm_EndAge
and (PAge-@prm_StartAge)%@prm_StepLong=0'
exec sp_executesql @s,
N'@prm_StartAge int, --起始年龄
@prm_EndAge int, --截至年龄
@prm_StepLong int',
@prm_StartAge,@prm_EndAge,@prm_StepLong
go--调用
exec p_qry
@prm_SqlFront = 'SELECT *',
@prm_StartAge = 10,
@prm_EndAge = 30,
@prm_StepLong = 5
go--删除测试
drop table PerInfo
drop proc p_qry /*--结果PID PName PAge PSex
---- ----- ----------- ----
002 B 10 0
004 D 15 1
005 E 30 0
--*/
一.想问问 exec sp_executesql 'select * into #t from tb'中的 #t的生命周期只是在此条语句中吗?
我原来想是这样的:
1. exec sp_executesql 'select * into #t from tb'
2. exec sp_executesql 'insert #t select from tb'
3. exec sp_executesql 'select from #t'可是执行到2, 3的时候, 报错, 找不到#t对象.二. 我理解的对吗:
主要的逻辑在@s里, 全在@s里执行的话,#t就一直都在了, 用exec sp_executesql @s 后面的几个参数是传给@s里的对于变量的吗。?
还可以多给你点分吗?可以的话,如果给,当然还有其他的几个朋友。;)