＋设为首页 ＋添加到收藏夹
--存储过程如下是﹐其实一个分页的存储过程﹕
CREATE PROCEDURE [PR_PAGE_USER]
@L_TOTAL_PAGE INTEGER OUTPUT
,@S_TABLE_NAME VARCHAR(30)
,@S_SORT_TYPE VARCHAR(10)
,@L_PAGE_SIZE INT
,@L_PAGE_NUM INT
AS
DECLARE @SQL_USER NVARCHAR(500)
,@L_TOTAL_COL INT
IF @L_PAGE_NUM < 1 SET @L_PAGE_NUM=1
SELECT @L_TOTAL_COL=COUNT(*) FROM USER_DATA
IF FLOOR(@L_TOTAL_COL / @L_PAGE_SIZE) + 1 < @L_PAGE_NUM SET @L_PAGE_NUM = FLOOR(@L_TOTAL_COL / @L_PAGE_SIZE) + 1
SET @L_TOTAL_PAGE = FLOOR(@L_TOTAL_COL / @L_PAGE_SIZE) + 1
IF @S_SORT_TYPE = DESC
BEGIN
SET @SQL_USER=SELECT TOP + CONVERT(VARCHAR,@L_PAGE_SIZE) + * FROM + @S_TABLE_NAME
+ WHERE USER_NO < ALL(SELECT TOP + CONVERT(VARCHAR,(@L_PAGE_NUM - 1) * @L_PAGE_SIZE) + USER_NO FROM
+ @S_TABLE_NAME + ORDER BY USER_NO + @S_SORT_TYPE + ) ORDER BY USER_NO + @S_SORT_TYPE +;
END
ELSE
BEGIN
SET @SQL_USER=SELECT TOP + CONVERT(VARCHAR,@L_PAGE_SIZE) + * FROM + @S_TABLE_NAME
+ WHERE USER_NO > ALL(SELECT TOP + CONVERT(VARCHAR,(@L_PAGE_NUM - 1) * @L_PAGE_SIZE) + USER_NO FROM
+ @S_TABLE_NAME + ORDER BY USER_NO + @S_SORT_TYPE + ) ORDER BY USER_NO + @S_SORT_TYPE +;
END
EXEC SP_EXECUTESQL @SQL_USER
ASP代码如下
<!-- #include file="adovbs.inc" -->
<% set cmdsp=server.CreateObject("adodb.command")
cmdsp.ActiveConnection="provider=sqloledb.1;data source=172.24.7.66;initial catalog=letter;User ID=caller;password=caller"
cmdsp.commandtext="PR_PAGE_USER"
cmdsp.commandtype=adCmdStoredProc
cmdsp.Parameters.append cmdsp.createparameter("@L_TOTAL_PAGE",adInteger,adParamOutput,,0)
cmdsp.Parameters.append cmdsp.createparameter("@S_TABLE_NAME",adVarChar,adParamInput,30,"USER_DATA")
cmdsp.Parameters.append cmdsp.createParameter("@S_SORT_TYPE",adVarchar,adParamInput,10,"DESC")
cmdsp.Parameters.append cmdsp.createParameter("@L_PAGE_SIZE",adInteger,adParamInput,,10)
cmdsp.Parameters.append cmdsp.createParameter("@L_PAGE_NUM",adInteger,adParamInput,,2)
set rs_user=cmdsp.execute
s_return_mess=trim(cmdsp.parameters("@L_TOTAL_PAGE"))
response.write s_return_mess
if rs_user.eof then
response.write "eof"
else
response.write rs_user("user_no")
end if
set rs_user=nothing
set cmdsp.ActiveConnection=nothing
set cmdsp=nothing
%>
我如果不加入EXEC SP_EXECUTESQL @SQL_USER这句的时候﹐@S_RETURN_MESS可以顺利读出返回值﹐但我加入这句的时候就只返回空值﹐如果我把EXEC SP_EXECUTESQL @SQL_USER换成insert,update等都一样﹐请问有什幺办法可以读取返回值又可以返回记录集
另外我发现函数ceiling和floor的作用好象都是一样的﹐不知其它人有没有发现
用exec()语句,可以这样写:
exec(
declare @test varchar(200)
select @test=field from table
select @test
)
这样就会得到@test的值
还有一种办法是将值放到一个临时表中,然后再取出
获得RETURN CODE时要停止 RS_USER.CLOSE
TRY BELOW ,GOOD LUCK!!!!
set rs_user=cmdsp.execute
if rs_user.eof then
response.write "eof"
else
response.write rs_user("user_no")
end if
RS_USER.CLOSE
s_return_mess=trim(cmdsp.parameters("@L_TOTAL_PAGE"))
response.write s_return_mess
set rs_user=nothing
set cmdsp.ActiveConnection=nothing
set cmdsp=nothing
