没仔细看,不知道“数据集合里面预览”是什么意思加多一句试试
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.FittingsDetalis_SelectByParamsPerPage
(
@TotalRecs int,
@PageIndex int,
@PageCount int,
@Fittings_No nvarchar(10),
@FittingsDetalis_Model nvarchar(20),
@FittingsDetalis_Name nvarchar(20),
@FittingsStock_Stock1 nvarchar(10),/*库存量*/
@FittingsStock_Stock2 nvarchar(10),
@OpWholesale1 nvarchar(20),/*批发价*/
@OpWholesale2 nvarchar(20),
@OpRetail1 nvarchar(20),/*零售价*/
@OpRetail2 nvarchar(20),
@OpRefrence1 nvarchar(20),/*指导价*/
@OpRefrence2 nvarchar(20),
@OpTime1 nvarchar(20),/*时间*/
@OpTime2 nvarchar(20)
)
AS
SET NOCOUNT ON; --加这里一句
declare @str varchar(5000)
declare @firstrec varchar(10)
declare @lastrec varchar(10)
declare @firstreci int
declare @lastreci int declare @strSql varchar(3000)
declare @strColums varchar(1500)
set @firstrec=(@PageIndex-1)*@PageCount+1
set @lastreci=@PageIndex*@PageCount
set @firstrec=cast(@firstrec as varchar(10))
if @PageIndex*@PageCount>=@TotalRecs
set @lastrec=cast(@TotalRecs as varchar(10))
else
set @lastrec=cast(@lastreci as varchar(10))
set @strSql=' where 1=1 ' set @strColums='SELECT FittingsDetalis_No, Fittings_No, FittingsDetalis_Name,
FittingsDetalis_Units,FittingsDetalis_Wholesale,FittingsDetalis_Retail,
FittingsDetalis_Reference,FittingsDetalis_CreateDate ,
isnull((select FittingsStock_Stock from FittingsStock where
FittingsStock_FittingsDetalis_No=FittingsDetalis.FittingsDetalis_No
and FittingsStock_No=(SELECT MAX(FittingsStock_No) from FittingsStock
where FittingsStock_FittingsDetalis_No=FittingsDetalis.FittingsDetalis_No) ),0)
as FittingsStock_Stock from FittingsDetalis
' if isnull(@Fittings_No,'') <> ''
set @strSql = @strSql + ' AND (Fittings_No = ''' + @Fittings_No +''')' if isnull(@FittingsDetalis_Name,'') <> ''
set @strSql = @strSql + ' AND (FittingsDetalis_Name like ''%' + @FittingsDetalis_Name +'%'')' if isnull(@OpWholesale1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Wholesale>=''' + @OpWholesale1 +''')'
if isnull(@OpWholesale2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Wholesale <=''' + @OpWholesale2 +''')' if isnull(@FittingsStock_Stock1,'') <>''
set @strSql = @strSql + ' AND (FittingsStock_Stock>=''' + @FittingsStock_Stock1 +''')'
if isnull(@FittingsStock_Stock2,'') <>''
set @strSql = @strSql + ' AND (FittingsStock_Stock <=''' + @FittingsStock_Stock2 +''')' if isnull(@OpRetail1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Retail>=''' + @OpRetail1 +''')'
if isnull(@OpRetail2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Retail <=''' + @OpRetail2 +''')' if isnull(@OpRefrence1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Reference>=''' + @OpRefrence1 +''')'
if isnull(@OpRefrence2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Reference <=''' + @OpRefrence2 +''')' if isnull(@OpTime1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_CreateDate>=''' + @OpTime1 +''')'
if isnull(@OpTime2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_CreateDate <=''' + @OpTime2 +''')' CREATE TABLE #temp (ser int)
execute('insert into #temp select Ser from FittingsDetalis '+@strSql+'order by Ser ')
set @str=rtrim(@strColums)+' where Ser in (select ser from #temp where ser>='+'(select
max(ser) from #temp where ser in (select top '+@firstrec +' ser from #temp ))'
+' and ser <=(select max(ser) from #temp where ser in (select top '+@lastrec +' ser
from #temp )))'
--select #temp
--print @str
execute(@str)
--exec FittingsDetalis_SelectByParamsPerPage 100,1,10,'','','','','','','','','','','','',''
--exec FittingsDetalis_SelectByParamsPerPage 100,1,10,null,null,null,null,null,null,null,null,null,null,null,null,null
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.FittingsDetalis_SelectByParamsPerPage
(
@TotalRecs int,
@PageIndex int,
@PageCount int,
@Fittings_No nvarchar(10),
@FittingsDetalis_Model nvarchar(20),
@FittingsDetalis_Name nvarchar(20),
@FittingsStock_Stock1 nvarchar(10),/*库存量*/
@FittingsStock_Stock2 nvarchar(10),
@OpWholesale1 nvarchar(20),/*批发价*/
@OpWholesale2 nvarchar(20),
@OpRetail1 nvarchar(20),/*零售价*/
@OpRetail2 nvarchar(20),
@OpRefrence1 nvarchar(20),/*指导价*/
@OpRefrence2 nvarchar(20),
@OpTime1 nvarchar(20),/*时间*/
@OpTime2 nvarchar(20)
)
AS
SET NOCOUNT ON; --加这里一句
declare @str varchar(5000)
declare @firstrec varchar(10)
declare @lastrec varchar(10)
declare @firstreci int
declare @lastreci int declare @strSql varchar(3000)
declare @strColums varchar(1500)
set @firstrec=(@PageIndex-1)*@PageCount+1
set @lastreci=@PageIndex*@PageCount
set @firstrec=cast(@firstrec as varchar(10))
if @PageIndex*@PageCount>=@TotalRecs
set @lastrec=cast(@TotalRecs as varchar(10))
else
set @lastrec=cast(@lastreci as varchar(10))
set @strSql=' where 1=1 ' set @strColums='SELECT FittingsDetalis_No, Fittings_No, FittingsDetalis_Name,
FittingsDetalis_Units,FittingsDetalis_Wholesale,FittingsDetalis_Retail,
FittingsDetalis_Reference,FittingsDetalis_CreateDate ,
isnull((select FittingsStock_Stock from FittingsStock where
FittingsStock_FittingsDetalis_No=FittingsDetalis.FittingsDetalis_No
and FittingsStock_No=(SELECT MAX(FittingsStock_No) from FittingsStock
where FittingsStock_FittingsDetalis_No=FittingsDetalis.FittingsDetalis_No) ),0)
as FittingsStock_Stock from FittingsDetalis
' if isnull(@Fittings_No,'') <> ''
set @strSql = @strSql + ' AND (Fittings_No = ''' + @Fittings_No +''')' if isnull(@FittingsDetalis_Name,'') <> ''
set @strSql = @strSql + ' AND (FittingsDetalis_Name like ''%' + @FittingsDetalis_Name +'%'')' if isnull(@OpWholesale1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Wholesale>=''' + @OpWholesale1 +''')'
if isnull(@OpWholesale2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Wholesale <=''' + @OpWholesale2 +''')' if isnull(@FittingsStock_Stock1,'') <>''
set @strSql = @strSql + ' AND (FittingsStock_Stock>=''' + @FittingsStock_Stock1 +''')'
if isnull(@FittingsStock_Stock2,'') <>''
set @strSql = @strSql + ' AND (FittingsStock_Stock <=''' + @FittingsStock_Stock2 +''')' if isnull(@OpRetail1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Retail>=''' + @OpRetail1 +''')'
if isnull(@OpRetail2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Retail <=''' + @OpRetail2 +''')' if isnull(@OpRefrence1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Reference>=''' + @OpRefrence1 +''')'
if isnull(@OpRefrence2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_Reference <=''' + @OpRefrence2 +''')' if isnull(@OpTime1,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_CreateDate>=''' + @OpTime1 +''')'
if isnull(@OpTime2,'') <>''
set @strSql = @strSql + ' AND (FittingsDetalis_CreateDate <=''' + @OpTime2 +''')' CREATE TABLE #temp (ser int)
execute('insert into #temp select Ser from FittingsDetalis '+@strSql+'order by Ser ')
set @str=rtrim(@strColums)+' where Ser in (select ser from #temp where ser>='+'(select
max(ser) from #temp where ser in (select top '+@firstrec +' ser from #temp ))'
+' and ser <=(select max(ser) from #temp where ser in (select top '+@lastrec +' ser
from #temp )))'
--select #temp
--print @str
execute(@str)
--exec FittingsDetalis_SelectByParamsPerPage 100,1,10,'','','','','','','','','','','','',''
--exec FittingsDetalis_SelectByParamsPerPage 100,1,10,null,null,null,null,null,null,null,null,null,null,null,null,null
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
可以直接预览查询结果 就是那个.xsd文件
情况1截图:
情况2截图:(1)
(2)
http://www.cnblogs.com/im_foto/gallery/186814.html
2L那个试过了,之前不小心给我删除了我其实就是想了解 子查询 跟 临时表 会不会冲突其他代码应该没问题,其他表都是这样做的,没出现这种错误
搜集了大牛们的sql语句集合,希望对像我一样的菜鸟能有所帮助