A. Use EXECUTE to pass a single parameter
The showind stored procedure expects one parameter (@tabname), a table name. The following examples
execute the showind stored procedure with titles as its parameter value.
Note The showind stored procedure is shown for illustrative purposes only and does not exist in the pubs
database.
EXEC showind titles The variable can be explicitly named in the execution:
EXEC showind @tabname = titles If this is the first statement in a batch or an isql script, EXEC is not required:
showind titles -Or-
showind @tabname = titles B. Use multiple parameters and an output parameter
This example executes the roy_check stored procedure, which passes three parameters. The third parameter,
@pc, is an OUTPUT parameter. After the procedure has been executed, the return value is available in the
variable @percent.
Note The roy_check stored procedure is shown for illustrative purposes only and does not exist in the
pubs database.
DECLARE @percent int EXECUTE roy_check 'BU1032', 1050, @pc = @percent OUTPUT SET Percent = @percent C. Use EXECUTE 'tsql_string' with a variable
This example shows how EXECUTE handles dynamically built strings containing variables. This example
creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).
Note This example is shown for illustrative purposes only.
DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tables_cursor DECLARE @tablename sysname FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN /* A @@FETCH_STATUS of -2 means that the row has been deleted. There is no need to test for this because this loop drops all user-defined tables. */. EXEC ('DROP TABLE ' + @tablename) FETCH NEXT FROM tables_cursor INTO @tablename END PRINT 'All user-defined tables have been dropped from the database.' DEALLOCATE tables_cursor D. Use EXECUTE with a remote stored procedure
This example executes the checkcontract stored procedure on the remote server SQLSERVER1 and stores
the return status indicating success or failure in @retstat.
DECLARE @retstat int EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract '409-56-4008' E. Use EXECUTE with an extended stored procedure
This example uses the xp_cmdshell extended stored procedure to list a directory of all files with an .exe file
name extension.
USE master EXECUTE xp_cmdshell 'dir *.exe' F. Use EXECUTE with a stored procedure variable
This example creates a variable that represents a stored procedure name.
DECLARE @proc_name varchar(30) SET @proc_name = 'sp_who' EXEC @proc_name G. Use EXECUTE with DEFAULT
This example creates a stored procedure with default values for the first and third parameters. When the
procedure is run, these defaults are inserted for the first and third parameters if no value is passed in the
call or if the default is specified. Note the various ways the DEFAULT keyword can be used.
USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc_calculate_taxes' AND type = 'P') DROP PROCEDURE proc_calculate_taxes GO -- Create the stored procedure. CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42,
@p2 char(1), @p3 varchar(8) = 'CAR') AS SELECT * FROM mytable The proc_calculate_taxes stored procedure can be executed in many combinations:
EXECUTE proc_calculate_taxes @p2 = 'A' EXECUTE proc_calculate_taxes 69, 'B' EXECUTE proc_calculate_taxes 69, 'C', 'House' EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D' EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E' EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT EXECUTE proc_calculate_taxes 95, 'G', DEFAULT EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT A Simple Example to run a parameterized constructor
No comments :
Post a Comment