One of the basic syntax for SQL stored procedures _mssql

CREATE PROCEDURE
Creates a stored procedure that is a collection of Transact-SQL statements that can be saved to accept and return user-supplied parameters.
You can create a procedure for permanent use, or for temporary use in a session (local temporary procedures), or for temporary use in all sessions (global temporary procedures). Can also be created at Microsoft? SQL Server? Stored procedures that run automatically at startup.

Grammar
CREATE PROC [edure] procedure_name [; number]
[{@parameter data_type}
[Varying] [= default] [OUTPUT]
] [,... N]
[With
{RECOMPILE | Encryption | RECOMPILE, encryption}]
[For REPLICATION]
As sql_statement [... n]
Parameters
Procedure_name
The name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique to the database and all of them. For more information, see Using Identifiers.
To create a local temporary procedure, you can precede procedure_name with a number character (#procedure_name), and to create a global temporary procedure, you can precede procedure_name with two number characters (# #procedure_name). The full name (including # or # #) cannot exceed 128 characters. Specifies that the name of the procedure owner is optional.
; number
is an optional integer that is used to group procedures with the same name so that a drop PROCEDURE statement can be used to remove the same group of procedures. For example, an application named orders uses a procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement drops the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, and the appropriate delimiter should be used only before and after procedure_name.
@parameter
The parameters in the procedure. You can declare one or more parameters in the CREATE PROCEDURE statement. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined). A stored procedure can have up to 2.1 parameters.
Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, parameters can only be substituted for constants and cannot be used in place of a table name, column name, or other database object name. For more information, see EXECUTE.
Data_type
The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If you specify a data type of cursor, you must also specify both varying and OUTPUT keywords. For more information about the data types and their syntax provided by SQL Server, see Data types.
Indicates that there is no maximum number of restrictions for output parameters that can be cursor data types.
Varying
Specifies the result set that is supported as output parameters (dynamically constructed by stored procedures and content can be changed). Only cursor parameters are applicable.
Default
The default value for the parameter. If you define a default value, you do not have to specify the value of the parameter to execute the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for this parameter, the default value can contain wildcard characters (%, _, [], and [^]).
OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. Output parameters that use the input keyword can be cursor placeholders.

Represents a placeholder that can specify up to 2.1 parameters.
{RECOMPILE | Encryption | RECOMPILE, encryption}
RECOMPILE indicates that SQL Server does not cache the schedule for the process, which will be recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and do not want to overwrite execution plans that are cached in memory.
Encryption represents an entry that contains the text of the CREATE PROCEDURE statement in the SQL Server encryption syscomments table. Use encryption to prevent the process from being published as part of SQL Server replication.
Description during the upgrade process, SQL Server uses the encrypted annotations stored in syscomments to recreate the encryption process.
For REPLICATION
Specifies that stored procedures created for replication cannot be performed at the Subscriber. Stored procedures created using the For REPLICATION option can be used as stored procedure filtering and can only be performed during the replication process. This option cannot be used with the WITH RECOMPILE option.
As
Specifies the action to be performed by the procedure.
Sql_statement
Any number and type of Transact-SQL statements to include in the procedure. But there are some restrictions.

is to indicate that this procedure can contain placeholders for more than one Transact-SQL statement.

Comments
The maximum size of the stored procedure is 128 MB.
A user-defined stored procedure can only be created in the current database (except for temporary procedures, which are always created in tempdb). In a single batch, the CREATE PROCEDURE statement cannot be used in combination with other Transact-SQL statements.
By default, the parameter can be empty. If you pass a null parameter value and the parameter is used in a CREATE or ALTER TABLE statement, and the column referenced in the statement does not allow NULL, SQL Server generates an error message. To prevent a null parameter value from being passed to a column that does not allow NULL, you should add programming logic to the procedure or use the default value for the column (using the defaults keyword of CREATE or ALTER TABLE).
It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER table statement in the stored procedure, such as when you create a temporary table. The ansi_dflt_on and Ansi_dflt_off options control how SQL Server assigns a null or NOT NULL attribute to a column (if not specified in the CREATE table or ALTER table statement). If a connection executes a stored procedure that sets these options differently than the connection that created the procedure, the table columns created for the second connection may have different nullability and behave differently. If NULL or NOT NULL is explicitly declared for each column, temporary tables are created with the same nullability for all connections that execute the stored procedure.
When you create or change a stored procedure, SQL Server saves the settings for SET QUOTED_IDENTIFIER and set ANSI_NULLS. These original settings are used when the stored procedure is executed. Therefore, the SET QUOTED_IDENTIFIER and set ANSI_NULLS settings for all client sessions are ignored when the stored procedure is executed. The SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur during a 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 changed. If the logic of a stored procedure depends on a specific setting, add a set statement at the beginning of the procedure to ensure that it is set correctly. When a set statement is executed from a stored procedure, the setting is valid only until the stored procedure completes. After that, the setting reverts to the value when the stored procedure was invoked. This allows individual clients to set the desired options without affecting the logic of the stored procedure.
Describes whether SQL Server interprets an empty string as a single space or is interpreted as a true empty string, controlled by the compatibility level setting. An empty string is interpreted as a single space if the compatibility level is less than or equal to 65,sql Server. If the compatibility level equals 70, SQL Server interprets the empty string as an empty string. For more information, see sp_dbcmptlevel.
To get information about stored procedures
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure is located and use the procedure name as a parameter.
Explains that stored procedures created using the encryption option cannot be viewed using sp_helptext.
To display a report about the objects referenced by the procedure, use sp_depends.
To rename a procedure, use sp_rename.

referencing objects
SQL Server allows you to create stored procedures that reference objects that do not already exist. When created, only syntax checking is performed. When executed, the stored procedure is compiled to generate an execution plan if there is no valid schedule in the cache. All objects referenced in the stored procedure are parsed only during compilation. Therefore, if the syntactically correct stored procedure references a nonexistent object, it can still be created successfully, but will fail at run time because the referenced object does not exist. For more information, see deferred name resolution and compilation.

Deferred name resolution and compatibility levels
SQL SERVER allows Transact-SQL stored procedures to reference tables that do not exist when they are created. This ability is called deferred name resolution. However, if a Transact-SQL stored procedure references a table defined in the stored procedure, and the compatibility level setting (set by executing sp_dbcmptlevel) is 65, a warning message is issued when it is created. If the table referenced at run time does not exist, an error message is returned. For more information, see sp_dbcmptlevel and deferred name resolution and compilation.

Executing stored procedures
After the 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 the syscomments. On first execution, the process is compiled to determine the best access plan for retrieving data.

Parameters using the cursor data type
Stored procedures can only use the cursor data type for OUTPUT parameters. If you specify a cursor data type for a parameter, you must also specify the varying and OUTPUT parameters. If you specify a varying keyword for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
Explains that cursor data types cannot be bound to application variables through database APIs such as OLE DB, ODBC, ADO, and Db-library. Because an output parameter must be bound before an application can execute a stored procedure, a stored procedure with a cursor OUTPUT parameter cannot be invoked through the database API. These procedures can be invoked through Transact-SQL batches, stored procedures, or triggers only if the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

Cursor Output parameters
The following rules apply to cursor output parameters when the procedure is executed:
For forward-only cursors, rows returned in the result set of the cursor are only those rows at or beyond the cursor position at the end of the execution of the stored procedure, for example:
A non-scrolling cursor is opened on a 100-row result set named RS in the procedure.
The procedure extracts the first 5 lines of the result set RS.
The procedure returns to its caller.
The result set RS returned to the caller consists of line 6th to 100th of RS, where the cursor in the caller is before the first line of Rs.
For forward-only cursors, the entire result set is returned to the calling batch, stored procedure, or trigger if the cursor is in front of the first row after the stored procedure completes. When returned, the cursor is positioned before the first row.
For forward-only cursors, empty result sets are returned for calling batches, stored procedures, or triggers if the position of the cursor exceeds the end of the last row after the stored procedure completes.
Indicates that an empty result set differs from a null value.
For scrollable cursors, all rows in the result set are returned to the calling batch, stored procedure, or trigger at the end of the execution of the stored procedure. When returned, the cursor remains where the extraction was last performed during the procedure.
For any type of cursor, if the cursor is closed, the null value is passed back to the calling batch, stored procedure, or trigger. This can also occur if a cursor is assigned to a parameter, but the cursor has never been opened.
Indicates that the shutdown state only affects when it is returned. For example, you can close a cursor in a procedure, open a cursor at a later time, and then return the cursor's result set to the calling batch, stored procedure, or trigger.

Temporary stored procedures
SQL Server supports two kinds of temporary procedures: local temporary procedures and global temporary procedures. A local temporary procedure can only be used by the connection that created the procedure. Global temporary procedures can be used by all connections. A local temporary procedure is automatically dropped at the end of the current session. The global temporary procedure is dropped at the end of the last session that uses the procedure. Typically, at the end of the session in which the procedure was created.
The temporary process is named with # and # # and can be created by any user. After the procedure is created, the owner of the local procedure is the only user who can use the procedure. Permission to perform a local temporary procedure cannot be granted to another user. If you create a global temporary procedure, all users can access the procedure, and permissions cannot be explicitly revoked. Only users with explicit create PROCEDURE permissions in the tempdb database can explicitly create temporary procedures in the database (without the use of a number character name). You can grant or revoke permissions in these procedures.
Explains that frequent use of temporary stored procedures can have a negative impact on performance by generating contention on system tables in tempdb. It is recommended to use sp_executesql instead. sp_executesql does not store data in the system tables, so this problem can be avoided.

Automatic execution of stored procedures
SQL Server can automatically execute one or more stored procedures when it starts. These stored procedures must be created by the system administrator and executed as background procedures under the sysadmin fixed server role. These procedures cannot have any input parameters.
There is no limit to the number of startup procedures, but be aware that each startup process consumes one connection at execution time. If you must execute multiple procedures at startup, but do not need to execute in parallel, you can specify a procedure to use as a startup procedure to call another procedure. This takes up only one connection.
When the last database is restored at startup, the stored procedure is started. To skip execution of these stored procedures, specify the startup parameters as trace flag 4022. If you start SQL Server with the lowest configuration (with the-f tag), the startup stored procedure is not executed. For more information, see Trace Flags.
To create a startup stored procedure, you must be logged on as a member of the sysadmin fixed server role and create a stored procedure in the master database.
Use sp_procoption to:
Specify an existing stored procedure as the startup process.
Stops executing the procedure when SQL Server starts.
View a list of all procedures executed at SQL Server startup.
Stored Procedure Nesting
A stored procedure can be nested, that is, one stored procedure can invoke another stored procedure. When the invoked procedure starts executing, the nesting level is incremented, and the nesting level is reduced after the called procedure has finished executing. If the maximum nesting level is exceeded, the entire call process chain fails. You can use the @ @NESTLEVEL function to return the current nesting level.
To estimate the size of the compiled stored procedure, use the following performance monitoring counters.
Performance Monitor object name Performance Monitor counter name
SQL Server: Buffer Manager Cache Size (page count)
SQL Server: Cache Manager Cache Hit rate
Cache pages
Cache Object Count *
* These counters can be used by cache objects of various classifications, including special SQL, preparing SQL, procedures, triggers, and so on.
For more information, see SQL Server:buffer Manager object and SQL Server:cache Manager object.

Sql_statement Limit
In addition to set SHOWPLAN_TEXT and set SHOWPLAN_ALL, which must be the only statements in a batch, any set statement can be specified inside a stored procedure. The SET options you select are valid during the execution of the stored procedure and revert to the original setting.
If another user wants to use a stored procedure, within that stored procedure, the object names used by some statements must be qualified with the name of the object owner. These statements include:
ALTER TABLE
CREATE INDEX
CREATE TABLE
All DBCC statements
DROP TABLE
DROP INDEX
TRUNCATE TABLE
UPDATE STATISTICS

Permissions
The CREATE PROCEDURE permissions are granted to members of the sysadmin fixed server role and to the db_owner and db_ddladmin fixed database roles by default. Members of the sysadmin fixed server role and member of the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. The permission to execute the stored procedure is granted to the owner of the procedure that can set execution permissions for other database users.

Example
A. Using a simple procedure with a complex SELECT statement
The following stored procedure returns all authors (names), published books, and publishers from a four-table join. The 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
Au_info_all stored procedures can be performed in the following ways:
EXECUTE Au_info_all
--Or
EXEC Au_info_all
If the procedure is the first statement in a batch, you can use:
Au_info_all
B. Using a simple process with parameters
The following stored procedure returns only the specified author (name), published book, and publisher from a four-table join. The stored procedure accepts a value that exactly matches 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
Au_info stored procedures can be performed in the following 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 '
If the procedure is the first statement in a batch, you can use:
Au_info ' dull ', ' Ann '
--Or
Au_info @lastname = ' dull ', @firstname = ' Ann '
--Or
Au_info @firstname = ' Ann ', @lastname = ' dull '
C. Using simple procedures with wildcard parameters
The following stored procedure returns only the specified author (name), published book, and publisher from a four-table join. The stored procedure matches the parameters passed, and if no arguments are supplied, the default defaults are used.
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 = ' 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
Au_info2 stored procedures can be executed in a variety of combinations. Only some of the combinations are listed below:
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. Using OUTPUT parameters
The OUTPUT parameter allows an external procedure, batch, or multiple Transact-SQL statements to access a value that is set during procedure execution. The following example creates a stored procedure (titles_sum) and uses an optional input parameter and an output parameter.
First, create the process:
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 = '% ', @ @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 to control the streaming language
Indicates that the OUTPUT variable must be defined when the table is created and the variable is used.
Parameter names and variable names do not necessarily match, but the data type and parameter positions must match (unless you use the @ @SUM = variable form).
DECLARE @ @TOTALCOST Money
EXECUTE titles_sum ' the% ', @ @TOTALCOST OUTPUT
IF @ @TOTALCOST < 200
BEGIN
PRINT '
PRINT ' All of the titles can be purchased for less than.
End
ELSE
SELECT ' Total cost ', these titles are $ ' + 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 the titles can be purchased for less than.
E. Using OUTPUT cursor parameters
The OUTPUT cursor parameter is used to pass the local cursor of the stored procedure back to the calling batch, stored procedure, or trigger.
First, you create the following procedure to declare and open 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, declare a local cursor variable, perform the procedure above to assign the cursor to a local variable, and then extract the row 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. Using the WITH RECOMPILE option
The WITH RECOMPILE clause can be helpful if the parameters provided for the procedure are not typical parameters, and the 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. Using the WITH ENCRYPTION option
The WITH ENCRYPTION clause hides the text of a stored procedure from the user. The following example creates the encryption process, uses the sp_helptext system stored procedure to obtain information about the encryption process, and then attempts to obtain information about the process 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 that encrypts the contents of the stored procedure.
SELECT c.id, c.text from syscomments c INNER JOIN sysobjects o on c.id = o.id WHERE o.name = ' encrypt_this '