Use Pubs
Demo 1
/* Simple Stored PROCEDURE */
CREATE PROCEDURE prcGetAuthors
AS
BEGIN
PRINT 'List of Authors'
SELECT * FROM authors
END
-- To EXECute the Stored PROCEDURE
EXEC prcGetAuthors
-- To Know the Contents of the Stored PROCEDURE
Sp_helptext prcGetAuthors
DEMO 2
/* Stored PROCEDURE with INput Parameter */
SELECT * FROM authors
CREATE PROCEDURE prcGetAuthorDetails(@au_id varchar(11))
AS
BEGIN
PRINT 'Details of Author : ' + @au_id
SELECT * FROM authors WHERE au_id = @au_id
END
/* To EXECute the PROCEDURE */
EXEC prcGetAuthorDetails '172-32-1176'
DEMO 3
/* Stored PROCEDURE with input and outPut Parameter */
CREATE PROCEDURE prcGetAuthorNames
(@au_id varchar(11),@au_fname varchar(20) output,@au_lname varchar(40) output)
AS
BEGIN
PRINT 'Details of Author : ' + @au_id
SELECT @au_fname = au_fname , @au_lname = au_lname
FROM authors
WHERE au_id = @au_id
END
/* To EXECute the Stored PROCEDURE */
DECLARE @afname varchar(20), @alname varchar(40)
EXEC prcGetAuthorNames '172-32-1176' , @afname output , @alname output
SELECT @afname , @alname
Demo 4
/* To CREATE a PROCEDURE that will call the Demo 3 and display the Details */
CREATE PROCEDURE prcDisplayAuthorDetails(@au_id varchar(11))
AS
BEGIN
Declare @afname varchar(20)
Declare @alname varchar(40)
EXEC prcGetAuthorNames @au_id , @afname OUTput , @alname OUTput
PRINT 'The Author First Name is : ' + @afname
PRINT 'The Author LASt Name is : ' + @alname
END
/* To EXECute the Stored PROCEDURE */
EXEC prcDisplayAuthorDetails '172-32-1176'
Demo 5
/* NotIFyINg Successful EXECution of a PROCEDURE usINg RETURN Value */
CREATE PROCEDURE prcAuthorStatus(@au_id varchar(11))
AS
BEGIN
IF Exists (SELECT * FROM authors WHERE au_id = @au_id)
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 1
END
END
/* To EXECute the Stored PROCEDURE */
DECLARE @ret int
EXEC @ret = prcAuthorStatus '172-32-1176'
SELECT @ret
/* Call PROCEDURE WithIN a PROCEDURE */
CREATE PROCEDURE prcCallAuthors(@au_id varchar(11))
AS
BEGIN
Declare @afname varchar(20)
Declare @alname varchar(40)
Declare @ret INt
EXEC @ret = prcAuthorStatus @au_id
IF @ret = 0
BEGIN
EXEC prcGetAuthorNames @au_id , @afname OUTput , @alname OUTput
PRINT 'The Author First Name is : ' + @afname
PRINT 'The Author LASt Name is : ' + @alname
END
ELSE
BEGIN
PRINT ' No Records Found '
END
END
/* To EXECute the Stored PROCEDURE */
EXEC prcCallAuthors '172-32-1176'
/* Stored PROCEDUREs with Default Values */
CREATE PROCEDURE prcDefValues(@p1 INt = null, @p2 INt = 10 , @p3 INt = 20)
AS
BEGIN
SELECT @p1 , @p2 , @p3
END
/* To EXECute the PROCEDURE */
EXEC prcDefValues
EXEC prcDefValues 1,2,3
EXEC prcDefValues @p2 = 2 , @p1 = 1 , @p3 = 3
EXEC prcDefValues 10,Default,20
EXEC prcDefValues 10,null,default
/* To Drop a Strored PROCEDURE */
Drop PROCEDURE ProcName
Drop proc prcCallAuthors
0 التعليقات:
إرسال تعليق