下面代码是自己写的,请勿见笑CREATE PROCEDURE show_swf
@current bigint,
@count int output
AS
declare @minid bigint
declare @sqls nvarchar(1000)
set @sqls='select @minid=min(id) from swf where id in(select top '+@current*20+' id from swf order by id desc)'
exec sp_executesql @sqls,N'@minid bigint output' --我想从这里返回最小ID,但老报“将数据类型 varchar 转换为 bigint 时出错”,为什么?
select top 20 [id],[name],intro,image,path from swf where id not in (select id from swf where id>=@minid ) order by id desc
select @count=count(*) from swf
GO
@current bigint,
@count int output
AS
declare @minid bigint
declare @sqls nvarchar(1000)
set @sqls='select @minid=min(id) from swf where id in(select top '+@current*20+' id from swf order by id desc)'
exec sp_executesql @sqls,N'@minid bigint output' --我想从这里返回最小ID,但老报“将数据类型 varchar 转换为 bigint 时出错”,为什么?
select top 20 [id],[name],intro,image,path from swf where id not in (select id from swf where id>=@minid ) order by id desc
select @count=count(*) from swf
GO
怎么都喜欢到c#来问sqlserver的问题
id字段也是bigint类型,跟@minid类型一样,为什么要强制转化?
--------------------------------------用途:分页存储过程(对有主键的表效率极高) ------------------------------------
CREATE PROCEDURE pro_GetRecordByPage
@TableName VARCHAR(255), -- 表名
@KFieldName VARCHAR(255), -- 主键字段名
@FieldName VARCHAR(1000)='*', --需要返回的列
@PageSize INT =10, -- 页尺寸
@PageIndex INT=1 , -- 页码
@OrderType BIT =0, -- 设置排序类型, 非 0 值则降序
@StrWhere VARCHAR(1000)='' , -- 查询条件 (注意: 不要加 where)
@TotalRecord INT OUT, --返回总记录数
@TotalPage INT OUT --返回总页数
AS
DECLARE @StrSQL VARCHAR(6000) -- 主语句
DECLARE @StrTmp VARCHAR(100) -- 临时变量
DECLARE @StrOrder VARCHAR(400) -- 排序类型
DECLARE @SQL NVARCHAR(1000) --统计语句
IF @StrWhere = ''
BEGIN
SET @SQL = 'SELECT @TotalRecord=COUNT(*) FROM [' + @TableName + ']'
END
ELSE
BEGIN
SET @SQL = 'SELECT @TotalRecord=COUNT(*) FROM [' + @TableName + ']' + ' WHERE ' + @StrWhere
END
EXEC SP_EXECUTESQL @SQL, N'@TotalRecord INT OUTPUT',@TotalRecord OUTPUT --计算总记录数
SET @TotalPage =CEILING( CAST(@TotalRecord AS FLOAT)/CAST(@PageSize AS FLOAT) )--计算总页数
--判断排序类型
IF @OrderType != 0
BEGIN
SET @StrTmp = '<(SELECT MIN'
SET @StrOrder = ' ORDER BY [' + @KFieldName +'] DESC'
END
ELSE
BEGIN
SET @StrTmp = '>(SELECT MAX'
SET @StrOrder = ' ORDER BY [' + @KFieldName +'] ASC'
END
SET @StrSQL = 'SELECT TOP ' + str(@PageSize) +' '+@FieldName+ ' FROM ['
+ @TableName + '] WHERE [' + @KFieldName + ']' + @StrTmp + '(['
+ @KFieldName + ']) FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @KFieldName + '] FROM [' + @TableName + ']' + @StrOrder + ') AS tblTmp)'
+ @StrOrder
--判断是否有条件
IF @StrWhere != ''
SET @StrSQL = 'SELECT TOP ' + str(@PageSize) +' '+@FieldName+ ' FROM ['
+ @TableName + '] WHERE [' + @KFieldName + ']' + @StrTmp + '(['
+ @KFieldName + ']) FROM (SELECT TOP ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @KFieldName + '] FROM [' + @TableName + '] WHERE ' + @StrWhere + ' '
+ @StrOrder + ') AS tblTmp) AND ' + @StrWhere + ' ' + @StrOrder
IF @PageIndex = 1
BEGIN
SET @StrTmp =''
IF @StrWhere != ''
SET @StrTmp = ' WHERE ' + @StrWhere
SET @StrSQL = 'SELECT TOP ' + str(@PageSize) +' '+ @FieldName+ ' FROM ['
+ @TableName + ']' + @StrTmp + ' ' + @StrOrder
END
EXEC (@StrSQL)GO