我用带参数查询的存储过程,如果参数为空就查全部记录:
create proc [dbo].[scanstore04](@工程号 varchar (20))
as
begin
declare @sql varchar (4000)
set @sql=N'SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh, bh
FROM dbo.ScanData
WHERE 1=1
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if @工程号 <>''
set @sql=@sql+' and gch='''+@工程号+''''
exec(@sql)
end
用Report Builder 2.0 做报表时,所选字段不出来,如果,把COUNT(stationNAME) GROUP BY ……去掉后,就能出字段。SQL报表存储
create proc [dbo].[scanstore04](@工程号 varchar (20))
as
begin
declare @sql varchar (4000)
set @sql=N'SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh, bh
FROM dbo.ScanData
WHERE 1=1
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if @工程号 <>''
set @sql=@sql+' and gch='''+@工程号+''''
exec(@sql)
end
用Report Builder 2.0 做报表时,所选字段不出来,如果,把COUNT(stationNAME) GROUP BY ……去掉后,就能出字段。SQL报表存储
SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh, bh
FROM dbo.ScanData
WHERE 1=1
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh
and gch='工程号参数的值'
(@工程号 varchar (20))
as
begin
declare @sql varchar(4000)set @sql=N'SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh, bh
FROM dbo.ScanData
WHERE 1=1 '
if @工程号<>''
set @sql=@sql+' and gch='''+@工程号+''' 'set @sql=@sql+' GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh 'exec(@sql)
end
as
begin
declare @sql varchar (4000)
set @工程号=ISNULL(@工程号,'')
set @sql=N'
SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh, bh
FROM dbo.ScanData
WHERE 1=1 '
if @工程号 <>''
set @sql=@sql+' and gch='''+@工程号+'''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
else
set @sql=@sql+' GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh 'exec(@sql)
end
GO
ALTER proc [dbo].[scanstore04](@作业站 varchar (10),@工程号 varchar (20),@图档号 varchar (20),@板号 varchar (20),@startdate varchar(20),@enddate varchar(20))
as
begin
declare @sql varchar (4000)
set @sql=N'SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh,bh
FROM dbo.ScanData WHERE 1=1'
if @作业站<>''
set @sql=@sql+' and stationName='''+@作业站+'''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if @工程号<>''
set @sql=@sql+' and gch='''+@工程号+'''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if @图档号<>''
set @sql=@sql+' and tdh='''+@图档号+'''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if @板号<>''
set @sql=@sql+' and bh='''+@板号+'''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if (@startdate<>'')
set @sql=@sql+' and scandate>=''' + @startdate + '''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
if (@enddate<>'')
set @sql=@sql+' and scandate<=''' + @enddate + '''
GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
else
set @sql=@sql+' GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh '
exec(@sqL)
(@作业站 varchar (10),
@工程号 varchar (20),
@图档号 varchar (20),
@板号 varchar (20),
@startdate varchar(20),
@enddate varchar(20))
as
begin
declare @sql varchar(6000)set @sql=N'SELECT barcode1,cast(sl as int)as sl, COUNT(stationName) AS zysl, stationName, zc, bzl, cl, ys, cc, gch, tdh,bh
FROM dbo.ScanData WHERE 1=1 'if @作业站<>''
set @sql=@sql+' and stationName='''+@作业站+''' '
if @工程号<>''
set @sql=@sql+' and gch='''+@工程号+''' 'if @图档号<>''
set @sql=@sql+' and tdh='''+@图档号+''' 'if @板号<>''
set @sql=@sql+' and bh='''+@板号+''' 'if @startdate<>''
set @sql=@sql+' and scandate>=''' + @startdate + ''' 'if @enddate<>''
set @sql=@sql+' and scandate<=''' + @enddate + ''' 'set @sql=@sql+' GROUP BY barcode1,stationName, sl, zc, bzl, cl, ys, cc, gch, tdh, bh 'exec(@sqL)end