谢谢大家帮忙!
kelph 请问,请问如何借助系统表? 我再描述一遍,根据参数计算得到了@sql的值,假如有4个参数,我要声明4个参数进行接收吧,如果是40个呢?我该如何办呢,列是动态显示的
我的最后目的就是依次循环所有列,按照列名进行统计set @strSql='declare ZGS_CURSOR cursor for select * table'
exec sp_executesql @strSql
open ZGS_CURSOR
--如何进行循环接收变量
fetch next from ZGS_CURSOR into @接收变量1,@接受变量2......多个
close ZGS_CURSOR
kelph 请问,请问如何借助系统表? 我再描述一遍,根据参数计算得到了@sql的值,假如有4个参数,我要声明4个参数进行接收吧,如果是40个呢?我该如何办呢,列是动态显示的
我的最后目的就是依次循环所有列,按照列名进行统计set @strSql='declare ZGS_CURSOR cursor for select * table'
exec sp_executesql @strSql
open ZGS_CURSOR
--如何进行循环接收变量
fetch next from ZGS_CURSOR into @接收变量1,@接受变量2......多个
close ZGS_CURSOR
declare @s2 as varchar(10)
declare @s3 as varchar(10)
declare @s4 as varchar(10)
set @s1 = 'col1'
set @s2 = 'col2'
set @s3 = 'col3'
set @s4 = 'col4'declare @sql as varchar(100)
set @sql = 'select ' + @s1 + ',' + @s2 + ',' + @s3 + ',' + @s4 + ' from tb'
exec(@sql)
/*
add by mingfei 20080312 计算工时
*/
CREATE PROCEDURE procCalcGS
@XMNO int,
@SJ1 datetime,
@SJ2 datetime,
@zGs float output,
@zcGS float output,
@jbGS float output,
@wyGS float output,
@ccGS float output,
@lsgccGS float output,
@lsgzcGS float output,
@lsgjbGS float output,
@lsgwyGS float output AS declare @fltZgs float
declare @startYear int
declare @startMonth int
declare @endYear int
declare @endMonth int
declare @i int
declare @m int
declare @n int
declare @strField nvarchar(100)
declare @strSql nvarchar(1000) set @m=0
set @n=0 set @startYear=year(@SJ1)
set @startMonth=month(@SJ1)
set @endYear=year(@SJ2)
set @endMonth=month(@SJ2)
set @strField=''
set @strSql=''
if @startYear=@endYear --说明查询的同年
begin
set @m=@startMonth
while @m<=@endMonth
begin
set @n=1
while @n<=4
begin
if @m<10
set @strField='sum(F0'+cast(@m as nvarchar(10))+cast(@n as nvarchar(10))+') as '+'F0'+cast(@m as nvarchar(10)) +cast(@n as nvarchar(10))
else
set @strField='sum(F'+cast(@m as nvarchar(10))+cast(@n as nvarchar(10))+') as '+'F'+cast(@m as nvarchar(10)) +cast(@n as nvarchar(10))
print @strSql
set @strSql=@strSql+@strField+','
--print @strSql
set @n=@n+1
end
set @m=@m+1
end
set @strSql=Substring(@strSql,1,len(@strSql)-1) --//注意将最后一个“,"删除
set @strSql='select '+@strSql+' from '+' rytj'+cast(@startYear as nvarchar(10))+' where xmno='+cast(@XMNO as nvarchar(10))
--print @strSql
--set @strSql='declare ZGS_CURSOR cursor for select '+@strSql+' from '+' rytj'+cast(@startYear as nvarchar(10))+' where xmno='+cast(@XMNO as nvarchar(10))
exec sp_executesql @strSql --open ZGS_CURSOR
--fetch next from ZGS_CURSOR
--close ZGS_CURSOR
end
GO
add by mingfei 20080312 计算工时
*/
CREATE PROCEDURE procCalcGS
@XMNO int,
@SJ1 datetime,
@SJ2 datetime,
@zGs float output,
@zcGS float output,
@jbGS float output,
@wyGS float output,
@ccGS float output,
@lsgccGS float output,
@lsgzcGS float output,
@lsgjbGS float output,
@lsgwyGS float output AS declare @fltZgs float
declare @startYear int
declare @startMonth int
declare @endYear int
declare @endMonth int
declare @i int
declare @m int
declare @n int
declare @strField nvarchar(100)
declare @strSql nvarchar(1000) set @m=0
set @n=0 set @startYear=year(@SJ1)
set @startMonth=month(@SJ1)
set @endYear=year(@SJ2)
set @endMonth=month(@SJ2)
set @strField=''
set @strSql=''
if @startYear=@endYear --说明查询的同年
begin
set @m=@startMonth
while @m<=@endMonth
begin
set @n=1
while @n<=4
begin
if @m<10
set @strField='sum(F0'+cast(@m as nvarchar(10))+cast(@n as nvarchar(10))+') as '+'F0'+cast(@m as nvarchar(10)) +cast(@n as nvarchar(10))
else
set @strField='sum(F'+cast(@m as nvarchar(10))+cast(@n as nvarchar(10))+') as '+'F'+cast(@m as nvarchar(10)) +cast(@n as nvarchar(10))
print @strSql
set @strSql=@strSql+@strField+','
--print @strSql
set @n=@n+1
end
set @m=@m+1
end
set @strSql=Substring(@strSql,1,len(@strSql)-1) --//注意将最后一个“,"删除
set @strSql='select '+@strSql+' from '+' rytj'+cast(@startYear as nvarchar(10))+' where xmno='+cast(@XMNO as nvarchar(10))
--print @strSql
--set @strSql='declare ZGS_CURSOR cursor for select '+@strSql+' from '+' rytj'+cast(@startYear as nvarchar(10))+' where xmno='+cast(@XMNO as nvarchar(10))
exec sp_executesql @strSql --open ZGS_CURSOR
--fetch next from ZGS_CURSOR
--close ZGS_CURSOR
end
GO
如果我只是将上面的结果返回客户端,用c#就可以这样处理了
for(j=0;j< dstTemp.Tables[0].Columns.Count;j++)
{
if (dstTemp.Tables[0].Rows[0][j] is DBNull)
{ }
else
{
switch(dstTemp.Tables[0].Columns[j].ColumnName.Substring(3,1))
{
case "1":
zcGs+=System.Convert.ToSingle((dstTemp.Tables[0].Rows[0][j]));
break; case "2":
jbGs+=System.Convert.ToSingle((dstTemp.Tables[0].Rows[0][j]));
break; case "3":
wyGs+=System.Convert.ToSingle((dstTemp.Tables[0].Rows[0][j]));
break;
case "4":
ccGs+=System.Convert.ToSingle((dstTemp.Tables[0].Rows[0][j]));
break;
}//end switch
}//end else
}//end for也就是如何将c#处理的这段代码在存储过程进行处理
換前臺處理好了,比如LZ說的C#
set @strSql = @strsql+' declare ZGS_CURSOR cursor for select '+@sql+' table '
set @strsql = @strsql+' open zgs_cursor '
set @strsql = @strsql+' fetch next from ZGS_CURSOR into @'+replace(@sql,',','@,)
set @strsql = @strsql+' while ....'
set @strsql = @strsql+' close zgs_cursor'
exec sp_executesql @strSql
set @strSql = @strsql+' declare ZGS_CURSOR cursor for select '+@sql+' table '
set @strsql = @strsql+' open zgs_cursor '
set @strsql = @strsql+' fetch next from ZGS_CURSOR into @'+replace(@sql,',',',@',)
set @strsql = @strsql+' while ....'
set @strsql = @strsql+' close zgs_cursor'
exec sp_executesql @strSql
set @strSql = @strsql+' declare ZGS_CURSOR cursor for select '+@sql+' table '
set @strsql = @strsql+' open zgs_cursor '
set @strsql = @strsql+' fetch next from ZGS_CURSOR into @'+replace(@sql,',',',@',)
set @strsql = @strsql+' while ....'
set @strsql = @strsql+' close zgs_cursor'
exec sp_executesql @strSql
你这种方式我昨天试了一下,感觉很麻烦,假如有5列(是用replace分离出来的,不知道名称),在下面语句中
set @strsql = @strsql+' while ....'
我该怎么循环处理呢