请教个问题,我用c#调用存储过程来取值。
存储过程中定义了三个类似declare @sql varchar(8000)的变量 ,
然后三个 set @sql='select 。'
最后用exec(@sql)取值,
因为表比较大,所以varchar(8000)放不下,就分成三个这样的语句来查询一个表(动态获取的字段名,通过colid来分开),
但是这样返回的结果集也成了三个,
c#调用的时候只能调用到第一个,
如何全部调用后绑定到Gridview
存储过程中定义了三个类似declare @sql varchar(8000)的变量 ,
然后三个 set @sql='select 。'
最后用exec(@sql)取值,
因为表比较大,所以varchar(8000)放不下,就分成三个这样的语句来查询一个表(动态获取的字段名,通过colid来分开),
但是这样返回的结果集也成了三个,
c#调用的时候只能调用到第一个,
如何全部调用后绑定到Gridview
declare @sql1 varchar(8000),@sql2 varchar(8000),@sql2 varchar(8000)
set @sql1='select ...'
set @sql2='union all select ...'
set @sql3='union all select ...'
exec(@sql1+@sql2+@sql3)
如果数据库环境是SQL Server 2005及以上版本,varchar类型变量内容允许超过8000字节,定义为 varchar(max) 即可。
@begintime DateTime,
@endtime DateTime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000) if (datediff(hh,@begintime,@endtime) <=24 )
set @g = 'convert(varchar(16),sj,120)'
if ((datediff(hh,@begintime,@endtime) > 24 ) and (datediff(hh,@begintime,@endtime) <= 720 ))
set @g = 'convert(varchar(13),sj,120)'
if ((datediff(hh,@begintime,@endtime) >720 ) and (datediff(hh,@begintime,@endtime) <= 8760 ))
set @g = ' convert(varchar(10),sj,120)'
if (datediff(hh,@begintime,@endtime) > 8760 )
set @g = ' convert(varchar(7),sj,120)' select @sql=isnull(@sql+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid<=70 set @sql='select '+@sql+','+@g+' AS [sj] from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+''
select @sql1=isnull(@sql1+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>70 and colid<=140 set @sql1='select '+@sql1+' from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+''
select @sql2=isnull(@sql2+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>140 set @sql2='select '+@sql2+' from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+''
exec(@sql+@sql1+@sql2)
end
GO
union all出错,你们自己调一下吧,试一试。我的表有200多个字段。
SqlDataAdapter ada = new SqlDataAdapter();
SqlCommand sqlcom = new SqlCommand("ysjcx", sqlcon);
SqlParameter pbegintime = new SqlParameter("@begintime", SqlDbType.DateTime);
pbegintime.Value = Convert.ToDateTime(sbegintime);
sqlcom.Parameters.Add(pbegintime); SqlParameter pendtime = new SqlParameter("@endtime", SqlDbType.DateTime);
pendtime.Value = Convert.ToDateTime(sendtime);
sqlcom.Parameters.Add(pendtime); SqlParameter psjb = new SqlParameter("@sjb", SqlDbType.VarChar, 100);
psjb.Value = ssjb;
sqlcom.Parameters.Add(psjb); sqlcom.CommandType = CommandType.StoredProcedure; ada.SelectCommand = sqlcom;
DataSet ds = new DataSet();
ada.Fill(ds);
sousuo1.DataSource = ds;
sousuo1.DataBind();取数据的语句。
CREATE proc ysjcx
@begintime DateTime,
@endtime DateTime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000) if (datediff(hh,@begintime,@endtime) <=24 )
set @g = 'convert(varchar(16),sj,120)'
if ((datediff(hh,@begintime,@endtime) > 24 ) and (datediff(hh,@begintime,@endtime) <= 720 ))
set @g = 'convert(varchar(13),sj,120)'
if ((datediff(hh,@begintime,@endtime) >720 ) and (datediff(hh,@begintime,@endtime) <= 8760 ))
set @g = ' convert(varchar(10),sj,120)'
if (datediff(hh,@begintime,@endtime) > 8760 )
set @g = ' convert(varchar(7),sj,120)' select @sql=isnull(@sql+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid<=70 set @sql='select '+@sql+','+@g+' AS [sj] from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+''
select @sql1=isnull(@sql1+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>70 and colid<=140 set @sql1='select '+@sql1+' from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+''
select @sql2=isnull(@sql2+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>140 set @sql2='select '+@sql2+' from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+''
exec(@sql+' union all '+@sql1+' union all '+@sql2) ---三个查询的select 列都一样吧 就这样写 其实很简单
end
GO
CREATE proc ysjcx
@begintime DateTime,
@endtime DateTime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000) if (datediff(hh,@begintime,@endtime) <=24 )
set @g = 'convert(varchar(16),sj,120)'
if ((datediff(hh,@begintime,@endtime) > 24 ) and (datediff(hh,@begintime,@endtime) <= 720 ))
set @g = 'convert(varchar(13),sj,120)'
if ((datediff(hh,@begintime,@endtime) >720 ) and (datediff(hh,@begintime,@endtime) <= 8760 ))
set @g = ' convert(varchar(10),sj,120)'
if (datediff(hh,@begintime,@endtime) > 8760 )
set @g = ' convert(varchar(7),sj,120)' select @sql=isnull(@sql+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid<=70 set @sql='select '+@sql+','+@g+' AS [sj] from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' '
select @sql1=isnull(@sql1+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>70 and colid<=140 set @sql1='select '+@sql1+' from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' '
select @sql2=isnull(@sql2+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>140 set @sql2='select '+@sql2+' from '+@sjb+' where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' '
exec(@sql+' union all '+@sql1+' union all '+@sql2+' order by '+@g+'') ---三个查询的select 列都一样吧 就这样写 其实很简单
end
GO[/code]
你可以分开三次执行呀,要不然放EXEC()里用;分开
[Microsoft][ODBC SQL Server Driver][SQL Server]在关键字 'union' 附近有语法错误。
@begintime DateTime,
@endtime DateTime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @strSelect varchar(8000)
if (datediff(hh,@begintime,@endtime) <=24 )
set @g = 'convert(varchar(16),sj,120)'
if ((datediff(hh,@begintime,@endtime) > 24 ) and (datediff(hh,@begintime,@endtime) <= 720 ))
set @g = 'convert(varchar(13),sj,120)'
if ((datediff(hh,@begintime,@endtime) >720 ) and (datediff(hh,@begintime,@endtime) <= 8760 ))
set @g = ' convert(varchar(10),sj,120)'
if (datediff(hh,@begintime,@endtime) > 8760 )
set @g = ' convert(varchar(7),sj,120)' select @sql=isnull(@sql+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid<=70 select @sql1=isnull(@sql1+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>70 and colid<=140 select @sql2=isnull(@sql2+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>140 exec('select '+@sql+','+ @sql1+','+sql2+' from '+@sjb+ '
where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+' ')
end
GO
试试
@begintime DateTime,
@endtime DateTime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @strSelect varchar(8000)
if (datediff(hh,@begintime,@endtime) <=24 )
set @g = 'convert(varchar(16),sj,120)'
if ((datediff(hh,@begintime,@endtime) > 24 ) and (datediff(hh,@begintime,@endtime) <= 720 ))
set @g = 'convert(varchar(13),sj,120)'
if ((datediff(hh,@begintime,@endtime) >720 ) and (datediff(hh,@begintime,@endtime) <= 8760 ))
set @g = ' convert(varchar(10),sj,120)'
if (datediff(hh,@begintime,@endtime) > 8760 )
set @g = ' convert(varchar(7),sj,120)' select @sql=isnull(@sql+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid<=70 select @sql1=isnull(@sql1+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>70 and colid<=140 select @sql2=isnull(@sql2+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>140 exec('select '+@sql+','+ @sql1+','+@sql2+' from '+@sjb+ '
where sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''' group by '+@g+' order by '+@g+' ')
end
GO
试试
如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1
@begintime DateTime,
@endtime DateTime,
@sjb varchar(100)
as
begin
declare @g varchar(8000)
declare @sql varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @strTime as nvarchar(100)
if (datediff(hh,@begintime,@endtime) <=24 )
set @g = 'convert(varchar(16),sj,120)'
if ((datediff(hh,@begintime,@endtime) > 24 ) and (datediff(hh,@begintime,@endtime) <= 720 ))
set @g = 'convert(varchar(13),sj,120)'
if ((datediff(hh,@begintime,@endtime) >720 ) and (datediff(hh,@begintime,@endtime) <= 8760 ))
set @g = ' convert(varchar(10),sj,120)'
if (datediff(hh,@begintime,@endtime) > 8760 )
set @g = ' convert(varchar(7),sj,120)' select @sql=isnull(@sql+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid<=70 select @sql1=isnull(@sql1+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>70 and colid<=140 select @sql2=isnull(@sql2+',','')+'cast(avg('+name+') as decimal(12,2)) as ['+name+']'
from syscolumns
where id=object_id(@sjb) and name<>'riqi' and name<>'uid' and name<>'sj' and colid>140 select @strTime='sj between ''' + convert(nvarchar,@begintime) + ''' and ''' + convert(nvarchar,@endtime) + ''''
exec('select '+@sql+','+ @sql1+','+@sql2+' from '+@sjb+ ' where '+ @strTime +' group by '+@g+' order by '+@g+' ')
end
GO
exec('select '+@sql+', '+@sql1+','+@sql2+','+@g+' AS [sj] from '+@sjb+' where sj between ''' +@begintime+ ''' and ''' + @endtime + ''' group by '+@g+' order by '+@g+'' )
就能行