请给个带OUTPUT的存储过程的说明问档,谢谢
请给个带OUTPUT的存储过程的说明问档,谢谢
[解决办法]
-------------------在线帮助的例子-------------------
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor ' and type = 'P ')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles
OPEN @titles_cursor
GO
Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.
USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
[解决办法]
--简单存储过程如下:
----------------------------------------------------
CREATE PROC P_TEST
@Name VARCHAR(20),
@Rowcount INT OUTPUT
AS
BEGIN
SELECT * FROM T_Customer WHERE NAME=@Name
SET @Rowcount=@@ROWCOUNT
END
GO
----------------------------------------------------
--存储过程调用如下:
----------------------------------------------------
DECLARE @i INT
EXEC P_TEST 'A ',@i OUTPUT
SELECT @i
--结果
/*
Name Address Tel
---------- ---------- --------------------
A Address Telphone
（所影响的行数为 1 行）
-----------
1
（所影响的行数为 1 行）
*/
[解决办法]
declare @return int
declare @p1 int
declare @p2 int
exec @return=存储过程名 @p1,@p2 output
select @return as 返回值,@p2 as 输出参数值
