sql 存储过程全局变量

悬赏园豆：5 [已解决问题] 解决于 2015-01-09 09:51

ALTER PROCEDURE [dbo].[pt_WFGetNavigationWithGroup]

@language varchar(20) = 'zh-cn',
@pagesize int,--页码大小
@pageno int --当前页
AS

declare @@Nav table(
   ID int IDENTITY(1,1),
   NodeCode varchar(50) Not Null,
   Name varchar(120) Not Null,
   URL varchar(500) null,
   IsSystem int,
   GroupName varchar(4000)
)

if (@language='zh-cn')   -------简体中文
   begin
       Insert into @@Nav(NodeCode, Name, URL, IsSystem)
       select NodeCode, Name, URL, IsSystem from NavigationBar
   end
else if(@language='zh-tw')   -------繁体中文
   begin
       Insert into @@Nav(NodeCode, Name, URL, IsSystem)
       select NodeCode, Name, URL, IsSystem from NavigationBarTW
   end
else if(@language='en-us')   -------英文
   begin
       Insert into @@Nav(NodeCode, Name, URL, IsSystem)
       select NodeCode, Name, URL, IsSystem from NavigationBarUK
   end
else if(@language='ja')   -------日文
   begin
       Insert into @@Nav(NodeCode, Name, URL, IsSystem)
       select NodeCode, Name, URL, IsSystem from NavigationBarJA
   end

declare @sumcount int
declare @nodecode varchar(50)
declare @groupname varchar(4000)
declare @sql varchar(4000)
------select top 40 * from NavigationBar where nodecode not in (select top( (2-1)*40) nodecode from NavigationBar)
select @sumcount = count(*) from @@Nav
----第一页
if @pageno<0
begin
    set @pageno=1
    set @nodecode='select top '+CONVERT(varchar,@pagesize)+' '+@nodecode+' from @@Nav where NodeCode not in (select top
             '+CONVERT(varchar,(@pageno-1)*@pagesize)+' NodeCode from @@Nav)'
print(@sql)
exec(@sql)
end