当前位置: 代码迷 >> Sql Server >> 大神
  详细解决方案

大神

热度:263   发布时间:2016-04-27 19:16:50.0
求助大神


create view View_GetCoupon
as 
SELECT [User].Username, [User].Email, Team.Title,team.city_id, Coupon.* FROM Coupon LEFT OUTER JOIN Team ON Coupon.Team_id = Team.Id LEFT OUTER JOIN [User] ON Coupon.User_id = [User].Id

 
IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'V_GetAsk' AND type = 'V')
  DROP view V_GetAsk


create view V_GetAsk
as 
SELECT Ask.*, Team.Title FROM Ask INNER JOIN Team ON Ask.Team_id = Team.Id


IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'sp_GetListByPageAndFileds' AND type = 'P')
  DROP PROCEDURE sp_GetListByPageAndFileds
 

CREATE 
 PROCEDURE [sp_GetListByPageAndFileds]

@pageSize int,  
@currentPage int = 1,  
@fields varchar(2000), 
@tablename varchar(200),  
@orderString varchar(1000),  
@whereString varchar(1000)  
)
AS
BEGIN
 DECLARE @sql varchar(2000)
 DECLARE @strOrder varchar(2000)
 DECLARE @strWhere varchar(2000)
 declare @recordcount int 
 declare @convertorderstr varchar(100)
declare @countsql nvarchar(500)
declare @totalpage int
 set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','')
 if @strOrder != ''
  set @strOrder = ' order by ' + @strOrder
 else
  set @strOrder = ' order by ID DESC'
set @strOrder=lower(@strOrder)
set @convertorderstr=replace(@strOrder,'desc','d_e_s_c')
set @convertorderstr=replace(@convertorderstr,'asc','desc')
set @convertorderstr=replace(@convertorderstr,'d_e_s_c','asc')
 set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','')
 if @strWhere != ''
  set @strWhere = ' where ' + @strWhere

set @countsql='select @a=count(*) from ' + @tablename + @strWhere
exec sp_executesql @countsql,[email protected] int output',@recordcount output 


 if @pageSize = 0
  set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder
 else
 begin
if @[email protected]=0
set @[email protected][email protected]
else
set @[email protected][email protected]+1
if @totalpage <=1
set @currentPage=1 
if @totalpage <@currentPage 
set @[email protected]
  if @currentPage = 1
  set @sql = 'select top ' + Str(@pageSize)+' '+ @fields + ' from ' + @tablename + @strWhere + @strOrder
  else
if (@currentPage - 1) * @pageSize > @recordcount / 2
set @sql = 'select top ' + str(@pageSize) + ' * from (select top ' + str((@recordcount - (@currentPage - 1) * @pageSize)) + ' ' + @fields + ' from ' + @tablename + @strWhere + @convertorderstr + ') as t1 ' + @strOrder
else
set @sql = 'select * from(select top ' + str(@pageSize) + ' * from (select top ' + str(@pageSize * @currentPage) + ' ' + @fields + ' from ' + @tablename + @strWhere + @strOrder + ') as t1 ' + @convertorderstr + ') as t2 ' + @strOrder
end
set @sql = @sql + ' select '+str(@recordcount)+' as cnt'
 exec(@sql)

END

 
IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'V_GetOrder' AND type = 'V')
  DROP view V_GetOrder
 


CREATE VIEW [V_GetOrder]
AS
SELECT [Order].*, [User].Email, [User].Username, [User].Mobile AS Expr1
FROM [Order] INNER JOIN [User] ON [Order].User_id = [User].Id



消息 111,级别 15,状态 1,第 759 行
'CREATE VIEW' 必须是查询批次中的第一个语句。
消息 111,级别 15,状态 1,第 769 行
'CREATE VIEW' 必须是查询批次中的第一个语句。
消息 111,级别 15,状态 1,第 792 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
消息 156,级别 15,状态 1,第 847 行
关键字 'VIEW' 附近有语法错误。[size=12px][/size]
  相关解决方案