DECLARE tempDomainCount CURSOR FOR 这句下面如果用select * from table1是对的,但是用@str就是错的 ,谁能帮我解决下啊,由于业务的原因,sql是用@str拼出来的
declare @str='select * from table1'DECLARE tempDomainCount CURSOR FOR @strOPEN tempDomainCount
FETCH NEXT FROM tempDomainCount INTO @tm,@ip,@domain
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM tempDomainCount INTO @tm,@ip,@domain
end
CLOSE tempDomainCount
DEALLOCATE tempDomainCount
declare @str='select * from table1'DECLARE tempDomainCount CURSOR FOR @strOPEN tempDomainCount
FETCH NEXT FROM tempDomainCount INTO @tm,@ip,@domain
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM tempDomainCount INTO @tm,@ip,@domain
end
CLOSE tempDomainCount
DEALLOCATE tempDomainCount
解决方案 »
- 同类汇总的SQL语句如何实现?
- [面试题]SQL Server 2K 的Windows身份验证和SQL Server身份验证?
- 从SQLSERVER7升级到SQL2000后的怪问题(中文字符比较)
- 关于SQL中作业的问题?
- 求解 update性能优化问题。
- 请问在双机系统里怎样安装SQLServer2000?
- 数据库内数万张表时的查询问题...梭分求教!
- sql7.0中一user庫的Stored Procedures中執行master庫的stored procedure如何寫?
- VB中DataGrid中编辑问题 急急急!!!---先谢谢了
- 教我协一句sql语句
- sql求助
- 如何找回误删除的权限?
CREATE PROCEDURE dbo.GetZYFZR
@XMBH nvarchar(6),
@ZY nvarchar(10)
AS
declare @RETURN nvarchar(2000)
declare @TABLERY nvarchar(9)
declare @XM nvarchar(20)
declare @SQL nvarchar(200)
set @RETURN=""
set @TABLERY="k"+@XMBH+"RY"
--print @TABLERY
set @SQL="declare XM_CURSOR cursor for select XM from "+@TABLERY+" where zy='"+@ZY+"' and rylb='03'"
exec sp_executesql @SQL
open XM_CURSOR
fetch next from XM_CURSOR into @XM
while @@fetch_status=0
begin
--print @XM
select @RETURN=@RETURN+ltrim(rtrim(@XM))+" "
fetch next from XM_CURSOR into @XM
end
close XM_CURSOR
deallocate XM_CURSOR
select ltrim(rtrim(@RETURN))
GO
@XMBH nvarchar(6),
@ZY nvarchar(10)
AS
declare @RETURN nvarchar(2000)
declare @TABLERY nvarchar(9)
declare @XM nvarchar(20)
declare @SQL nvarchar(200)
set @RETURN=""
set @TABLERY="k"+@XMBH+"RY"
--print @TABLERY
set @SQL="declare XM_CURSOR cursor for select XM from "+@TABLERY+" where zy='"+@ZY+"' and rylb='03'"
exec sp_executesql @SQL
open XM_CURSOR
fetch next from XM_CURSOR into @XM
while @@fetch_status=0
begin
--print @XM
select @RETURN=@RETURN+ltrim(rtrim(@XM))+" "
fetch next from XM_CURSOR into @XM
end
close XM_CURSOR
deallocate XM_CURSOR
select ltrim(rtrim(@RETURN))
GO
标题:分别使用游标和动态SQL语句完成各列组成SQL语句
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2009-12-01
地点:广东深圳
地址:http://topic.csdn.net/u/20091201/23/72896af9-b647-4718-b29e-f6aaef3e258a.html?65000
*//*
tablename fieldname isdatetime datetimelength
-------------------------------------------------- -------------------------------------------------- ---------- --------------
Ids_ActualData sw1 0 NULL
Ids_ActualData sw2 0 NULL
Ids_ActualData ymdhm 1 16
Ids_Configuration zdID 0 NULL
Ids_Configuration zdmc 0 NULL
Ids_ActualData yl 0 NULLIds_Configuration.zdID zdid,Ids_Configuration.zdmc zdmc,convert(varchar(16),Ids_ActualData.ymdhm,120) ymdhm,Ids_ActualData.sw1 sw1,Ids_ActualData.sw2 sw2
其中convert(varchar(16),Ids_ActualData.ymdhm,120)是通过isdatetime来判断的,这里面的16就是datetimelength里存储的,请高手帮忙啊,谢谢
*/--使用油标完成的.
create table tb(tablename varchar(20), fieldname varchar(20), isdatetime int,datetimelength int)
insert into tb values('Ids_ActualData ' , 'sw1' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'sw2' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'ymdhm', 1 , 16)
insert into tb values('Ids_Configuration' , 'zdID' , 0 , NULL)
insert into tb values('Ids_Configuration' , 'zdmc' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'yl' , 0 , NULL)
godeclare @tablename varchar(20), @fieldname varchar(20), @isdatetime int,@datetimelength int;
declare @sql as varchar(8000)
set @sql = ''
declare cur cursor fast_forward for
select * from tb;
open cur;
fetch next from cur into @tablename , @fieldname , @isdatetime ,@datetimelength;
while @@fetch_status=0
begin
if @isdatetime = 1
set @sql = @sql + 'convert(varchar(' + cast(@datetimelength as varchar) + '),' + @tablename + '.' + @fieldname + ',120) ' + @fieldname + ','
else
set @sql = @sql + @tablename + '.' + @fieldname + ' ' + @fieldname + ','
fetch next from cur into @tablename , @fieldname , @isdatetime ,@datetimelength;
end
close cur;
deallocate cur;set @sql = left(@sql , len(@sql) - 1)
print @sql drop table tb/*
Ids_ActualData .sw1 sw1,Ids_ActualData .sw2 sw2,convert(varchar(16),Ids_ActualData .ymdhm,120) ymdhm,Ids_Configuration.zdID zdID,Ids_Configuration.zdmc zdmc,Ids_ActualData .yl yl
*/
--使用动态SQL语句完成.
create table tb(tablename varchar(20), fieldname varchar(20), isdatetime int,datetimelength int)
insert into tb values('Ids_ActualData ' , 'sw1' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'sw2' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'ymdhm', 1 , 16)
insert into tb values('Ids_Configuration' , 'zdID' , 0 , NULL)
insert into tb values('Ids_Configuration' , 'zdmc' , 0 , NULL)
insert into tb values('Ids_ActualData ' , 'yl' , 0 , NULL)
godeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + t.tablename + '.' + t.fieldname + ' ' + t.fieldname + ',' from (select * from tb where isdatetime = 0) as t
select @sql = @sql + 'convert(varchar(' + cast(t.datetimelength as varchar) + '),' + tablename + '.' + fieldname + ',120) ' + fieldname + ',' from (select * from tb where isdatetime = 1) as t
set @sql=left(@sql , len(@sql) - 1)
print @sql drop table tb/*
Ids_ActualData .sw1 sw1,Ids_ActualData .sw2 sw2,Ids_Configuration.zdID zdID,Ids_Configuration.zdmc zdmc,Ids_ActualData .yl yl,convert(varchar(16),Ids_ActualData .ymdhm,120) ymdhm
*/
create table #tb () --字段及数据类型和table1的一致
insert into #tb exec(@sql)DECLARE tempDomainCount CURSOR FOR
select * from #tbOPEN tempDomainCount
FETCH NEXT FROM tempDomainCount INTO @tm,@ip,@domain
WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM tempDomainCount INTO @tm,@ip,@domain
end
CLOSE tempDomainCount
DEALLOCATE tempDomainCount