分页存储过程的问题-->这次80全散了 兄弟门也速战速决吧!!
错误:对象名 '@TableName ' 无效。
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。
异常详细信息: System.Data.SqlClient.SqlException: 对象名 '@TableName ' 无效。
------------------------------
存储过程:
CREATE PROCEDURE dbo.Page_Two
@TableName varchar(20),
@KeyName varchar(20),
@PageIndex int,
@PageSize int,
@TotalRecords int output
AS
BEGIN
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * (@PageIndex-1)
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsres
(
IndexId int IDENTITY (1, 1) NOT NULL primary key,
UsreId int
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsres (UsreId)
SELECT [KeyName] from [@TableName]
SELECT @TotalRecords = ceiling(@@ROWCOUNT/1.00/@PageSize)
SELECT u.name,u.age
FROM @TableName as u,#PageIndexForUsres as p
WHERE u.[KeyName] = p.UsreId AND
p.IndexId > = @PageLowerBound AND p.IndexId <= @PageUpperBound
RETURN @TotalRecords
END
-----------------------------
Code:
public partial class _Default : System.Web.UI.Page
{
public int pageIndex = 3;
public int pageSize = 10;
public int TotalRecords;//页数
public string tableName = "UsreInfo ";//表名
public string columnName = "uid ";//关联列
protected void Page_Load(object sender, EventArgs e)
{
this.BandGridView();
}
private void BandGridView()
{
this.GridView1.DataSource = CreateCon();
this.GridView1.DataBind();
}
private DataTable CreateCon()
{
SqlConnection sqlCon = new SqlConnection( "server=.;database=test;uid=sa;pwd=; ");
SqlDataAdapter sda = new SqlDataAdapter( "Page_Two ",sqlCon);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par1 = new SqlParameter( "@TableName ",SqlDbType.VarChar);
par1.Value = tableName;
sda.SelectCommand.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter( "@KeyName ",SqlDbType.VarChar);
par2.Value = columnName;
sda.SelectCommand.Parameters.Add(par2);
SqlParameter par3 = new SqlParameter( "@PageIndex ", SqlDbType.Int);
par3.Value = pageIndex;
sda.SelectCommand.Parameters.Add(par3);
SqlParameter par4 = new SqlParameter( "@PageSize ", SqlDbType.Int);
par4.Value = pageSize;
sda.SelectCommand.Parameters.Add(par4);
SqlParameter par5 = new SqlParameter( "@TotalRecords ",SqlDbType.Int);
par5.Direction = ParameterDirection.Output;
sda.SelectCommand.Parameters.Add(par5);
DataSet ds = new DataSet();
sda.Fill(ds);
TotalRecords = Convert.ToInt32(par5.Value.ToString());
return ds.Tables[0];
}
}
--------------------------
请大家帮个忙
[解决办法]
SELECT u.name,u.age
FROM @TableName as u,#PageIndexForUsres as p
WHERE u.[KeyName] = p.UsreId AND
p.IndexId > = @PageLowerBound AND p.IndexId <= @PageUpperBound
---------
要用动态SQL
DECLARE @sql varchar(2000)
set @sql = 'SELECT u.name,u.age from '
set @sql = @sql + @TableName
set @sql = @sql + ' as u,#PageIndexForUsres as p WHERE u.[KeyName] = p.UsreId AND
p.IndexId > = '
set @sql = @sql + @PageLowerBound
set @sql = @sql + ' AND p.IndexId <= '
set @sql = @sql + @PageUpperBound
exec(@sql)
