QOD 8-4-2003 Disagree with answer...

Question

Post reply

QOD 8-4-2003 Disagree with answer...

sbdragoo

SSC Enthusiast

Points: 167
More actions
August 4, 2003 at 8:06 am

#106311

I don't agree with the answere or explanation given for the QOD today regarding data usable data for parameters in stored procedures. After answering the question (incorrectly) I went back to books on line and reviewed the topic given in the explanation and found a paragraph which differs distinctly from the one given. What I found under the topic of CREATE PROCEDURE is:

@parameter

Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined). A stored procedure can have a maximum of 2,100 parameters.

Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.

data_type

Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

So I present this for review by my peers for an explanation as to why the differing text. It seems as though the answer were not correct to choose from. I chose the "cursor" type since it was limited only to output type thus could not be used to directly call the procedure.

Any comments / discussion are welcome.

Thanks in advanced,

Scott B Dragoo

GIS Systems Analyst

Sangamon County, Illinois

scottd@co.sangamon.il.us

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Scott,

first let me say, I was also wrong!

I, too, had to look up BOL, but I've found another page stating this:

quote:

Parameters in a stored procedure are defined with a data type, much as a column in a table is defined. A stored procedure parameter can be defined with any of the Microsoft® SQL Server™ 2000 data types, except the table data type . Stored procedure parameters can also be defined with user-defined data types.

Note The cursor data type can be used only as an OUTPUT parameter to a stored procedure. For more information about using cursor variables, see Scope of Transact-SQL Cursor Names.

The data type of a parameter determines the type and range of values that are accepted for the parameter. For example, if you define a parameter with a tinyint data type, only numeric values ranging from 0 to 255 are accepted. An error is returned if a stored procedure is executed with a value incompatible with the data type.

I was only reading 'The cursor data type can be used only as an OUTPUT parameter to a stored procedure', and gotcha !

Should have read some lines above 'A stored procedure parameter can be defined with any of the Microsoft® SQL Server™ 2000 data types, except the table data type'

Cheers,

Frank

Edited by - a5xo3z1 on 08/04/2003 08:17:33 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bozo7 Ten Centuries Points: 1278 More actions · Answer 2

bozo7

Ten Centuries

Points: 1278

August 4, 2003 at 8:25 am

#468445

Frank,

Do you recall which page you found that section from BOL on? I can't find anything that references parameters like that. I can only find the section that Scott showed.

Is this the difference between the BOL update with SP3 and the BOL before SP3?

Ross

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:

Do you recall which page you found that section from BOL on? I can't find anything that references parameters like that. I can only find the section that Scott showed.

yes, goto stored procedures -> parameters -> Specifying a Data Type (in the pop-up)

quote:

Is this the difference between the BOL update with SP3 and the BOL before SP3?

I don't think so. The usual SP3 hint 'New Information blablabla' is missing

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sbdragoo SSC Enthusiast Points: 167 More actions · Answer 4

Frank,

I followed your directions to the page you quoted by going to the Index Tab of BOL and going to stroed procedures --> parameters --> and the specifying a Data Type in the popup and here is what my BOL quotes:

Specifying a Data Type

Parameters in a stored procedure are defined with a data type, much as a column in a table is defined. A stored procedure parameter can be defined with any of the Microsoft® SQL Server™ 2000 data types, including text and image. Stored procedure parameters can also be defined with user-defined data types.

Note The cursor data type can be used only as an OUTPUT parameter to a stored procedure. For more information about using cursor variables, see Scope of Transact-SQL Cursor Names.

The data type of a parameter determines the type and range of values that are accepted for the parameter. For example, if you define a parameter with a tinyint data type, only numeric values ranging from 0 to 255 are accepted. An error is returned if a stored procedure is executed with a value incompatible with the data type.

See Also

Creating User-Defined Data Types

CREATE PROCEDURE

Data Types"

Agreeing with what your BOL quotes..

Not sure which is actually correct at this point in time. I'm a bit pressed for time to actually create a procedure to test the TABLE type with but will attempt to find some time later today to give a go.

Anyone else finding this conflicting information in the BOL / MSDN Library data??

Scott

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Scott,

I guess that is one of those things I must not understand.

I've downloaded my SP3 (a?) BOL update from M$ website.

Now, where can I definitely see which BOL version I'm running?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sbdragoo SSC Enthusiast Points: 167 More actions · Answer 6

sbdragoo

SSC Enthusiast

Points: 167

August 4, 2003 at 8:55 am

#468451

Frank,

I tried to find what version of BOL I am running here and was unable to do so.. If you can find out how - I will determine my installed version also for comparison.

Scott

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Ok looks to me like you may have tripped over the version. SQL Server 2000 as the text posted looks like from SQL Server 7 BOL. As for how to tell when you launch make sure the title page when open states "Microsoft SQL Server 2000" also if is one of the SP BOLs it will say "SQL Server Books Online (Updated)" in the title bar of the window.

I did try to make sure I cover all my bases when I posted this QOD to be purposefully tricky but accurate.

Here is SQL 2000

quote:

Transact-SQL Reference

CREATE PROCEDURE

New Information - September 2001.

Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Procedures can be created for permanent use or for temporary use within a session (local temporary procedure) or for temporary use within all sessions (global temporary procedure).

Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.

Syntax

CREATE PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

Arguments

procedure_name

Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner. For more information, see Using Identifiers.

Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.

;number

Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

@parameter

Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined or the value is set to equal another parameter). A stored procedure can have a maximum of 2,100 parameters.

Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.

data_type

Is the parameter data type. All data types, except the table data type, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

Note There is no limit on the maximum number of output parameters that can be of cursor data type.

VARYING

Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and ) if the procedure uses the parameter with the LIKE keyword.

OUTPUT

Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.

n

Is a placeholder indicating that a maximum of 2,100 parameters can be specified.

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory.

ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.

Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.

FOR REPLICATION

Specifies that stored procedures created for replication cannot be executed on the Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during replication. This option cannot be used with the WITH RECOMPILE option.

AS

Specifies the actions the procedure is to take.

sql_statement

Is any number and type of Transact-SQL statements to be included in the procedure. Some limitations apply.

n

Is a placeholder that indicates multiple Transact-SQL statements may be included in this procedure.

Remarks

The maximum size of a stored procedure is 128 MB.

A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb). The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error. To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.

It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behaviors. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created with the same nullability for all connections that execute the stored procedure.

SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.

Note Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.

Getting Information About Stored Procedures

To display the text used to create the procedure, execute sp_helptext in the database in which the procedure exists with the procedure name as the parameter.

Note Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.

For a report on the objects referenced by a procedure, use sp_depends.

To rename a procedure, use sp_rename.

Referencing Objects

SQL Server allows the creation of stored procedures that reference tables that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when executed if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references tables that do not exist can be created successfully; however, the store procedure will fail at run time if the referenced tables do not exist. For more information, see Deferred Name Resolution and Compilation.

Deferred Name Resolution and Compatibility Level

SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. An error message is returned at run time if the table or column referenced does not exist. If the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.

Executing Stored Procedures

When a CREATE PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.

Parameters Using the cursor Data Type

Stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.

Note The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a stored procedure, stored procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, stored procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

Cursor Output Parameters

The following rules pertain to cursor output parameters when the procedure is executed:

For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:

A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

The procedure fetches the first 5 rows of result set RS.

The procedure returns to its caller.

The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When returned, the cursor position is set before the first row.

For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

Note An empty result set is not the same as a null value.

For a scrollable cursor, all the rows in the result set are returned to the calling batch, stored procedure, or trigger at the conclusion of the execution of the stored procedure. When returned, the cursor position is left at the position of the last fetch executed in the procedure.

For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

Note The closed state matters only at return time. For example, it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, stored procedure, or trigger.

Temporary Stored Procedures

SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends.

Temporary procedures named with # and ## can be created by any user. When the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted for other users. If a global temporary procedure is created, all users can access it; permissions cannot be revoked explicitly. Explicitly creating a temporary procedure in tempdb (naming without a number sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted and revoked from these procedures.

Note Heavy use of temporary stored procedures can create contention on the system tables in tempdb and adversely affect performance. It is recommended that sp_executesql be used instead. sp_executesql does not store data in the system tables and therefore avoids the problem.

Automatically Executing Stored Procedures

One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.

Execution of the stored procedures starts when the last database is recovered at startup. To skip launching these stored procedures, specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see Trace Flags.

To create a startup stored procedure, you must be logged in as a member of the sysadmin fixed server role and create the stored procedure in the master database.

Use sp_procoption to:

Designate an existing stored procedure as a startup procedure.

Stop a procedure from executing at SQL Server startup.

Stored Procedure Nesting

Stored procedures can be nested; that is one stored procedure calling another. The nesting level is incremented when the called procedure starts execution, and decremented when the called procedure finishes execution. Exceeding the maximum levels of nesting causes the whole calling procedure chain to fail. The current nesting level is returned by the @@NESTLEVEL function.

To estimate the size of a compiled stored procedure, use these Performance Monitor Counters.

Performance Monitor object name Performance Monitor Counter name

SQLServer: Buffer Manager Procedure Cache Pages

SQLServer: Cache Manager Cache Hit Ratio

Cache Pages

Cache Object Counts*

* These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on.

For more information, see SQL Server: Buffer Manager Object and SQL Server: Cache Manager Object.

sql_statement Limitations

Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch. The SET option chosen remains in effect during the execution of the stored procedure and then reverts to its former setting.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:

ALTER TABLE

CREATE INDEX

CREATE TABLE

All DBCC statements

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS

Permissions

CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.

Examples

A. Use a simple procedure with a complex SELECT

This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type = 'P')

DROP PROCEDURE au_info_all

GO

CREATE PROCEDURE au_info_all

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

GO

The au_info_all stored procedure can be executed in these ways:

EXECUTE au_info_all

-- Or

EXEC au_info_all

Or, if this procedure is the first statement within the batch:

au_info_all

B. Use a simple procedure with parameters

This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info' AND type = 'P')

DROP PROCEDURE au_info

GO

USE pubs

GO

CREATE PROCEDURE au_info

@lastname varchar(40),

@firstname varchar(20)

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname = @firstname

AND au_lname = @lastname

GO

The au_info stored procedure can be executed in these ways:

EXECUTE au_info 'Dull', 'Ann'

-- Or

EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'

-- Or

EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'

-- Or

EXEC au_info 'Dull', 'Ann'

-- Or

EXEC au_info @lastname = 'Dull', @firstname = 'Ann'

-- Or

EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

Or, if this procedure is the first statement within the batch:

au_info 'Dull', 'Ann'

-- Or

au_info @lastname = 'Dull', @firstname = 'Ann'

-- Or

au_info @firstname = 'Ann', @lastname = 'Dull'

C. Use a simple procedure with wildcard parameters

This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset defaults.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info2' AND type = 'P')

DROP PROCEDURE au_info2

GO

USE pubs

GO

CREATE PROCEDURE au_info2

@lastname varchar(30) = 'D%',

@firstname varchar(18) = '%'

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname LIKE @firstname

AND au_lname LIKE @lastname

GO

The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:

EXECUTE au_info2

-- Or

EXECUTE au_info2 'Wh%'

-- Or

EXECUTE au_info2 @firstname = 'A%'

-- Or

EXECUTE au_info2 '[CK]ars[OE]n'

-- Or

EXECUTE au_info2 'Hunter', 'Sheryl'

-- Or

EXECUTE au_info2 'H%', 'S%'

D. Use OUTPUT parameters

OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.

First, create the procedure:

USE pubs

GO

IF EXISTS(SELECT name FROM sysobjects

WHERE name = 'titles_sum' AND type = 'P')

DROP PROCEDURE titles_sum

GO

USE pubs

GO

CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT

AS

SELECT 'Title Name' = title

FROM titles

WHERE title LIKE @TITLE

SELECT @SUM = SUM(price)

FROM titles

WHERE title LIKE @TITLE

GO

Next, use the OUTPUT parameter with control-of-flow language.

Note The OUTPUT variable must be defined during the table creation as well as during use of the variable.

The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @SUM = variable is used).

DECLARE @TOTALCOST money

EXECUTE titles_sum 'The%', @TOTALCOST OUTPUT

IF @TOTALCOST < 200

BEGIN

PRINT ' '

PRINT 'All of these titles can be purchased for less than $200.'

END

ELSE

SELECT 'The total cost of these titles is $'

+ RTRIM(CAST(@TOTALCOST AS varchar(20)))

Here is the result set:

Title Name

------------------------------------------------------------------------

The Busy Executive's Database Guide

The Gourmet Microwave

The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.

All of these titles can be purchased for less than $200.

E. Use an OUTPUT cursor parameter

OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.

First, create the procedure that declares and then opens a cursor on the titles table:

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

F. Use the WITH RECOMPILE option

The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical, and when a new execution plan should not be cached or stored in memory.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'titles_by_author' AND type = 'P')

DROP PROCEDURE titles_by_author

GO

CREATE PROCEDURE titles_by_author @LNAME_PATTERN varchar(30) = '%'

WITH RECOMPILE

AS

SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',

title AS Title

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON ta.title_id = t.title_id

WHERE au_lname LIKE @LNAME_PATTERN

GO

G. Use the WITH ENCRYPTION option

The WITH ENCRYPTION clause hides the text of a stored procedure from users. This example creates an encrypted procedure, uses the sp_helptext system stored procedure to get information on that encrypted procedure, and then attempts to get information on that procedure directly from the syscomments table.

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'encrypt_this' AND type = 'P')

DROP PROCEDURE encrypt_this

GO

USE pubs

GO

CREATE PROCEDURE encrypt_this

WITH ENCRYPTION

AS

SELECT *

FROM authors

GO

EXEC sp_helptext encrypt_this

Here is the result set:

The object's comments have been encrypted.

Next, select the identification number and text of the encrypted stored procedure contents.

SELECT c.id, c.text

FROM syscomments c INNER JOIN sysobjects o

ON c.id = o.id

WHERE o.name = 'encrypt_this'

Here is the result set:

Note The text column output is shown on a separate line. When executed, this information appears on the same line as the id column information.

id text

---------- ------------------------------------------------------------

1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????

(1 row(s) affected)

H. Create a user-defined system stored procedure

This example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'sp_showindexes' AND type = 'P')

DROP PROCEDURE sp_showindexes

GO

USE master

GO

CREATE PROCEDURE sp_showindexes

@TABLE varchar(30) = 'sys%'

AS

SELECT o.name AS TABLE_NAME,

i.name AS INDEX_NAME,

indid AS INDEX_ID

FROM sysindexes i INNER JOIN sysobjects o

ON o.id = i.id

WHERE o.name LIKE @TABLE

GO

USE pubs

EXEC sp_showindexes 'emp%'

GO

Here is the result set:

TABLE_NAME INDEX_NAME INDEX_ID

---------------- ---------------- ----------------

employee employee_ind 1

employee PK_emp_id 2

(2 row(s) affected)

I. Use deferred name resolution

This example shows a procedure that uses deferred name resolution. The stored procedure is created although the table that is referenced does not exist at compile time. The table must exist, however, at the time the procedure is executed.

USE master

GO

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'proc1' AND type = 'P')

DROP PROCEDURE proc1

GO

-- Create a procedure on a nonexistent table.

USE pubs

GO

CREATE PROCEDURE proc1

AS

SELECT *

FROM does_not_exist

GO

-- This statement returns the text of the procedure proc1.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c

ON o.id = c.id

WHERE o.type = 'P' AND o.name = 'proc1'

GO

See Also

ALTER PROCEDURE

Batches

Control-of-Flow Language

Cursors

DBCC

DECLARE @local_variable

DROP PROCEDURE

Functions

GRANT

Programming Stored Procedures

SELECT

sp_addextendedproc

sp_depends

sp_helptext

sp_procoption

sp_recompile

sp_rename

System Tables

Using Comments

Using Variables and Parameters

©1988-2001 Microsoft Corporation. All Rights Reserved.

And SQL 7

quote:

CREATE PROCEDURE (T-SQL)

Creates a stored procedure (a saved collection of Transact-SQL statements) that can take and return user-supplied parameters.

Procedures can be created for permanent use or for temporary use within a user’s session (local temporary procedure) or for temporary use within all user’s sessions (global temporary procedure).

Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.

Syntax

CREATE PROC[EDURE] procedure_name [;number]

[

{@parameter data_type} [VARYING] [= default] [OUTPUT]

]

[,...n]

[WITH

{

RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION

}

]

[FOR REPLICATION]

AS

sql_statement [...n]

Arguments

procedure_name

Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner. For more information, see Using Identifiers.

Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.

;number

Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

@parameter

Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter has been defined). A stored procedure can have a maximum of 1,024 parameters.

Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.

data_type

Is the data type of the parameter. All data types, including text and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

--------------------------------------------------------------------------------

Note There is no limit on the maximum number of output parameters that can be of cursor data type.

--------------------------------------------------------------------------------

VARYING

Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and ) if the procedure uses the parameter with the LIKE keyword.

OUTPUT

Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text parameters cannot be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.

n

Is a placeholder indicating that up to 1,024 parameters can be specified.

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled each time it is executed. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan that is cached in memory.

ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement.

--------------------------------------------------------------------------------

Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.

--------------------------------------------------------------------------------

FOR REPLICATION

This option is mutually exclusive of the WITH RECOMPILE option. Specifies that stored procedures created for replication cannot be executed on the subscribing server and is used when creating a filter stored procedure that is executed only by replication.

AS

Specifies the actions the procedure is to take.

sql_statement

Is any number and type of Transact-SQL statements to be included in the procedure. Some limitations do apply. For more information, see sql_statement Limitations later in this topic.

n

Is a placeholder indicating that multiple Transact-SQL statements may be included in this procedure. For more information, see sql_statement Limitations later in this topic.

Remarks

The maximum size of a stored procedure is 128 MB.

A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb). The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error. To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.

It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if they are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behaviors. If NULL or NOT NULL is explicitly stated for each column, the temporary tables will be created with the same nullability for all connections that execute the stored procedure.

SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients freedom to set the options wanted without affecting the logic of the stored procedure.

--------------------------------------------------------------------------------

Note Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.

--------------------------------------------------------------------------------

Getting Information About Stored Procedures

To display the text used to create the procedure, execute sp_helptext (in the database in which the procedure exists) with the procedure name as the parameter.

--------------------------------------------------------------------------------

Note Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.

--------------------------------------------------------------------------------

For a report on the objects referenced by a procedure, use sp_depends.

To rename a procedure, use sp_rename.

Referencing Objects

SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when it is executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at execution time because referenced objects do not exist. For more information, see Deferred Name Resolution and Compilation.

Deferred Name Resolution and Compatibility Level

SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. If, however, the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. An error message is returned at execution time if the table referenced does not exist. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.

Executing Stored Procedures

When a CREATE PROCEDURE statement is executed successfully, the procedure’s name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.

Parameters Using the cursor Data Type

Stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.

--------------------------------------------------------------------------------

Note The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a stored procedure, stored procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, stored procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

--------------------------------------------------------------------------------

Cursor Output Parameters

The following rules pertain to cursor output parameters when the procedure is executed:

For a forward-only cursor, the rows returned in the cursor’s result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:

A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

The procedure fetches the first 5 rows of result set RS.

The procedure returns to its caller.

The result set RS returned to the caller consists of rows 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When it is returned, the cursor’s position is set before the first row.

For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

--------------------------------------------------------------------------------

Note An empty result set is not the same thing as a null value.

--------------------------------------------------------------------------------

For a scrollable cursor, all the rows in the result set are returned to the calling batch, stored procedure, or trigger at the conclusion of the execution of the stored procedure. When it is returned, the cursor’s position is left at the position of the last fetch executed in the procedure.

For any type of cursor, if the cursor has been closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor has been assigned to a parameter but that cursor is never opened.

--------------------------------------------------------------------------------

Note The closed state matters only at return time. For example it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor’s result set to the calling batch, stored procedure, or trigger.

--------------------------------------------------------------------------------

Temporary Stored Procedures

SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends.

Temporary procedures named with # and ## can be created by any user. After the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted to other users. If a global temporary procedure is created, all users can access it; permissions cannot be revoked explicitly. Explicitly creating a temporary procedure in tempdb (naming without a number sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted to and revoked from these procedures.

--------------------------------------------------------------------------------

Note Heavy use of temporary stored procedures can create contention on the system tables in tempdb and adversely affect performance. It is recommended that sp_executesql be used instead. sp_executesql does not store data in the system tables and therefore avoids the problem.

--------------------------------------------------------------------------------

Automatically Executing Stored Procedures

One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but don’t need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.

Execution of the stored procedures starts when the last database has been recovered at startup. To skip launching these stored procedures, you can specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see Trace Flags.

To create a startup stored procedure, you must be logged in as a member of the sysadmin fixed server role and create the stored procedure in the master database.

Use sp_procoption to:

Make an existing stored procedure a startup procedure.

Stop a procedure from executing at SQL Server startup.

View a list of all procedures that execute at SQL Server startup.

Stored Procedure Nesting

Stored procedures can be nested (one stored procedure calling another). The nesting level is incremented when the called procedure starts execution, and it is decremented when the called procedure finishes execution. Exceeding the maximum levels of nesting causes the whole calling procedure chain to fail. The current nesting level is returned by the @@NESTLEVEL function.

To estimate the size of a compiled stored procedure, use these Performance Monitor Counters.

Performance Monitor object name Performance Monitor Counter name

SQLServer: Buffer Manager Cache Size (pages)

SQLServer: Cache Manager Cache Hit Ratio

Cache Pages

Cache Object Counts*

* These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on.

For more information, see SQL Server: Buffer Manager Object and SQL Server: Cache Manager Object.

sql_statement Limitations

Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch. The SET option chosen remains in effect during the execution of the stored procedure and then reverts to its former setting.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:

ALTER TABLE

CREATE INDEX

CREATE TABLE

All DBCC statements

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS

Permissions

CREATE PROCEDURE permissions default to the members of the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permissions to execute a stored procedure are given to the procedure owner, who can grant execution permission on it to other database users.

Examples

A. Use a simple procedure with a complex SELECT

This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type = 'P')

DROP PROCEDURE au_info_all

GO

CREATE PROCEDURE au_info_all

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

GO

The au_info_all stored procedure can be executed in these ways:

EXECUTE au_info_all

-- Or

EXEC au_info_all

Or, if this procedure is the first statement within the batch:

au_info_all

B. Use a simple procedure with parameters

This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info' AND type = 'P')

DROP PROCEDURE au_info

GO

USE pubs

GO

CREATE PROCEDURE au_info

@lastname varchar(40),

@firstname varchar(20)

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname = @firstname

AND au_lname = @lastname

GO

The au_info stored procedure can be executed in these ways:

EXECUTE au_info 'Dull', 'Ann'

-- Or

EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'

-- Or

EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'

-- Or

EXEC au_info 'Dull', 'Ann'

-- Or

EXEC au_info @lastname = 'Dull', @firstname = 'Ann'

-- Or

EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

Or, if this procedure is the first statement within the batch:

au_info 'Dull', 'Ann'

-- Or

au_info @lastname = 'Dull', @firstname = 'Ann'

-- Or

au_info @firstname = 'Ann', @lastname = 'Dull'

C. Use a simple procedure with wildcard parameters

This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset defaults.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info2' AND type = 'P')

DROP PROCEDURE au_info2

GO

USE pubs

GO

CREATE PROCEDURE au_info2

@lastname varchar(30) = 'D%',

@firstname varchar(18) = '%'

AS

SELECT au_lname, au_fname, title, pub_name

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON t.title_id = ta.title_id INNER JOIN publishers p

ON t.pub_id = p.pub_id

WHERE au_fname LIKE @firstname

AND au_lname LIKE @lastname

GO

The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:

EXECUTE au_info2

-- Or

EXECUTE au_info2 'Wh%'

-- Or

EXECUTE au_info2 @firstname = 'A%'

-- Or

EXECUTE au_info2 '[CK]ars[OE]n'

-- Or

EXECUTE au_info2 'Hunter', 'Sheryl'

-- Or

EXECUTE au_info2 'H%', 'S%'

D. Use OUTPUT parameters

OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.

First, create the procedure:

USE pubs

GO

IF EXISTS(SELECT name FROM sysobjects

WHERE name = 'titles_sum' AND type = 'P')

DROP PROCEDURE titles_sum

GO

USE pubs

GO

CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT

AS

SELECT 'Title Name' = title

FROM titles

WHERE title LIKE @@TITLE

SELECT @@SUM = SUM(price)

FROM titles

WHERE title LIKE @@TITLE

GO

Next, use the OUTPUT parameter with control-of-flow language.

--------------------------------------------------------------------------------

Note The OUTPUT variable must be defined during the table creation as well as during use of the variable.

--------------------------------------------------------------------------------

The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @@SUM = variable is used).

DECLARE @@TOTALCOST money

EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT

IF @@TOTALCOST < 200

BEGIN

PRINT ' '

PRINT 'All of these titles can be purchased for less than $200.'

END

ELSE

SELECT 'The total cost of these titles is $'

+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))

Here is the result set:

Title Name

------------------------------------------------------------------------

The Busy Executive's Database Guide

The Gourmet Microwave

The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.

All of these titles can be purchased for less than $200.

E. Use an OUTPUT cursor parameter

OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.

First, create the procedure that declares and then opens a cursor on the titles table:

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

F. Use the WITH RECOMPILE option

The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical and when a new execution plan should not be cached or stored in memory.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'titles_by_author' AND type = 'P')

DROP PROCEDURE titles_by_author

GO

CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'

WITH RECOMPILE

AS

SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',

title AS Title

FROM authors a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN titles t

ON ta.title_id = t.title_id

WHERE au_lname LIKE @@LNAME_PATTERN

GO

G. Use the WITH ENCRYPTION option

The WITH ENCRYPTION clause hides the text of a stored procedure from users. This example creates an encrypted procedure and uses the sp_helptext system stored procedure and then attempts to select directly from the syscomments table.

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'encrypt_this' AND type = 'P')

DROP PROCEDURE encrypt_this

GO

USE pubs

GO

CREATE PROCEDURE encrypt_this

WITH ENCRYPTION

AS

SELECT *

FROM authors

GO

EXEC sp_helptext encrypt_this

Here is the result set from the encrypt_this stored procedure:

The object's comments have been encrypted.

Next, select the identification number and text of the encrypted stored procedure contents.

SELECT c.id, c.text

FROM syscomments c INNER JOIN sysobjects o

ON c.id = o.id

WHERE o.name = 'encrypt_this'

Here is the result set:

--------------------------------------------------------------------------------

Note The text column output is shown on a separate line. When executed, this information appears on the same line as the id column information.

--------------------------------------------------------------------------------

id text

---------- ------------------------------------------------------------

1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????

(1 row(s) affected)

H. Create a user-defined system stored procedure

This example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'sp_showindexes' AND type = 'P')

DROP PROCEDURE sp_showindexes

GO

USE master

GO

CREATE PROCEDURE sp_showindexes

@@TABLE varchar(30) = 'sys%'

AS

SELECT o.name AS TABLE_NAME,

i.name AS INDEX_NAME,

indid AS INDEX_ID

FROM sysindexes i INNER JOIN sysobjects o

ON o.id = i.id

WHERE o.name LIKE @@TABLE

GO

USE pubs

EXEC sp_showindexes 'emp%'

GO

Here is the result set:

TABLE_NAME INDEX_NAME INDEX_ID

---------------- ---------------- ----------------

emp_pay employeeID_ind 1

employee employee_ind 1

employee PK_emp_id 2

(3 row(s) affected)

I. Use deferred name resolution

This example shows four procedures and the various ways that deferred name resolution can be used. Each stored procedure is created, although the table or column referenced does not exist at compile time.

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'proc1' AND type = 'P')

DROP PROCEDURE proc1

GO

-- Creating a procedure on a nonexistent table.

USE pubs

GO

CREATE PROCEDURE proc1

AS

SELECT *

FROM does_not_exist

GO

-- Here is the statement to actually see the text of the procedure.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c

ON o.id = c.id

WHERE o.type = 'P' AND o.name = 'proc1'

GO

USE master

GO

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'proc2' AND type = 'P')

DROP PROCEDURE proc2

GO

-- Creating a procedure that attempts to retrieve information from a

-- nonexistent column in an existing table.

USE pubs

GO

CREATE PROCEDURE proc2

AS

DECLARE @middle_init char(1)

SET @middle_init = NULL

SELECT au_id, middle_initial = @middle_init

FROM authors

GO

-- Here is the statement to actually see the text of the procedure.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c

ON o.id = c.id

WHERE o.type = 'P' and o.name = 'proc2'

See Also

ALTER PROCEDURE SELECT

Batches sp_addextendedproc

Control-of-Flow Language sp_depends

Cursors sp_helptext

DBCC sp_procoption

DECLARE @local_variable sp_recompile

DROP PROCEDURE sp_rename

Functions System Tables

GRANT Using Comments

Programming Stored Procedures Using Variables and Parameters

(c) 1988-98 Microsoft Corporation. All Rights Reserved.

Edited by - antares686 on 08/04/2003 10:06:10 AM

sbdragoo SSC Enthusiast Points: 167 More actions · Answer 8

OK much research on the question has resulted in the following...

I do have a SQL Server 2000 version of BOL installed on my machine. (SQL 2K is the only version that has been installed on this machine). I earlier posted a set of quotes from MSDN (thinking they would be the most up to date info) which contradicted each other. I just revisited the SQL server home page on micro$oft.com and discovered that the CREATE PROCEDURE page has been updated to match the text from the data type page (updated in July 2003). Possibly I had a cached page being presented to me, don't know.

Anyway, I now agree with the answer indicated and the description supplied.

It's been an interesting and informative discussion from which I learned some new information.

Looking forward to tomorrow's QOD,

Scott

amcleod SSC Journeyman Points: 91 More actions · Answer 9

amcleod

SSC Journeyman

Points: 91

August 4, 2003 at 11:07 am

#468469

Throw the question out!

Antares686 SSC Guru Points: 125444 More actions · Answer 10

Antares686

SSC Guru

Points: 125444

August 4, 2003 at 11:24 am

#468473

quote:

Throw the question out!

Why?

cholliet SSC Veteran Points: 266 More actions · Answer 11

More support from MS SQL Server BOL.

"Parameters are used to exchange data between stored procedures and the application or tool that called the stored procedure."

"Input parameters allow the caller to pass a data value to the stored procedure."

"Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller."

"Assignment operation between table variables is not supported."

So how COULD you use a table data type as a parameter in a stored procedure?

SQLBill SSC Guru Points: 51440 More actions · Answer 12

SQLBill

SSC Guru

Points: 51440

August 4, 2003 at 1:05 pm

#468516

Antares,

I think you just won for having the longest post! [big smile]

-SQLBill

Brian Knight One Orange Chip Points: 27249 More actions · Answer 13

Brian Knight

One Orange Chip

Points: 27249

August 4, 2003 at 4:01 pm

#468546

Antares, I think you just tested the limit of the text column we have to store posts :). Good discussion as always.

Brian Knight

bknight@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/bknight

Brian Knight
Free SQL Server Training Webinars

mccork SSC-Insane Points: 22288 More actions · Answer 14

mccork

SSC-Insane

Points: 22288

August 4, 2003 at 10:22 pm

#468567

I actually liked this question. Not only was it a reminder to test things that BOL is ambiguous or unclear about, but it was a prompt to me to finally download and update BOL.

Cheers,

- Mark

Cheers,
- Mark