SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE proc dbo.proSql @sql nvarchar(50)as begin print @sql
exec(@sql) end GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 看看OK?
create procedure XiaoZhengGe @sqlstr nvarchar(4000), --查询字符串 @currentpage int, --第N页 @pagesize int --每页行数 as set nocount on declare @P1 int, --P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off 这个存储过程
主要是那个SQL语句外边有单引号里边条件也有引号这有错误不知道该怎么样定
这是执行语句 XiaoZhengGe'select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = "城关"and a.nian = "2007" and a.yue = "8" and a.highway = "平度市" GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu',1,15
declare @sql nvarchar(4000) set @sql = 'select * from Northwind.dbo.Categories where CategoryName = ' + char(39)+'Beverages' + char(39) exec dbo.testpro @sql如上调用示例,参数 @sql 修改成 nvarchar(4000)
XiaoZhengGe "select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = '平度市'GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = '平度市'GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = '城关'and a.nian = '2007' and a.yue = '8' and a.highway = '平度市' GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu",1,15 XiaoZhengGe 'select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = '平度市'GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = '平度市'GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = '城关'and a.nian = '2007' and a.yue = '8' and a.highway = '平度市' GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu',1,15 XiaoZhengGe 'select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = "城关"and a.nian = "2007" and a.yue = "8" and a.highway = "平度市" GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu',1,15 XiaoZhengGe " select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = "城关"and a.nian = "2007" and a.yue = "8" and a.highway = "平度市" GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu ",1,15
set quoted_identifier off
GO
SET ANSI_NULLS ON
GO
CREATE proc dbo.proSql
@sql nvarchar(50)as begin
print @sql
exec(@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
看看OK?
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
这个存储过程
XiaoZhengGe'select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = "城关"and a.nian = "2007" and a.yue = "8" and a.highway = "平度市" GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu',1,15
set @sql = 'select * from Northwind.dbo.Categories where CategoryName = ' + char(39)+'Beverages' + char(39)
exec dbo.testpro @sql如上调用示例,参数 @sql 修改成 nvarchar(4000)
XiaoZhengGe 'select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = '平度市'GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = '平度市'GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = '城关'and a.nian = '2007' and a.yue = '8' and a.highway = '平度市' GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu',1,15
XiaoZhengGe 'select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = "城关"and a.nian = "2007" and a.yue = "8" and a.highway = "平度市" GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu',1,15
XiaoZhengGe " select luxian,xiangmu,sum(gcshuliang) as gcshuliang,sum(leiji) as leiji from (select TOP 100 PERCENT a.road_id as luxian ,a.xiangmu ,a.gcshuliang,sum(b.gcshuliang) as leiji from (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as a left outer join (SELECT danwei,road_id, luxian, xiangmu, YEAR(riqi) AS nian, MONTH(riqi) AS yue,SUM(gcshuliang) AS gcshuliang,highway FROM shengchanriji where Highway = "平度市"GROUP BY danwei,road_id, luxian, xiangmu, YEAR(riqi), MONTH(riqi),highway) as b on a.danwei = b.danwei AND a.luxian = b.luxian AND a.xiangmu = b.xiangmu AND a.nian = b.nian and a.highway = b.highway where 1=1 and a.danwei = "城关"and a.nian = "2007" and a.yue = "8" and a.highway = "平度市" GROUP BY a.road_id, a.xiangmu,a.nian,a.gcshuliang ORDER BY a.road_id,a.xiangmu) as c group by c.luxian,c.xiangmu order by c.luxian,c.xiangmu ",1,15