自己写的一个分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER procedure [dbo].[proc_MutipleShowPage]
@tblName varchar(2000),
@strFileds varchar(1000)='*',
@orderFiled varchar(500)='',
@orderType varchar(10)='asc',
@pagesize int=10,
@pageIndex int=1
as
begin
    declare @strSQL varchar(5000)
    set @strSQL='with temptbl as(
    select row_number() over(order by '+@orderFiled+' '+@orderType+') as row 
    ,'+ @strFileds+' from '+@tblName+')'+'select * from temptbl where row between '
    +str((@pageIndex-1)*@pagesize+1)+' and '+str(@pageIndex*@pagesize)
    execute(@strSQL)
    print @strSQL
end

我的疑惑假设如下两张表:

 

USE [jhebp]
GO
/****** 对象:  Table [dbo].[Y_AirQuality]    脚本日期: 01/18/2010 15:43:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Y_AirQuality](
    [Air_ID] [int] IDENTITY(1,1) NOT NULL,
    [Air_Type] [int] NULL,
    [Air_date] [datetime] NULL,
    [Air_API] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Air_Top] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Air_grade] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Air_Status] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Air_color] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Air_publish] [datetime] NULL,
    [Air_author] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Oper_code] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Oper_date] [datetime] NULL,
    [CodeId] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [CodeType] [nvarchar](5) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK__Y_AirQuality__0F975522] PRIMARY KEY CLUSTERED 
(
    [Air_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [jhebp]
GO
ALTER TABLE [dbo].[Y_AirQuality]  WITH CHECK ADD  CONSTRAINT [FK_airquality_sysCode] FOREIGN KEY([CodeId], [CodeType])
REFERENCES [dbo].[Y_SysCode] ([CodeId], [CodeType])

USE [jhebp]
GO
/****** 对象:  Table [dbo].[Y_SysCode]    脚本日期: 01/18/2010 15:44:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Y_SysCode](
    [CodeId] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [CodeType] [nvarchar](5) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [CodeName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [CodeDes] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_SysCode] PRIMARY KEY CLUSTERED 
(
    [CodeId] ASC,
    [CodeType] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 

 

为什么我为@strFileds的参数设置为*就会调用错误呢?如下:

 

exec proc_MutipleShowPage 'Y_AirQuality air inner join Y_SysCode code on air.codeId=code.codeId and air.codeType=code.codeType',
'*','air_date','asc',10,1

 

错误:

消息 8156，级别 16，状态 1，第 1 行
多次为 'temptbl' 指定了列 'CodeId'。

如果调用方式是指明了列就没问题:

 

exec proc_MutipleShowPage 'Y_AirQuality air inner join Y_SysCode code on air.codeId=code.codeId and air.codeType=code.codeType',
'air.*,code.codename','air_date','asc',10,1

 

 

也就是为什么不能为CodeId多次指定列?

 

可是:select * from Y_AirQuality air inner join y_SysCode code
on air.codeId=code.codeId and air.codeType=code.codeType

 

好疑惑哇。。。！！！！大伙帮忙解答~~~~