原本在2005中一句话解决的,可在2000不支持大费周章..标红的那行就是ceate procedure GetEventIDList
@eventID int
as
declare @IDTable table(EventID int)
insert into @IDTable values (@eventID)declare @proce_name varchar(40),@sTempEvtID int
set @proce_name = 'GetEventID'exec @sTempEvtID = @proce_name @eventID,1 while (@sTempEvtID <>-1)
begin
insert into @IDTable values (@sTempEvtID)
exec @sTempEvtID = @proce_name @sTempEvtID,1
endexec @sTempEvtID = @proce_name @eventID,-1
while (@sTempEvtID <>-1)
begin
insert into @IDTable values (@sTempEvtID)
exec @sTempEvtID = @proce_name @sTempEvtID,-1
endselect EventID from @IDTable order by EventIDgocreate procedure UpdateUDEvent
@eventOldIfm int, --,@eventRepeat bit,
@eventID int,
@eventStartDate varchar(20),@eventStartHour varchar(10),@eventStartMinute varchar(10),
@eventEndDate varchar(20),@eventEndHour varchar(10),@eventEndMinute varchar(10),
@eventTitle varchar(30),@eventLocation varchar(30),@eventDescription varchar(250),
@pubUserName varchar(30),@subUserName varchar(30)
asdeclare @eventIDTable table(EventID int)
declare @procedure_name varchar(40)
set @procedure_name = 'GetEventIDList'
insert into @eventIDTable exec @procedure_name @eventIDif exists(select * from T_Event where
EventStartDate >= @eventStartDate and EventEndDate <= @eventEndDate
and EventStartHour = @eventStartHour and EventStartMinute = @eventStartMinute
and EventEndHour = @eventEndHour and EventEndMinute = @eventEndMinute
and EventTitle = @eventTitle and EventLocation = @eventLocation and EventDescription = @eventDescription
and PubUserName = @pubUserName and SubUserName = @subUserName and EventID not in (select EventID from @eventIDTable))
return
....
发了2个帖子..不少回帖..问题解决一个出一个..现在又卡住了.对执行字符串完全糊涂了.说说状况:
标蓝的部分是使用到@eventIDTable的地方还有很多..现在在2000下得不到有效的@eventIDTable,因为不支持红色部分.
有人提议用临时表..我用了.如下替换:
declare @tname varchar(50)
set @tname='#'+replace(newid(),'-','')declare @sql1 varchar(8000)
set @sql1='
create table '+@tname+'(EventID int)
declare @procedure_name varchar(40)
set @procedure_name = ''GetEventIDList '' declare @eventIDTable table(EventID int)insert into '+@tname+' exec @procedure_name ' + rtrim(@eventID)+'
insert into @eventIDTable select EventID from '+ @tname
exec (@sql1)
虽然@tname,@eventIDTable都可以插入有效的记录..但是在标蓝部分不变的情况下都会提示没定义@eventIDTable,怎么解决?另有疑问如下:
1.为什么我在标黄部分后加exec('select * from ' + @eventIDTable) 或select * from @eventIDTable 都取不到结果?
2.如果我把标绿部分的 @eventIDTable放出sql1字符连接之外定义,怎么让它取得和@tname一样的集合.
3.到底这个变量定义在字符串内外有什么区别..
4.为什么我定义临时表用"#"开头确找不到表,但它是存在的..而用其他确可以找到,"#"代表什么..问题弄了2天.蛮郁闷的.这里先感谢大家..谢谢!
@eventID int
as
declare @IDTable table(EventID int)
insert into @IDTable values (@eventID)declare @proce_name varchar(40),@sTempEvtID int
set @proce_name = 'GetEventID'exec @sTempEvtID = @proce_name @eventID,1 while (@sTempEvtID <>-1)
begin
insert into @IDTable values (@sTempEvtID)
exec @sTempEvtID = @proce_name @sTempEvtID,1
endexec @sTempEvtID = @proce_name @eventID,-1
while (@sTempEvtID <>-1)
begin
insert into @IDTable values (@sTempEvtID)
exec @sTempEvtID = @proce_name @sTempEvtID,-1
endselect EventID from @IDTable order by EventIDgocreate procedure UpdateUDEvent
@eventOldIfm int, --,@eventRepeat bit,
@eventID int,
@eventStartDate varchar(20),@eventStartHour varchar(10),@eventStartMinute varchar(10),
@eventEndDate varchar(20),@eventEndHour varchar(10),@eventEndMinute varchar(10),
@eventTitle varchar(30),@eventLocation varchar(30),@eventDescription varchar(250),
@pubUserName varchar(30),@subUserName varchar(30)
asdeclare @eventIDTable table(EventID int)
declare @procedure_name varchar(40)
set @procedure_name = 'GetEventIDList'
insert into @eventIDTable exec @procedure_name @eventIDif exists(select * from T_Event where
EventStartDate >= @eventStartDate and EventEndDate <= @eventEndDate
and EventStartHour = @eventStartHour and EventStartMinute = @eventStartMinute
and EventEndHour = @eventEndHour and EventEndMinute = @eventEndMinute
and EventTitle = @eventTitle and EventLocation = @eventLocation and EventDescription = @eventDescription
and PubUserName = @pubUserName and SubUserName = @subUserName and EventID not in (select EventID from @eventIDTable))
return
....
发了2个帖子..不少回帖..问题解决一个出一个..现在又卡住了.对执行字符串完全糊涂了.说说状况:
标蓝的部分是使用到@eventIDTable的地方还有很多..现在在2000下得不到有效的@eventIDTable,因为不支持红色部分.
有人提议用临时表..我用了.如下替换:
declare @tname varchar(50)
set @tname='#'+replace(newid(),'-','')declare @sql1 varchar(8000)
set @sql1='
create table '+@tname+'(EventID int)
declare @procedure_name varchar(40)
set @procedure_name = ''GetEventIDList '' declare @eventIDTable table(EventID int)insert into '+@tname+' exec @procedure_name ' + rtrim(@eventID)+'
insert into @eventIDTable select EventID from '+ @tname
exec (@sql1)
虽然@tname,@eventIDTable都可以插入有效的记录..但是在标蓝部分不变的情况下都会提示没定义@eventIDTable,怎么解决?另有疑问如下:
1.为什么我在标黄部分后加exec('select * from ' + @eventIDTable) 或select * from @eventIDTable 都取不到结果?
2.如果我把标绿部分的 @eventIDTable放出sql1字符连接之外定义,怎么让它取得和@tname一样的集合.
3.到底这个变量定义在字符串内外有什么区别..
4.为什么我定义临时表用"#"开头确找不到表,但它是存在的..而用其他确可以找到,"#"代表什么..问题弄了2天.蛮郁闷的.这里先感谢大家..谢谢!
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
insert into @IDTable values(1)-- @IDTable变量做表名.没用动态sql,反而正确
select * from @IDTable -- 同上
exec ('select * from ' + @IDTable) -- 这却是错误的declare @sqls varchar(1000)
set @sqls = 'select * from ' +@IDTable -- 这也是错误的
exec(@sqls)
----
这不是和楼上写的冲突么..