My Sql Note: sp

What Is a Stored Procedure?

A Transact-SQL stored procedure is a set of T-SQL code that is stored in a SQL Server database and compiled when used.

You create this set of code using the CREATE PROCEDURE command. You can use most Transact-

SQL commands in a stored procedure; however, some commands (such as CREATE PROCEDURE, CREATE VIEW, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and so forth) not allowed in CREATE PROCEDURE.

CREATE PROCEDURE command must be the first (or only) statement in a command batch, and therefore aren’t allowed in stored procedures.

Most Transact-SQL commands behave the same in a stored procedure as they do in a command batch, but some have special capabilities or exhibit different behavior when executed within the context of a stored procedure. Listing 1–1 shows a simple stored procedure (only the code from the CREATE PROCEDURE line down to the ensuing GO actually constitutes the stored procedure

Listing 1–1 A simple stored procedure

Use Northwind

IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL

DROP PROC dbo.ListCustomersByCity

CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'

SELECT City, COUNT(*) AS NumberOfCustomers

FROM Customers

WHERE Country LIKE @Country

GROUP BY City

EXEC dbo.ListCustomersByCity

Advantages:

They can reduce network traffic.

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.It can become more important benefit when the bandwidth of the network is less.

Bandwidth:

The amount of data that can be carried from one point to another in a given time period (usually a second). This kind of bandwidth is usually expressed in bits (of data) per second (bps). Occasionally, it's expressed as bytes per second (Bps).

Encapsulation of Queries:

One of the first uses I discovered for stored procedures was to encapsulate queries so that I didn't have to worry about where they were saved, and I could execute them from anywhere on the network.

Parameterized Queries:

After I figured out how basic stored procedure worked and mastered the SELECT statement, the next thing that I did was start to work on parameterized queries. These stored procedures accepted one or two parameters and returned a subset of the information in the tables in which the user was interested. This enabled the users to return only those

results that were important to them.

Maintainability of Application Logic:

One very widespread use of stored procedures is to use them as a container for application logic. This way, you can maintain all your company's business rules and logic in a single location, which makes them extremely easy to maintain. If a business rule changes, all you have to do is change the code in the stored procedure, and all users would have the new code.

Security: One of the best, but least implemented, uses for stored procedures is as a security measure. If you create a stored procedure that accesses a table, you can revoke access to that table; the only way your users can access that table is through the stored procedure you've created. This is an extremely powerful method for locking down the server and keeping users from accessing information they aren't supposed to.

Automation of Administration Tasks:

The most fun and most interesting types of stored procedures to write (for me at least) are procedures that assist in the automation of the SQL Server. Like system stored procedures, the core set of procedures installed with SQL Server, these procedures are used to perform low-level system functions and to return information about the server and the objects on the server. I call these procedures utility stored

procedures. Later in this book, I present some of the most useful utility stored

procedures that I have written for you to use and learn from.

Compilation step is required only once when the stored procedure is created. Then after it does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.

It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.

Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.

They can enhance the security of your application.

Parameterized stored procedures can help protect your application from SQL Injection attacks.

· Execution plan retention and reuse

· Query auto-parameterization

· Encapsulation of business rules and policies

· Application modularization

· Sharing of application logic between applications

· Access to database objects that is both secure and uniform

· Consistent, safe data modification

· Network bandwidth conservation

· Support for automatic execution at system start-up

· Enhanced hardware and software capabilities

· Improved security

· Reduced development cost and increased reliability

· Centralized security, administration, and maintenance for common routines

Pinal Dave:"There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.

I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only."

Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

Prior to SQL Server 7.0, when you executed the CREATE PROCEDURE statement,

SQL Server would parse the statement,

Resolve the names of all objects referenced in the procedure and

Partially compile the stored procedure.

The precompiled statement was stored until the procedure was executed. This saved compilation time when the procedure was executed, thus speeding up the server's response time.

With SQL Server 7.0 and beyond, Microsoft changed the way this worked. Now, when CREATE PROCEDURE is executed,

SQL Server first parses the statement to ensure that all statements are syntactically correct. If the server encounters any sort of syntax error, the creation of the procedure is cancelled and an error is returned to the user.

If there are no syntax errors, SQL Server stores the text of the stored procedure in the syscomments table in the database that the user who created the stored procedure was using at the time CREATE PROCEDURE was executed. Nothing else happens until the stored procedure is actually executed. After the stored procedure is run for the first time,SQL Server prepares the code contained in the procedure to be executed.

“Syscomments” is a misnomer. The table doesn’t store comments per se; it stores source code. The name is a vestige from the pre-7.0 days and was so named because it stored the optional source code to stored procedures (and other objects), whereas sysprocedures stored a pseudo-compiled version (a kind of normalized query tree) of the procedures themselves. This is no longer true,and the sysprocedures table no longer exists. Syscomments is now the sole

repository for stored procedures, views, triggers, user-defined functions (UDFs), rules, and defaults. If you remove an object’s source code from syscomments, you can no longer use that object.

How SQL Server Processes Stored Procedures:

When you first execute a stored procedure, the SQL Server query processor reads the code of the stored procedure from the syscomments table and begins the deferred name resolution process.

select * from syscomments where id = (select id from sysobjects where name = 'test')

No rows affected.

If any syntax error is exist:

create proc rule_test

begin

slect city from AdventureWorks2008.person.Address where AddressID = 535

end

Msg 102, Level 15, State 1, Procedure rule_test, Line 4

Incorrect syntax near 'slect'.

select * from syscomments where id = (select id from sysobjects where name = 'test')

No rows affected.

If syntactically correct:

create proc test

begin

select city from AdventureWorks2008.person.Address where AddressID = 535

end

Command(s) completed successfully.

select id,number, colid, status,texttype, language, encrypted, compressed

from syscomments where id = (select id from sysobjects where name = 'test')

Result Set:

id number colid status texttype language encrypted compressed

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

151671588 1 1 0 2 0 0 0

Here table name is wrong however proc created succesfully:

create proc test

begin

select city from AdventureWorks2008.person.Adress where AddressID = 535

end

select text

from syscomments where id = (select id from sysobjects where name = 'test')

text

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

create proc test

begin

select city from AdventureWorks2008.person.Adress where AddressID = 535

end

By Executing the SP

EXEC test

Msg 208, Level 16, State 1, Procedure test, Line 4

Invalid object name 'AdventureWorks2008.person.Adress'.

The first time the stored procedure is executed, the SQL Server query processor reads the data from the syscomments table and checks whether all the objects referenced in the stored procedure are contained in the database. This process is known as deferred name resolution.

If any errors are encountered during deferred name resolution, SQL Server returns an error and halts the execution of the stored procedure. Because of the deferred name resolution process, any objects referenced in a SQL Server stored procedure do not need to be in the database when the stored procedure is created. The objects must be there only when the stored procedure is first run. This type of resolution is used because it enables the user to drop and recreate tables (and other objects in the database) without having to recompile every stored procedure that references that object.

Deferred Name Resolution:

Deferred name resolution is the process of checking the names of all objects that a stored procedure uses to make sure that they exist.

Tables, stored procedures, or other objects referenced by the stored procedure don't have to exist when the procedure is first created. SQL Server doesn't resolve the names of the objects until the stored procedure is actually executed.

The resolution process occurs whenever a stored procedure needs to be compiled, such as when the server is first restarted or after the query plan is aged out of memory.

During the resolution process, not only does SQL Server check the objects that are referenced in the stored procedure, but it also performs other validation, such as the following:

? Ensuring that the column data types in any referenced tables are compatible with any variables that reference those columns

? Checking that any variables and columns passed into SQL Server functions are compatible with the expected inputs for the functions

? Making sure that any data being passed out of the stored procedure is compatible with any variables that it's being passed into

If any object being referenced by the stored procedure is renamed or deleted, an error will be returned to the user when the stored procedure is executed. If an object referenced in the stored procedure is deleted and a different one is created with the same name, the stored procedure uses the new object, assuming that all the data types in the new object are compatible. The stored procedure doesn't have to be recompiled for this to occur.

This activity is extremely different from the way prior versions of SQL Server worked.

In previous versions of SQL Server, you had to recompile the stored procedure if you made any changes to the underlying objects. This could lead to some very difficult-to troubleshoot problems.

For example, if you renamed an object referenced by a stored procedure and then created a new object with the old name, the stored procedure would continue to use the old object, even though the name had changed. This was because SQL Server used to precompile the stored procedure and resolve the names of any objects referenced in the stored procedure to their object IDs.When the old object was renamed, SQL Server didn't—and still doesn't—change the object ID. Because the stored procedure had been precompiled and all the names had already been resolved, everything continued to execute exactly as it had.

If any errors are encountered during the name resolution process, an error is returned and execution halts. If the name resolution process is successful, the stored procedure is analyzed to create an execution plan.

Deferred Name Resolution and an Interesting Exception

Before we go further, it’s worth mentioning that there’s an interesting exception to SQL Server’s default deferred name resolution behavior. Run the code in Listing 1–2 in Query Analyzer:

Listing 1–2 SQL Server doesn’t allow you to include more than one CREATE TABLE statement for a given temporary table in the same stored procedure.

CREATE PROC testp @var int

IF @var=1

CREATE TABLE #temp (k1 int identity, c1 int)

ELSE

CREATE TABLE #temp (k1 int identity, c1 varchar(2))

INSERT #temp DEFAULT VALUES

SELECT c1 FROM #temp

The syntax contained in the stored procedure is seemingly valid, yet we get

this message when we run it:

Server: Msg 2714, Level 16, State 1, Procedure testp, Line 6

There is already an object named '#temp' in the database.

Why? Obviously @var can’t be both equal to one and not equal to one, right? To get a little closer to the answer, change the temporary table reference to a permanent table, like the one shown in Listing 1–3.

Listing 1–3 Changing the table to a permanent table works around the temporary table limitation.

CREATE PROC testp @var int

IF @var=1

CREATE TABLE tempdb..temp (k1 int identity, c1 int)

ELSE

CREATE TABLE tempdb..temp (k1 int identity, c1 varchar(2))

INSERT #temp DEFAULT VALUES

SELECT c1 FROM #temp

This procedure is created without error. What’s happening here? Why does SQL Server care whether the table created is a temporary or a permanent table? And why does it matter now—before the procedure is even executed and before the value of @var can be known?

What appears to be happening is that SQL Server resolves CREATE TABLE references to temporary tables before inserting the procedure into syscomments—an apparent vestige from the pre-7.0 days when object references were resolved when a procedure was first created. The same limitation applies to variable declarations and, therefore, to the table data type. You can’t

declare a variable more than once in a single stored procedure, even if the declarations

reside in mutually exclusive units of code. This differs from how permanent tables are handled, and is the reason the code in Listing 1–3 runs without error. It appears that, beginning with SQL Server 7.0, deferred name resolution was enabled for permanent tables, but not for temporary ones. Whatever the case, you can’t execute code like that shown in Listing 1–2, so here’s a workaround (Listing 1–4):

Listing 1–4 Including one CREATE TABLE statement, but two sets of ALTER TABLE statements, works around the problem.

CREATE PROC testp @var int

CREATE TABLE #temp (k1 int identity)

IF @var=1

ALTER TABLE #temp ADD c1 int

ELSE

ALTER TABLE #temp ADD c1 varchar(2)

INSERT #temp DEFAULT VALUES

EXEC('SELECT c1 FROM #temp')

This technique creates the table just once, then alters it to match the Data Definition Language (DDL) specification (spec) we want to end up with. Note the use of EXEC() to select the column we added with ALTER TABLE.

The use of dynamic SQL is necessary because the newly added column isn’t immediately visible to the procedure that added it. We’re forced to create and execute an ad hoc query string to access it by name. (Note that you can reference the column indirectly—for example, through SELECT * or via an ordinal value in an ORDER BY clause, just not by name).

Another disadvantage of this approach is that it mixes DDL (the CREATE and ALTER statements) and Data Modification Language (DML; the INSERT and SELECT). Because of this, the procedure’s execution plan must be recompiled when the INSERT is encountered (the temporary table’s schema information (info) has changed since the original execution plan was formulated). Any stored procedure that creates a temporary table, then processes it further, will cause a plan recompile because the table’s schema info did not exist when the execution plan was first created; however, the procedure in Listing 1–4 causes an additional recompile to occur because it alters this schema, then processes the table further. Particularly with large procedures in high-throughput environments, this can cause performance problems as well as blocking and concurrency issues because a compile lock is taken out on the stored procedure

while the execution plan is being recompiled. Listing 1–5 presents a workaround that doesn’t require the use of dynamic T-SQL:

Listing 1–5 A workaround for the temporary table creation problem.

CREATE PROCEDURE testp4

INSERT #temp DEFAULT VALUES

SELECT c1 FROM #temp

CREATE PROC testp3

CREATE TABLE #temp (k1 int identity, c1 varchar(2))

EXEC dbo.testp4

CREATE PROC testp2

CREATE TABLE #temp (k1 int identity, c1 int)

EXEC dbo.testp4

CREATE PROC testp @var int

IF @var=1

EXEC dbo.testp2

ELSE

EXEC dbo.testp3

Although this technique alleviates the need for EXEC(), it also forces us to completely reorganize the stored procedure. In fact, we’re forced to break the original procedure into four separate routines and call the fourth one redundantly from the second and third routines. Why? First, instead of having two CREATE TABLE statements for the same temporary table in one procedure—which, as we’ve discovered, isn’t supported—we moved each CREATE

TABLE to its own procedure.

Second, because a temporary table is automatically dropped as soon as it goes out of scope, we can’t simply create it, then return to the top-level routine and add rows to it or SELECT from it. We have to do that either in one of the procedures that created it or in a common routine that they call. We chose the latter, so procedures two and three call a fourth routine that takes care of inserting the row into the temporary table and selecting the c1 column from it. (Because objects created in a procedure are visible to the procedures it calls, the fourth routine can “see” the table created by its caller.) This approach works, but is far from optimal. Think about how complex this would get for a really large procedure. Breaking it into multiple,distinct pieces may not be practical. Still, it avoids the necessity of having to create and execute an ad hoc T-SQL string and should generally perform better than that approach.

Creation of an Execution Plan:

After all the object names referenced in the stored procedure are successfully resolved, the SQL Server query optimizer analyzes all the stored procedure code and creates an execution plan.

An execution plan is basically a roadmap that shows that fastest method for SQL

Server to access the data that the stored procedure is attempting to access.

When SQL Server is creating the execution plan, the query optimizer takes several variables into account. Some of these variables

Include the following:

? The quantity of data in the tables that are referenced.

? The types of indexes that have been created on the tables referenced in the query.

? The indexed columns. It's extremely important that you analyze how the data in your tables is accessed. When a certain column or group of columns is used to identify the rows that need to be retrieved from the table, you should ensure that there's an index on those columns. It's also important that you know the order in which the columns will be referenced in the queries. You must make sure that the columns in the index are in the same order as the columns that are most frequently listed in queries. If the columns are listed differently in the query than they are listed in the index, the index won't be used.

? The data distribution in the indexes.

Note

To determine the distribution, SQL Server uses distribution statistics that are kept on the indexes. It's possible for these distribution statistics to become old and obsolete. To keep this from occurring, run UPDATE STATISTICS on any table that experiences large changes in the indexed values or if you perform a large insert or delete from the table.

? The types of comparison operators and the values being compared to in the WHERE clause.

? The presence and types of joins being used in the query.

? The existence of the UNION, GROUP BY, or ORDER BY keyword.

After all these factors are analyzed and SQL Server determines the fastest way to access the data, the stored procedure is compiled into an execution plan. The execution plan is cached into memory and used to run the stored procedure. SQL Server will reuse this execution plan

whenever the stored procedure is executed until the SQL Server is stopped or needs memory and swaps out that execution plan.

Execution Plan Makeup:

As mentioned previously, with the release of SQL Server 7.0, there is a much greater chance that the query optimizer will reuse an execution plan. Microsoft increased the chances that execution plans will be reused by changing the makeup of the plans. There are two main components to a SQL Server execution plan: the query plan and the execution context.

The majority of a SQL Server execution plan is made up of an object known as a query plan, a read-only reusable structure that any user on the system can use. The query plan is essentially a roadmap that is used to get to the data in the tables quickly.

The query plan doesn't contain any information about a user's query parameters, only how to get to the data. SQL Server will maintain a maximum of only two query plans in memory at any point in time:

one handles all serial executions, the other handles all parallel executions.

The second part of an execution plan contains all the parameters unique to a specific user's execution. This structure is known as the execution context. Although there is no set number of execution context structures, SQL Server will reuse any that have become inactive. This separation allows SQL Server to reuse query plans for queries that have the same query plan with different parameters in the WHERE clause.

Putting it all together, SQL Server reuses available execution plans when a user executes a SQL query or stored procedure. The parameters or search criteria that the user passes in are stored in the execution context. The execution context is specific to the individual user.

Practical:

Query to view plan cache:

SELECT --p.plan_handle,

p.cacheobjtype,

--bucket = p.bucketid,

used = p.usecounts,

--b = size_in_bytes,

t.[text]

FROM sys.dm_exec_cached_plans AS p

CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t

Sample query:

select AddressID,city from Person.Address where AddressID = 535

Rows from plan cache:

cacheobjtype used text

Compiled Plan 1 select AddressID,city from Person.Address where AddressID = 535

Compiled Plan 1 (@1 smallint)SELECT [AddressID],[city] FROM [Person].[Address]

WHERE [AddressID]=@1

The plan cache shows one execution context plan and query plan

execution context:Specific to user’s execution

query plan :Read-only reusable structure that any user on the system can use

SQL Profiler:

SP:CacheInsert (@1 smallint)SELECT [AddressID],[city] FROM [Person].[Address] WHERE [AddressID]=@1

SP:CacheInsert select AddressID,city from Person.Address where AddressID = 535

SQL:BatchStarting select AddressID,city from Person.Address where AddressID = 535

SQL:BatchCompleted select AddressID,city from Person.Address where AddressID = 535

Sample Query: executed same query then

select AddressID,city from Person.Address where AddressID = 535

Rows from plan cache:

cacheobjtype used text

Compiled Plan 1 (@1 smallint)SELECT [AddressID],[city] FROM [Person].[Address]

WHERE [AddressID]=@1

Compiled Plan 2 select AddressID,city from Person.Address where AddressID = 535

SQL Profiler:

SP:CacheHit select AddressID,city from Person.Address where AddressID = 535

SQL:BatchStarting select AddressID,city from Person.Address where AddressID = 535

SQL:BatchCompleted select AddressID,city from Person.Address where AddressID = 535

Sample Query: executed simillar query with different value in where clause

select AddressID,city from Person.Address where AddressID = 532

Rows from plan cache:

cacheobjtype used text

Compiled Plan 2 select AddressID,city from Person.Address where AddressID = 535

Compiled Plan 2 (@1 smallint)SELECT [AddressID],[city] FROM [Person].[Address] WHERE

[AddressID]=@1

SQL Profiler:

SP:CacheHit (@1 smallint)SELECT [AddressID],[city] FROM [Person].[Address] WHERE

[AddressID]=@1

SP:CacheInsert select AddressID,city from Person.Address where AddressID = 532

SQL:BatchStarting select AddressID,city from Person.Address where AddressID = 532

SQL:BatchCompleted select AddressID,city from Person.Address where AddressID = 532

How SQL Server Chooses Query Plans:

When a stored procedure—or any other SQL statement—is sent to the SQL Server, the query engine checks through the procedure cache to see whether an execution plan has been created for that stored procedure or SQL statement. If a plan has been stored in the procedure cache, SQL Server loads and uses that plan, thus saving the time and overhead required to re-create a new query plan. If the query engine doesn't find an execution plan, it must create one and store it in the procedure cache for future use.

When SQL Server searches the procedure cache to see whether an execution plan exists, it uses an extremely efficient method that almost always uses fewer system resources than compiling a new procedure. One major caveat to the search method SQL Server uses is that it requires that all object names be fully qualified for it to work.

Look at the following code:

SELECT *

FROM pubs.dbo.authors

SELECT *

FROM authors

The first SELECT statement in this listing will be matched to an existing execution plan, if one exists. The second SELECT statement will be recompiled, even if there's an existing plan. The reason is that if the names aren't fully qualified, the database doesn't truly know the exact object that you are looking for. Remember that it is possible to have more than one table with the same name in the database as long as each table has a separate owner. If you don't specify an owner for the object in your query, even if there are no tables with the same name in the database, SQL Server must still act as though there are.

Therefore, to effectively reuse execution plans, you should always ensure that the objects referenced in the SQL statements you create are fully qualified.

Programming Stored Procedure Rules:

The CREATE PROCEDURE statement must be the first statement in a SQL Server batch. If the procedure is not the first statement,SQL Server returns an error to the user and fails to create the procedure.

set nocount on

create proc rule_test

begin

select city from AdventureWorks2008.person.Address where AddressID = 535

end

Error Message:

Msg 111, Level 15, State 1, Procedure rule_test, Line 6

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Resolution:

set nocount on

create proc rule_test

begin

select city from AdventureWorks2008.person.Address where AddressID = 535

end

Command(s) completed successfully.

Workaround:

It is becuase CREATE or ALTER procedure statement should always be executed as seperate batch

We don't want "some other code here" to be included in the definition of the stored procedure because the definition will be stored in syscomments table.

We also wouldn't know what to do with this

we can legally place comments before CREATE PROCEDURE, how does the parser know which object the comment belongs to?

/* Hi this is to test why create proc need to be first statement in batch */

create proc test23 as

begin

select 10

end

select * from syscomments where id =(select id from sysobjects where name = 'test23')

Text column:

/* Hi this is to test why create proc need to be first statement in batch */

create proc test23 as begin select 10 end

Workaround2 :

/* Hi this is to test why create proc need to be first statement in batch */

create proc test24 as

begin

select 10

end

select * from syscomments where id =(select id from sysobjects where name = 'test24')

Text column:

create proc test24 as begin select 10 end

Stored procedures can execute any SQL statement available to the creator, except for the following statements: CREATE DEFAULT, CREATE TRIGGER, CREATE PROCEDURE,CREATE FUNCTION,CREATE SCHEMA,SET SHOWPLAN_TEXT,SET SHOWPLAN_ALL,CREATE VIEW,and CREATE RULE.

create proc rule_test

begin

select city from AdventureWorks2008.person.Address where AddressID = 535

create proc test as

begin

select 10

end

Msg 156, Level 15, State 1, Procedure rule_test, Line 6

Incorrect syntax near the keyword 'proc'.

Alternative Way:

TIP: You can work around this limitation—the inability to construct most other kinds of objects from within a stored procedure—by constructing a T-SQL string and executing it via sp_executesql or the EXEC() function:

create proc rule_test

begin

select city from AdventureWorks2008.person.Address where AddressID = 535

exec('create proc test as

begin

select 10

end')

end

Command(s) completed successfully.

EXEC rule_test

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.test2'. The stored procedure will still be created.

The warning message is due to the fact that the test2 procedure doesn’t exist when the test procedure is first created. You can safely ignore it.

Any database object, except for those SQL statements listed earlier, can be created and used within a stored procedure.

Stored procedures can create and reference temporary tables.

If you create a local, or private, temporary table inside a stored procedure, that table is in the database only for the use of the stored procedure that created it. When the stored procedure has completed its execution, the temporary table is dropped from

the database.

If you create a stored procedure that executes a second stored procedure, all objects created in the first stored procedure are available to the second stored procedure. This includes both private and public temporary tables.

If you execute a remote stored procedure that modifies data on a remote server, those changes cannot be rolled back. This is because remote stored procedures do not take place as a part of the local transaction.

SQL Server stored procedures can take a maximum of 1,024 parameters. This includes both input and output parameters.

SQL Server places no logical limit on the number of internal variables that can be defined in a stored procedure. The amount of available memory determines how many variables can be used.

Parameters passed into the stored procedures are NULL by default. This can pose problems when SQL Server is adding or modifying data in tables that do not allow NULL values. If the stored procedure attempts an UPDATE or INSERT into a table that does not allow a NULL, SQL Server returns an error. To keep this from occurring, place logic in the stored procedure to check for the NULL and replace it with a valid value or put a default value on the table.

SQL Server stored procedures are limited to a maximum of 128MB, depending on the amount of memory in the server. If you need to create a stored procedure that is larger than 128MB, you should create two separate stored procedures that are called by a third’s

Permissions and Limitations:

Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

Only members of the sysadmin, db_owner, or db_ddladmin role (or those explicitly granted CREATE PROC permission by a member of the appropriate role) can execute CREATE PROCEDURE.

The maximum stored procedure size is 128MB.

The maximum number of parameters a procedure may receive is 1,024.

Source Code Protection:

After the stored procedures that make up your SQL Server application have been installed, there are times that you will want to protect your code from being viewed by the users and administrators of your system. Of course, if the users or administrators actually have to run a script to install the procedures, this step is a moot point.

When a stored procedure is created, SQL Server stores the actual code for the stored procedure in the syscomments system table in the database in which the stored procedure resides. In earlier versions of SQL Server, people simply deleted the information contained in that table to keep other people from seeing the code. This worked for that purpose, but it did not allow your users to upgrade to the next version of SQL Server when it was released. Now, the best option you have to keep your users from viewing the code that was used to create the procedure is to encrypt the information using the WITH ENCRYPTION option. After the syscomments table has been encrypted, it cannot be decrypted and cannot be viewed by anyone.

Take a look at the example

Encrypting Stored Procedures

CREATE PROCEDURE uspTestProcedure

SELECT *

FROM authors

sp_helptext uspTestProcedure

ALTER PROCEDURE uspTestProcedure

WITH ENCRYPTION

SELECT *

FROM authors

sp_helptext uspTestProcedure

When you look run the sp_helptext system stored procedure against the unencrypted stored procedure, it returns the code exactly as you typed it in. When the stored procedure is then encrypted, sp_helptext returns a message saying that the source has been encrypted with no

other information.

when SP is encrypted Actual Execution Plan of the SP is also not visible. In fact, it does not respond to that command(Clrl+M) and does not display the tab of execution plan if only Encrypted SP is called.

Listing a Stored Procedure

Assuming the object is not encrypted, you can list the source code to a procedure, view, trigger, UDF, rule, or default object using the sp_helptext system procedure. An example is included in Listing 1–6:

Listing 1–6 sp_helptext lists the source for a stored procedure.

EXEC dbo.sp_helptext 'ListCustomersByCity'

Text

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

CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'

SELECT City, COUNT(*) AS NumberOfCustomers

FROM Customers

WHERE Country LIKE @Country

GROUP BY City

Objects they reference to be owner qualified:

Avoid broken ownership chains when dealing with stored procedures and the objects they reference. Try to ensure that the owner of a stored procedure and the owner of the objects it references are the same. The best way to do this is by specifying the dbo user as the owner of every object you create. Having multiple objects with the same name but different owners adds an unnecessary layer of indirection to the database that’s almost always more trouble than it’s worth. While perhaps useful during the development phase of a project, it’s definitely something you should avoid on production servers.

When used within a stored procedure, certain commands require the objects they reference to be owner qualified (an object reference is said to be owner qualified when the object name is prefixed with the name of the owner and a period) if the procedure is to be executed by users other than the owner.

These commands are

_ CREATE TABLE

_ ALTER TABLE

_ DROP TABLE

_ TRUNCATE TABLE

_ CREATE INDEX

_ DROP INDEX

_ UPDATE STATISTICS

_ All DBCC commands

USE dbname:

Include USE dbname at the top of creation scripts for procedures that must reside in a specific database. This ensures that they end up where they belong and alleviates having to remember to set the current database context before executing the script.

Keep each stored procedure as simple and modular as possible. Ideally, a stored procedure will accomplish a single task or a small group of closely related tasks.

SET NOCOUNT ON:

As a rule, SET NOCOUNT ON should be the first statement in every stored procedure you create because it minimizes network traffic between SQL Server and client applications. Setting NOCOUNT on disables DONE_IN_PROC messages—the messages SQL Server normally sends to the client indicating the number of rows affected by a T-SQL statement. Because these messages

are very rarely used, eliminating them conserves network bandwidth without really giving up any functionality and can speed up applications considerably.

Note that you can disable DONE_IN_PROC messages for the entire server via a trace flag (3640) and for a particular user session via the sp_configure ‘user options’ command. (In rare circumstances, disabling DONE_IN_PROC messages can cause problems with some applications—for

example, some older versions of Microsoft Access and certain ill-behaved OLEDB providers).

Executing Stored Procedures:

Although executing a stored procedure can be as easy as listing it on a line by itself in a T-SQL command batch, you should make a habit of prefixing all stored procedure calls with the EXEC keyword, like this:

EXEC dbo.sp_who

Stored procedure calls without EXEC must be the first command in a command batch. Even if this were the case initially, inserting additional lines before the procedure call at some point in the future would break your code.

You should also be sure to owner-qualify procedure calls (“dbo” in the previous example). Omitting the owner from a procedure call causes SQL Server to momentarily place a compile lock on the procedure because it cannot locate it immediately in the procedure cache. This lock is released once the proceduresans- owner is located in the cache, but can still cause problems in high-throughput environments. Owner-qualifying objects is simply a good habit to get into. It’s one of those things you can do to save yourself problems down the road.

INSERT and EXEC

The INSERT command supports calling a stored procedure to supply rows for insertion into a table. Listing 1–11 shows how:

Listing 1–11 You can use INSERT…EXEC to save a stored procedure’s output in a table.

CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname

NULL, indid int, type char(4), resource char(15), mode char(10), status

char(6))

INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status)

EXEC dbo.sp_lock

SELECT * FROM #locks

DROP TABLE #locks

This is a handy way of trapping the output of a stored procedure in a table so that you can manipulate it or retain it for later use. Prior to the advent of cursor OUTPUT parameters, this was the only way to perform further work on a stored procedure’s result set within Transact-SQL.

Note that INSERT…EXEC works with extended procedures that return result sets as well. A simple example is shown in Listing 1–12:

Listing 1–12 INSERT…EXEC works with extended procedures as well.

CREATE TABLE #cmd_result (output varchar(8000))

INSERT #cmd_result

EXEC master.dbo.xp_cmdshell 'TYPE C:\BOOT.INI'

SELECT * FROM #cmd_result

DROP TABLE #cmd_result

Temporary Procedures

You create temporary procedures the same way you create temporary tables— a prefix of a single pound sign (#) creates a local temporary procedure that is visible only to the current connection, whereas a double pound sign prefix (##) creates a global temporary procedure all connections can access.

Temporary procedures are useful when you want to combine the advantages of using stored procedures such as execution plan reuse and improved error handling with the advantages of ad hoc code. Because you can build and execute a temporary stored procedure at run-time, you get the best of both worlds. For the most part, sp_executesql can alleviate the necessity for temporary procedures, but they’re still nice to have around when your needs exceed the capabilities of sp_executesql.

System Procedures

System procedures reside in the master database and are prefixed with sp_. You can execute a system procedure from any database. When executed from a database other than the master, a system procedure runs within the context of that database. So, for example, if the procedure references the sysobjects table (which exists in every database) it will access the one in the database that was current when it was executed, not the one in the master database, even though the procedure actually resides in the master. Listing 1–15 is a simple system procedure that lists the names and creation dates of the objects that match a mask:

Environmental Issues:

A number of SQL Server environmental settings affect the behavior of stored procedures. You specify most of these via SET commands. They control the way that stored procedures handle nulls, quotes, cursors, BLOB fields, and so forth. Two of these—QUOTED_IDENTIFIER and ANSI_NULLS—are stored permanently in each procedure’s status field in sysobjects, as I mentioned earlier in the chapter. That is, when you create a stored procedure, the status of these two settings is stored along with it. QUOTED_IDENTIFIER controls whether strings within double quotes are interpreted as object identifiers (e.g., table or column references), and ANSI_NULLS controls whether non-ANSI equality comparisons with NULLs are allowed.

SET QUOTED_IDENTIFIER is normally used with a stored procedure to allow the procedure to reference objects with names that contain reserved words, spaces, or other illegal characters. An example is provided in Listing 1–20.

Listing 1–20 SET QUOTED_IDENTIFIER allows references to objects with names with embedded spaces.

USE Northwind

SET QUOTED_IDENTIFIER ON

IF OBJECT_ID('dbo.listorders') IS NOT NULL

DROP PROC dbo.listorders

CREATE PROC dbo.listorders

SELECT * FROM "Order Details"

SET QUOTED_IDENTIFIER OFF

EXEC dbo.listorders

OrderID ProductID UnitPrice Quantity Discount

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

10248 11 14.0000 12 0.0

10248 42 9.8000 10 0.0

“Order Details” contains both a reserved word and a space, so it can’t be referenced without special handling. In this case, we turned on quoted identifier support and enclosed the table name in double quotes, but a better way would be to use SQL Server’s square brackets ( [ ] ) to enclose the name (e.g., [Order Details]) because this alleviates the need to change any settings. Note that bracketed object names are not supported by the ANSI/ISO SQL standard.

The ANSI_NULLS setting is even more useful to stored procedures. It controls whether non-ANSI equality comparisons with NULLs work properly. This is particularly important with stored procedure parameters that can receive NULL values. See Listing 1–21 for an example:

Listing 1–21 SET ANSI_NULLS allows comparisons between variables or

columns and NULL values to work as you would expect.

USE Northwind

IF (OBJECT_ID('dbo.ListRegionalEmployees') IS NOT NULL)

DROP PROC dbo.ListRegionalEmployees

SET ANSI_NULLS OFF

CREATE PROC dbo.ListRegionalEmployees @region nvarchar(30)

SELECT EmployeeID, LastName, FirstName, Region FROM employees

WHERE Region=@region

SET ANSI_NULLS ON

EXEC dbo.ListRegionalEmployees NULL

EmployeeID LastName FirstName Region

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

5 Buchanan Steven NULL

6 Suyama Michael NULL

Thanks to SET ANSI_NULLS, the procedure can successfully compare a NULL @region with the region column in the Northwind Employees table. The query returns the rows that have NULL region values because, contrary to the ANSI SQL specification, SQL Server checks the NULL variable against the column for equality. The handiness of this becomes more evident when a procedure defines a large number of “NULL-able” parameters. Without the ability to test NULL values for equality in a manner identical to non-NULL values, each NULL-able parameter would require special handling (perhaps using the IS NULL predicate), very likely multiplying the amount of code necessary to process query parameters.

Because SQL Server stores the QUOTED_IDENTIFIER and ANSI_NULLS settings with each stored procedure, you can trust them to have the values you require when a procedure runs. The server restores them to the values they had when the procedure was created each time the procedure runs, then resets them afterward. Here’s an example: SET ANSI_NULLS ON

EXEC dbo.ListRegionalEmployees NULL

The stored procedure still executes as though ANSI_NULLS is set to OFF. Note that you can check the saved status of a procedure’s QUOTED_IDENTIFIER and ANSI_NULLS settings via the OBJECTPROPERTY() function. An example is provided in Listing 1–22:

How to check the ANSI_NULLS and QUOTED_IDENTIFIER status for a procedure?

You can check the ANSI_NULLS and QUOTED_IDENTIFIER status for a procedure using the OBJECTPROPERTY function.

USE Northwind

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ListRegionalEmployees'),

'ExecIsAnsiNullsOn') AS 'AnsiNulls'

USE Northwind

SELECT OBJECTPROPERTY(OBJECT_ID('test'),

'ExecIsQuotedIdentOn') AS 'QuotedIdentifier'

Those that apply to procedures, like ANSI_NULLS and QUOTED_IDENTIFIER are in sys.sql_modules, where they are retrieved from by OBJECTPROPERTY.

Those that apply to databases and are set per database are available in sys.databases.

Those that apply to sessions are available in sys.dm_exec_sessions.

In the end what actualy gets applied depends from setting to setting, and the rules of overwrites and defaults are complex to say the least. Some client drivers set options on/off automatically. Not the same options and not the same defaults, depends from client to client (ODBC, OleDB, SNAC, SqlClient etc). The generic rule is:

A database option overrides an instance option.

A SET option overrides a database option.

A hint overrides a SET option.

A number of other environmental commands affect how stored procedures execute. SET XACT_ABORT, SET CURSOR_CLOSE_ON_COMMIT, SET TEXTSIZE, SET IMPLICIT_TRANSACTIONS, and numerous others help determine how a stored procedure behaves when executed. If you have a stored procedure that requires a SET command to have a particular value to run properly, set it to that value as early as possible in the procedure and document why it’s necessary via comments.

The Changing of the Code—Altering Stored Procedures:

Anyone who has ever programmed can tell you that there are times when the original code you rolled out to your users does not provide the correct functionality. Sometimes this is because of a minor problem with the code; other times, it is because the code is completely wrong. It used to be very difficult and somewhat complicated to modify stored procedures.

SQL Server 2000 provides an easy way to modify stored procedures without losing any important information.

The Old Way:

If you worked with any of the 6.x versions of SQL Server, you know that modifying stored procedures could be very difficult. This is because none of those versions provided a real way to perform this functionality. Instead, the only way to modify stored procedures was to drop the stored procedure and run a script to re-create the procedure. Although this works, several flaws are inherent in this approach.

Usually the first problem you will notice deals with the permissions assigned to the original stored procedure. Although the actual drop and re-creation of the stored procedure takes very little time, often milliseconds, your users will begin to complain that they cannot execute the procedure. That is because when the original stored procedure is dropped, all permissions associated with that procedure are dropped as well. If you do not remember to reassign the permissions to the procedures, your users will not be able to execute the stored procedure.

A second problem you will notice when you modify a stored procedure the old way is not as problematic for your users. Instead, you as the developer will notice the problem. As with the permissions associated with a stored procedure, the dependencies associated with that stored procedure are also dropped. A dependency is an object in the database that relies on another object in the database.

For example, a stored procedure that returns data out of a particular table is known to

depend on that table. When you create a stored procedure, the object IDs of any objects referenced in that stored procedure are stored in the sysdepends table in the database in which the procedure is created. The inverse of this is true as well: If you create a stored procedure or trigger that references the original procedure, that information is also stored in the sysdepends table. Later on, this information is very useful when determining what objects in the database can be dropped without affecting anything else.

The problem comes if you drop and re-create a stored procedure that is referenced by any other stored procedure.

All dependency information regarding that procedure is deleted from the sysdepends table. Even though the original object is still in the database and the old stored procedure was re-created, there is no record in the database regarding the dependency. The only way to get that dependency information back is to drop and re-create all the dependent objects, which is a big hassle. It is such a big hassle that most developers do not bother to re-create the dependency information and learn not to rely on it.

Workaround regarding dependency:

select name,id from sys.sysobjects where name = 'test_table' and type = 'U'

name id

test_table 1111675008

--Creating one SP, depends on table test_table

create proc test as

begin

select * from test_table

end

Command(s) completed successfully.

select name,id from sys.sysobjects where name = 'test' and type ='P'

name id

test 116195464

--Looking dependency info for object ‘Test’

select * from sys.sysdepends where id = 116195464

id depid

116195464 1111675008

--Creating one more SP “test2”, depends on SP ‘test’

create proc test2 as

begin

exec test

end

Command(s) completed successfully.

select name,id from sys.sysobjects where name = 'test2' and type ='P'

name id

test2 148195578

--Looking dependency info for object ‘Test2’

select id,depid from sys.sysdepends where id = 148195578

id depid

148195578 116195464

--Now dropping the stored procedure and re create with same name

drop proc test

create proc test as

begin

select * from test_table

end

select id,depid from sys.sysdepends where id = 148195578

id depid

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

(0 row(s) affected)

Viewing Dependency Information:

There are two ways that you can view dependency information available to you. One is to use the sp_depends system stored procedure.

The other way to view this information is using SQL Enterprise Manager.

The syntax of the sp_depends stored procedure is as follows :

sp_depends <object_name>

<object_name > is the name of the object on which you are trying to get information. The results of this stored procedure will give you all the objects that depend on and are dependent upon the object specified.

The other way to view the dependency information is to use SQL Enterprise Manager. To do this, open the stored procedures folder,right-click the stored procedure for which you want to view the dependencies, and select All Tasks -> View Dependencies.

This opens the Dependencies dialog box

sp_depends 'test2'

Object does not reference any object, and no objects reference it.

sp_depends 'test' (On which data base objects this object(‘test’) depends)

name type updated selected column

dbo.test_table user table no yes name

dbo.test_table user table no yes id

The Old Problems:

/** First Section **/

CREATE PROCEDURE proc_c11_1

SELECT au_lname, au_fname, phone

FROM authors

CREATE PROCEDURE proc_c11_2

EXEC proc_c11_1

GRANT EXECUTE ON proc_c11_1 TO public

sp_helprotect proc_c11_1

sp_depends proc_c11_2

/** Stop Here **/

/** Second Section **/

DROP PROCEDURE proc_c11_1

CREATE PROCEDURE proc_c11_1

SELECT au_lname, au_fname, phone, state

FROM authors

sp_helprotect proc_c11_1

sp_depends proc_c11_2

As you can see from the results in the first section, all the permissions and dependencies show up correctly. In the second section, after the proc_c11_1 procedure was dropped and re- created, all the permissions and dependency information are gone. This could be problematic to say the least, and has been the cause of many DBAs being called at odd hours of the night. That was the situation until SQL Server 7.0 was released with the ALTER PROCEDURE command.

The New Way—ALTER PROCEDURE:

With the release of SQL Server 7.0, Microsoft provided developers and DBAs with a new and very useful way to modify stored procedures.Instead of first dropping and then re-creating the stored procedure, all you have to do is use the ALTER PROCEDURE command.

This command alleviates all the issues discussed earlier.

The ALTER PROCEDURE command actually modifies a stored procedure that was previously created using the CREATE PROCEDURE command without losing the permissions or information about objects that are dependent on the procedure. Any other procedures on which the

procedure being re-created depends will also be re-created.

The syntax of the ALTER PROCEDURE command is as follows:

ALTER PROCEDURE <procedure_name>

{ <@parameter> <data type>} [VARYING] [=

default] [OUTPUT]][,...

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ]

<sql_statements>

The following are the options for the ALTER PROCEDURE command:

< procedure_name>

The name of the stored procedure that you are going to alter.

< @parameter >

If you want to pass parameters into a stored procedure, you must define them in the declaration of the stored procedure. This declaration includes the name of the parameter, the data type of the parameter, and a few other special options depending on

their use.

< data type >

If you specify a parameter, you must specify the data type of that parameter. This can be any valid data type, including text and image.

[VARYING]

This option is specified when you are returning a cursor as a parameter.

[= default ]

This option specifies a default value for a particular parameter. If

the procedure is executed without specifying a value for the parameter, this value is used instead. This can be a NULL or any other valid value for that data type. For string data, this value can include wildcards if the parameter is used in conjunction with

the LIKE parameter.

[OUTPUT]

This optional keyword specifies that the parameter is a return parameter. The value of this parameter can then be returned to the executing procedure when execution has completed. Text or image data types cannot be used as OUTPUT parameters.

[,... n ]

This symbol indicates that you can specify multiple parameters with a stored procedure. SQL Server allows up to 1,024 parameters for use in a single stored procedure.

WITH RECOMPILE

This option forces SQL Server to recompile the stored procedure every time it is executed. You should use this when you are using temporary values and objects.

WITH ENCRYPTION

This option forces SQL Server to encrypt the text of the stored procedure that is stored in the syscomments table. This enables you to create and redistribute a database without worrying about users figuring out the source code of your stored procedures.

WITH RECOMPILE,ENCRYPTION

This option forces SQL Server to recompile and encrypt the stored procedure.

This indicates that the definition of the stored procedure is about to begin.

< sql_statements>

This is a placeholder for the different statements that will make up the stored procedure.

ALTER PROCEDURE in Action

/** First Section **/

CREATE PROCEDURE proc_c11_3

SELECT au_lname, au_fname, phone

FROM authors

CREATE PROCEDURE proc_c11_4

EXEC proc_c11_3

GRANT EXECUTE ON proc_c11_3 TO public

sp_helprotect proc_c11_3

sp_depends proc_c11_4

/** Stop Here **/

/** Second Section **/

ALTER PROCEDURE proc_c11_3

SELECT au_lname, au_fname, phone, state

FROM authors

sp_helprotect proc_c11_3

sp_depends proc_c11_4

Notice that unlike the results in earlier section(old approach), after proc_c11_3 is

rebuilt using the ALTER PROCEDURE command, SQL Server has still maintained the permissions and dependency information.

Alteration Considerations:

When you alter stored procedures, there a couple of things you must take into consideration. These considerations are extremely important and if they are not taken into account, your application might stop functioning completely.

The major issue that you must consider is the question of parameters that are present in previous versions of the stored procedure. If dependent stored procedures or triggers expect to be able to pass data into the altered stored procedure, and you have modified the data types or the number or order of the parameters in the stored procedure, the dependent stored procedures or triggers will fail.

Another important issue is that you must ensure that any output parameters are handled within the dependent stored procedures. If you modify how these parameters are passed or received, you might get unexpected results during the execution.

An example of this type of unexpected result can be seen by executing the query in

Listing 5.14. Make sure to run the first section up until the

/**Stop Here**/ comment before running the second section.

Listing 5.14 Problems with ALTER PROCEDURE

/** First Section **/

CREATE PROCEDURE proc_c11_5

@intTestValue INT

PRINT @intTestValue + @intTestValue

CREATE PROCEDURE proc_c11_6

EXEC proc_c11_5 5

EXEC proc_c11_6

/** Stop Here **/

/** Second Section **/

ALTER PROCEDURE proc_c11_5

@chrTestValue CHAR(1)

PRINT @chrTestValue + @chrTestValue

EXEC proc_c11_6

In the first section, a procedure is created that accepts numeric values and adds them together. The second procedure is a wrapper of the first procedure. When it executes, it returns a value of 10.

In the second section, the first stored procedure is modified to accept a character value instead of a numeric value. When this procedure is executed after the change has been made, it returns a value of 55.

Although both these results are technically correct, they are completely different. If your applications are expecting one result and getting another one, this can cause some very difficult-to-trace problems.

A key difference between them is that ALTER PROCEDURE requires the use of the same encryption and recompile options as the original CREATE PROCEDURE statement. If you omit or change them when you execute ALTER PROCEDURE,they’ll be omitted or changed permanently in the actual procedure definition.

Passing Parameters into Stored Procedures:

With any development language, it is always important to be able to pass data into functions so that it can be processed or acted upon in some way. SQL Server is no different.

Stored Procedure Without Parameters

CREATE PROCEDURE usp_c6_1

SELECT au_lname + ', '+ au_fname, phone

FROM authors

EXEC usp_c6_1

This stored procedure returns all the names and phone numbers of all entries in the authors table. Although there are some uses for this stored procedure, it is likely more useful to be able to look up a single author's phone number. You could perform this retrieval in a couple ways. One method is to create separate stored procedures for each author's name.

One Way to Perform Lookups

CREATE PROCEDURE usp_6_2_bennet

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname = 'Bennet'

CREATE PROCEDURE usp_6_2_blotchethalls

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname = 'Blotchet-Halls'

CREATE PROCEDURE usp_6_2_yokomoto

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname = 'Yokomoto'

Although this approach would work, there are several problems with it.

First of all, it is extremely inefficient to have to create a new stored procedure every time a new author is hired. This requires a developer or some other power user who has knowledge of SQL Server to create the procedure. This approach will also clutter the database with many extra procedures. Another issue with this approach is that it does not take into account multiple authors with the same last name . In this case, if multiple authors with the same last name are contained in the database, the code will return all those authors to the user.

The last problem with this approach is that you always have to know the exact spelling of the author's name. If you don't know the exact spelling, you will not be able to access that author's information.

A better approach is to pass the author's name into a stored procedure. This enables you to create one stored procedure that returns the information you require.

Using IN Parameters

CREATE PROCEDURE usp_6_3

@vchAuthorLName VARCHAR(40)

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname = @vchAuthorLName

Executing SP Using IN Parameters:

EXEC usp_6_3 'Bennet'

EXEC usp_6_3 'Blotchet-Halls'

EXEC usp_6_3 'Yokomoto'

From the source code of this stored procedure, you can see that all you have to do to create a stored procedure that accepts a parameter is declare the stored procedure after the CREATE statement and before the code for the stored procedure.

The syntax for the declaration is as follows :

<@parameter> <data type> [,...n]

<@parameter>

< datatype >

If you specify a parameter, you must specify the data type of that parameter. This can be any valid data type, including text and image.

[,... n ]

This symbol indicates that you can specify multiple parameters with a stored procedure. SQL Server allows up to 1,024 parameters for use in a single stored procedure.

The results of the execution of this stored procedure show that it returns the same information as the stored procedures you created and executed earlier, while keeping you from cluttering your database with many redundant procedures.

There are a couple of issues with this stored procedure. First, if you do not pass in the parameter that the stored procedure is expecting,the stored procedure will fail. This is because the stored procedure requires that the user pass something in. It does not matter if the parameter that is passed in does not exist in the database, something must be there .

Problems with Parameters

EXEC usp_6_3

--- Results start here ---

Server: Msg 201, Level 16, State 1, Procedure usp_6_3, Line 0

Procedure 'usp_6_3'expects parameter '@vchAuthorLName',which was not supplied.

To solve this problem, you can add a default to the parameter. If the user does not pass any value into the stored procedure, SQL Server uses the default instead. This default value is usually very generic;

It allows the stored procedure to run, but not much more than that. In this case, you could either use an author's last name, the value NULL,or a wildcard character. Which of these options you use depends on the needs of your application. If your application always expects your stored procedure to return a value, you would probably use an author's last name. If your application can handle the stored procedure not returning any value, you could use the value NULL as the default.

Lastly, if your application can handle many results being returned from the stored procedure, you could use the wildcard character. Note that when you use the wildcard character, you also must use the LIKE keyword instead of the equal sign (=).

Using Default Parameters Values:

You can force a parameter for which a default value has been defined to use that default by omitting it altogether or by passing it the DEFAULT keyword.

You can specify NULL to supply individual parameters with NULL values.

CREATE PROCEDURE usp_6_5_1

@vchAuthorLName VARCHAR(40) = 'Bennet'

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname = @vchAuthorLName

EXEC usp_6_5_1

CREATE PROCEDURE usp_6_5_2

@vchAuthorLName VARCHAR(40) = NULL

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname = @vchAuthorLName

EXEC usp_6_5_2

CREATE PROCEDURE usp_6_5_3

@vchAuthorLName VARCHAR(40) = '%'

SELECT au_lname + ', '+ au_fname, phone

FROM authors

WHERE au_lname LIKE @vchAuthorLName

EXEC usp_6_5_3

When you type in and execute this code, you can see the major differences in the results. You must take into account how the application will process the results when choosing which method to use.

Output Parameters:

In addition to the return status code that every stored procedure supports, you can use output parameters to return other types of values from a procedure. These parameters can be integers, character strings, dates, and even cursors. An example is provided in Listing 1–28:

CREATE PROCEDURE dbo.uspGetAddressCount

@City nvarchar(30), @AddressCount int OUTPUT

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

To call this stored procedure we would execute it as follows. First we are going to declare a variable, execute the stored procedure and then select the returned valued

DECLARE @AddressCount int

EXEC dbo.uspGetAddressCount @City = 'Calgary', @AddressCount = @AddressCount OUTPUT

SELECT @AddressCount

DECLARE @AddressCount int

EXEC dbo.uspGetAddressCount 'Calgary', @AddressCount OUTPUT

SELECT @AddressCount

Ways to pass parameter Values:

Parameters can be passed to stored procedures by name or by position. An example of each method is presented in Listing 1–23:

Listing 1–23 You can pass procedure parameters by position or by name.

EXEC dbo.sp_who 'sa' --Parameter passed by Position

EXEC dbo.sp_who @loginame='sa' -- Parameter Passed by Name

Obviously, the advantage of referencing parameters by name is that you can specify them out of order.

Listing Procedure Parameters

You can list a procedure’s parameters (which include its return status code, considered

parameter 0) by querying the INFORMATION_SCHEMA.PARAMETERS view (Listing 1–29).

create procedure test @id int

begin

select 10 where @id = 10

end

exec test @id = 10

select PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE

from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME ='test'

PARAMETER_MODE PARAMETER_NAME DATA_TYPE

IN @id int

General Parameter Notes

In addition to what I’ve already said about parameters, here are a few more tips:

_ Check stored procedure parameters for invalid values early on.

_ Human-friendly names allow parameters to be passed by name more easily.

_ It’s a good idea to provide default values for parameters when you can.This makes a procedure easier to use. A parameter default can consist of a constant or the NULL value.

_ Because parameter names are local to stored procedures, you can use the same name in multiple procedures. If you have ten procedures that each take a user name parameter, name the parameter @UserName in all ten of them—for simplicity’s sake and for general consistency in your code.

_ Procedure parameter information is stored in the syscolumns system table.

_ A stored procedure can receive as many as 1,024 parameters. If you have a procedure that you think needs more parameters than 1,024, you should probably consider redesigning it.

_ The number and size of stored procedure local variables is limited only by the amount of memory available to SQL Server.

Limitations to the Use of Parameters

Many newcomers to SQL (myself included) learn about parameters, and then excitedly try out the following procedure:

CREATE PROC spListRows(

@TableName varchar(MAX)

)

-- list out rows from any table? I think not

SELECT

FROM

@TableName

This won't work! You can't use parameter values as table names, column names or in ORDER BY clauses.

Automatic Variables, a.k.a. System Functions

By their very nature, automatic variables, also known as system functions, are usually the province of stored procedures. This makes most of them germane in some way to a discussion about stored procedures. Several, in fact, are used almost exclusively in stored procedures. Table 1–1 summarizes them.

Returning Information to the User:

So far, you have primarily seen how to return results from SELECT statements to the user. With more complex stored procedures, you will find that you need to return error codes and text, contents of variables, and internal errors and exit codes that your procedure has generated during its execution. There are several different ways to return this information, with the simplest way being to return a numeric value you can use in your application.

The RETURN Statement

The RETURN statement is used to stop unconditionally the execution of a stored procedure.

No commands that follow the RETURN statement are processed after the RETURN statement is executed. When you use the RETURN statement in a stored procedure, you have the option of specifying an integer value that is returned to the application, batch,or stored procedure that executed the stored procedure. If you do not specify a value for the RETURN statement, SQL Server automatically returns a value of 0.

Almost all Microsoft's system stored procedures follow the convention of returning a value after the execution of the stored procedure.

You must decide whether this or one of the other approaches works best for your applications. One note: When using the RETURN statement, returning a value of 0 indicates that the stored procedure completed execution correctly and without encountering any problems. Returning a value other than 0 indicates that a problem occurred in the processing.

This code creates a stored procedure that searches the titles table in the pubs database for a particular title pattern. In other words, it looks for all titles that match the word or phrase that the user supplies. In this procedure, we are going to take for granted that the calling application cannot handle more than a single row of results. You must be very specific about which book you are looking for, or the application will fail.

Using the RETURN Statement

CREATE PROCEDURE usp_6_6

@vchTitlePattern VARCHAR(80) = '%'

SELECT @vchTitlePattern = '%'+ @vchTitlePattern + '%'

IF (SELECT COUNT(*) FROM titles WHERE title LIKE

@vchTitlePattern) < 1

BEGIN

RETURN 1

END

IF (SELECT COUNT(*) FROM titles WHERE title LIKE

@vchTitlePattern) > 1

BEGIN

RETURN 2

END

SELECT title, price

FROM titles

WHERE title LIKE @vchTitlePattern

RETURN 0

DECLARE @intReturnValue INT

EXEC @intReturnValue = usp_6_6 'the'

IF (@intReturnValue = 1)

BEGIN

PRINT 'There are no corresponding titles.'

END

IF (@intReturnValue = 2)

BEGIN

PRINT 'There are multiple titles that match this criteria.narrow your search.'

END

Note that when calling a stored procedure that returns a value from within a SQL script, you must call the procedure in a specific way in order to trap the return value. This is done by declaring a variable and setting the variable equal to the stored procedure when it is executed, as shown in the following code snippet:

DECLARE @intReturnValue INT

EXEC @intReturnValue = <stored procedure name>

A return code of 0 indicates success, values –1 through –14 indicate different types of failures (see the Books Online for descriptions of these), and values –15 through –99 are reserved for future use.

The PRINT Command:

Another option you have for returning information to the user is to use the PRINT command.

The PRINT function enables you to display custom messages to the user. These messages can have up to 8,000 characters and include variables. The variables can be local or global, but if they are not CHAR or VARCHAR data types, they must be changed by using either the CONVERT or CASR function.

The creation of a stored procedure that uses the PRINT function to return a message to the user. Notice that the CONVERT function is used to change the integer data type to character data types.

Using the PRINT Statement to Return Data

CREATE PROCEDURE usp_6_7

@intMinQty INT

DECLARE @intNumOrders INT

DECLARE @chrOutputText CHAR(60)

SELECT @intNumOrders = COUNT(*) FROM sales WHERE qty >@intMinQty

SELECT @chrOutputText = 'There are '+ CONVERT(VARCHAR,@intNumOrders) +

'orders that contain more than '+ CONVERT(VARCHAR,@intMinQty) +

'units.'

PRINT @chrOutputText

EXEC usp_6_7 15

EXEC usp_6_7 12

EXEC usp_6_7 8

For two reasons, when returning information from stored procedures, you should use a PRINT statement rather than a SELECT statement.

First, SELECT statements should be reserved for data processing or output.

Second, the SELECT statement uses a different type of formatting, with a line over the returned data, which makes it difficult to read a large list of returned data. The PRINT statement is useful for communicating the status or outcome of a procedure to the user, but it should not be used to send critical messages. A better solution for returning error messages is the RAISERROR function.

Errors:

The @@ERROR automatic variable returns the error code of the last Transact-

SQL statement. If there was no error, @@ERROR returns zero. Because

@@ERROR is reset after each Transact-SQL statement, you must save it to a

variable if you wish to process it further after checking it.

If you want to write robust code that runs for years without having to be

reengineered, make a habit of checking @@ERROR often in your stored procedures,

especially after data modification statements. A good indicator of

resilient code is consistent error checking, and until Transact-SQL supports

structured exception handling, checking @@ERROR frequently is the best way

to protect your code against unforeseen circumstances.

Error Messages

The system procedure sp_addmessage adds custom messages to the sysmessages

table that can then be raised (returned to the client) by the RAISERROR

command. User messages should have error numbers of 50,000 or higher. The

chief advantage of using SQL Server’s system messages facility is internationalization.

Because you specify a language ID when you add a message via sp_addmessage, you can add a separate version of your application’s messages

for each language it supports. When your stored procedures then reference a

message by number, the appropriate message will be returned to your application

using SQL Server’s current language setting.

Nesting

You can nest stored procedure calls up to 32 levels deep. Use the

@@NESTLEVEL automatic variable to check the nesting level from within a

stored procedure or trigger. From a command batch, @@NESTLEVEL

returns 0. From a stored procedure called from a command batch and from

first-level triggers, @@NESTLEVEL returns 1. From a procedure or trigger

called from nesting level 1, @@NESTLEVEL returns 2; procedures called

from level 2 procedures return level 3, and so on. Objects (including temporary

tables) and cursors created within a stored procedure are visible to all objects it

calls. Objects and cursors created in a command batch are visible to all the

objects referenced in the command batch.

Recursion

Because Transact-SQL supports recursion, you can write stored procedures

that call themselves. Recursion can be defined as a method of problem solving

wherein the solution is arrived at by repetitively applying it to subsets of the

problem. A common application of recursive logic is to perform numeric computations

that lend themselves to repetitive evaluation by the same processing

steps. Listing 1–31 presents an example that features a stored procedure that

calculates the factorial of a number:

Listing 1–31 Stored procedures can call themselves recursively.

SET NOCOUNT ON

USE master

IF OBJECT_ID('dbo.sp_calcfactorial') IS NOT NULL

DROP PROC dbo.sp_calcfactorial

CREATE PROC dbo.sp_calcfactorial @base_number decimal(38,0), @factorial

decimal(38,0) OUT

SET NOCOUNT ON

DECLARE @previous_number decimal(38,0)

IF ((@base_number>26) and (@@MAX_PRECISION<38)) OR (@base_number>32) BEGIN

RAISERROR('Computing this factorial would exceed the server''s max.

numeric precision of %d or the max. procedure nesting level of

32',16,10,@@MAX_PRECISION)

RETURN(-1)

END

IF (@base_number<0) BEGIN

RAISERROR('Can''t calculate negative factorials',16,10)

RETURN(-1)

END

IF (@base_number<2) SET @factorial=1 -- Factorial of 0 or 1=1

ELSE BEGIN

SET @previous_number=@base_number-1

EXEC dbo.sp_calcfactorial @previous_number, @factorial OUT -- Recursive

call

IF (@factorial=-1) RETURN(-1) -- Got an error, return

SET @factorial=@factorial*@base_number

IF (@@ERROR<>0) RETURN(-1) -- Got an error, return

END

RETURN(0)

DECLARE @factorial decimal(38,0)

EXEC dbo.sp_calcfactorial 32, @factorial OUT

SELECT @factorial

The procedure begins by checking to make sure it has been passed a valid

number for which to compute a factorial. It then recursively calls itself to perform

the computation. With the default maximum numeric precision of 38,

SQL Server can handle numbers in excess of 263 decillion. (Decillion is the U.S.

term for 1 followed by 33 zeros. In Great Britain, France, and Germany, 1 followed

by 33 zeros is referred to as 1,000 quintillion.) As you’ll see in Chapter

11, UDFs functions are ideal for computations like factorials.

Error Handling Mechanisum:

Let’s start with a simple statement like below which results in an exception as I am trying to access a non-existing table.

--------------Try To Access Non-Existing Table ---------------

SELECT * FROM dbo.NonExistingTable

Result of the above query:
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘dbo.NonExistingTable’.

By looking at the above error message, we can see that the error message consists of following 5 parts:

Msg 208 – Error Number
Level 16 – Severity of the Error
State 1 – State of the Error
Line 2 – Line Number of the statement which generated the Error
Invalid object name ‘dbo.NonExistingTable’. – Actual Error Message

Now Let’s wrap the above statement which generated the error into a stored procedure as below?

CREATE PROCEDURE dbo.ErrorMessageDemo

BEGIN

SELECT * FROM dbo.NonExistingTable

END

--Execute the Stored Procedure

EXEC dbo.ErrorMessageDemo

Result of executing the above stored procedure is:
Msg 208, Level 16, State 1, Procedure ErrorMessageDemo, Line 4
Invalid object name ‘dbo.NonExistingTable’.

If we compare this error message with the previous error message, then this message contains one extra part “Procedure ErrorMessageDemo“ specifying the name of the stored procedure in which the exception occurred.

ERROR STATE: Same Error can be raised for several different conditions in the code. Each specific condition that raises the error assigns a unique state code. Also the SQL Support Team uses it to find the location in the source code where that error is being raised.

create procedure usp_my_proc

if 2=2

raiserror('Error foo and bar', 16, 0);

if 3=3

raiserror('Error foo and bar', 16, 1);

exec usp_my_proc

Msg 50000, Level 16, State 0, Procedure usp_my_proc, Line 4

Error foo and bar

Msg 50000, Level 16, State 1, Procedure usp_my_proc, Line 6

Error foo and bar

Now let us see how Sql Server Reacts to different errors. To demonstrate this let us create a New Database and table as shown below:

--Create a New database for the Demo

CREATE DATABASE SqlHintsErrorHandlingDemo

USE SqlHintsErrorHandlingDemo

CREATE TABLE dbo.Account (AccountId INT NOT NULL PRIMARY KEY,

Name NVARCHAR (50) NOT NULL,

Balance Money NOT NULL CHECK (Balance>=0)

)

As the Account table has Primary Key on the AccountId column, so it will raise an error if we try to duplicate the AccountId column value. And the Balance column has a CHECK constraint Balance>=0, so it will raise an exception if the value of Balance is <0.

Let us first check whether we are able to insert valid Account into the Account table.

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

Result: We are able to successfully insert a record in the Account table

Now try to insert one more account whose AccountId is same as the one which we have just inserted above.

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000)

Result: It fails with below error message, because we are trying to insert a duplicate value for the the Primary Key column AccountId.

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint ‘PK__Account__349DA5A67ED5FC72′. Cannot insert duplicate key in object ‘dbo.Account’. The duplicate key value is (1).

The statement has been terminated.

Let me empty the Account Table by using the below statement:

DELETE FROM dbo.Account

DEMO 1: Now let us see what will be the result if we execute the below batch of Statements:

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 2

Violation of PRIMARY KEY constraint 'PK__Account__349DA5A61352D76D'. Cannot insert duplicate key in object 'dbo.Account'.

The statement has been terminated.

(1 row(s) affected)

Result: The First and Third Insert statements in the batch are succeeded even though the Second Insert statement fails

From the above example result it is clear that even though the Second insert statement is raising a primary key voilation error,

Sql server continued the execution of the next statement and it has successfully inserted the Account with AccountId 2 by the third Insert statement.

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination.

Let me clear the Account Table by using the below statement

DELETE FROM dbo.Account

DEMO 2: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is the first line i.e. SET XACT_ABORT ON:

SET XACT_ABORT ON

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)

RESULT: Only the first Insert succeeded

From the above example result it is clear that failure in the Second insert statement due to primary key violation caused Sql Server to terminate the execution of the Subsequent statements in the batch.

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion.

The Only difference in the DEMO 2 script from DEMO 1 is the additional first statement SET XACT_ABORT ON. So from the result it is clear that the SET XACT_ABORT ON statement is causing Sql Server to do the Batch Abortion for a Statement Termination Error. It means SET XACT_ABORT ON converts the Statement Terminating errors to the Batch Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account

SET XACT_ABORT OFF

DEMO 3: Now let us see what will be the result if we execute the below batch of Statements.

The only difference between this batch of statement and the DEMO 1 is that the INSERT statements are executed in a Transaction:

BEGIN TRAN

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)

COMMIT TRAN

RESULT: Same as the DEMO 1, that is only the statement which raised the error is terminated but continues with the next statement in the batch. Here First and Third Inserts are Successful even though the Second statement raised the error.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account

DEMO 4: Now let us see what will be the result if we execute the below batch of Statements.

The only difference between this batch of statement and the DEMO 2 is that the INSERT statement’s are executed

within a Transaction

SET XACT_ABORT ON

BEGIN TRAN

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000)

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)

COMMIT TRAN

RESULT: No records inserted

From the above example result it is clear that SET XACT_ABORT ON setting not only converts the Statement Termination Errors to the Batch Abortion Errors and also ROLLS BACK any active transactions started prior to the BATCH Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account

SET XACT_ABORT OFF

DEMO 5: As a part of this DEMO we will verify what happens if a CONVERSION Error occurs within a batch of statement.

CONVERSION ERROR: Trying to convert the string ‘TEN THOUSAND’ to MONEY Type will result in an error.

Let us see this with an example:

SELECT CAST('TEN THOUSAND' AS MONEY)

RESULT:

Msg 235, Level 16, State 0, Line 1

Cannot convert a char value to money. The char value has incorrect syntax.

Now let us see what happens if we come across such a CONVERSION error within a batch of statement like the below one:

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY)

WHERE AccountId = 1

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000) GO

RESULT: Only the First INSERT is successful

From the above result it is clear that CONVERSION errors cause the BATCH abortion,

i.e Sql Server terminates the statement which raised the error and the subsequent statements in the batch. Where as PRIMARY KEY violation was resulting in a Statement Termination as explained in the DEMO 1.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account

DEMO 6: Now let us see what will be the result if we execute the below batch of Statements.

The only difference between this batch of statement and the previous DEMO 5 is that the Batch statement’s are executed within a Transaction

BEGIN TRAN

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)

UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY)

WHERE AccountId = 1

INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)

COMMIT TRAN

RESULT: No records inserted

From the above example result it is clear that CONVERSION errors results in a BATCH Abortion and BATCH Abortion errors ROLLS BACK any active transactions started prior to the BATCH Abortion error.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account

SET XACT_ABORT OFF

Permissions Workaround:

create proc test as

begin

select 10

end

select object_id,name,schema_id from sys.objects where name ='test' and type ='P'

object_id name schema_id

196195749 test 1

select * from sys.schemas where schema_id = 1

name schema_id principal_id

dbo 1 1

select name,principal_id,type_desc,default_schema_name from sys.database_principals

where principal_id = 1

name principal_id type_desc default_schema_name

dbo 1 WINDOWS_USER dbo

SELECT s.name as 'schema name'

,o.name 'object name'

,(select name from sys.database_principals dp1 where dp1.principal_id =pe.grantee_principal_id) as grantee

,pe.permission_name

FROM sys.database_principals AS pr

JOIN sys.database_permissions AS pe

ON pe.grantor_principal_id = pr.principal_id

JOIN sys.objects AS o

ON pe.major_id = o.object_id

JOIN sys.schemas AS s

ON o.schema_id = s.schema_id

where o.name = 'test' and o.type = 'P'

schema name object name grantee permission_name

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

(0 row(s) affected)

GRANT EXECUTE ON test TO DBO

Error Message:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

GRANT EXECUTE ON test TO public

Command(s) completed successfully.

SELECT s.name as 'schema name'

,o.name 'object name'

,(select name from sys.database_principals dp1 where dp1.principal_id =pe.grantee_principal_id) as grantee

,pe.permission_name

FROM sys.database_principals AS pr

JOIN sys.database_permissions AS pe

ON pe.grantor_principal_id = pr.principal_id

JOIN sys.objects AS o

ON pe.major_id = o.object_id

JOIN sys.schemas AS s

ON o.schema_id = s.schema_id

where o.name = 'test' and o.type = 'P'

Schema name object name grantee permission_name

dbo test public EXECUTE

IF OBJECT_ID('test') is not null

drop proc test

create proc test as

begin

select 100

end

Command(s) completed successfully.

SELECT s.name as 'schema name'

,o.name 'object name'

,(select name from sys.database_principals dp1 where dp1.principal_id =pe.grantee_principal_id) as grantee

,pe.permission_name

FROM sys.database_principals AS pr

JOIN sys.database_permissions AS pe

ON pe.grantor_principal_id = pr.principal_id

JOIN sys.objects AS o

ON pe.major_id = o.object_id

JOIN sys.schemas AS s

ON o.schema_id = s.schema_id

where o.name = 'test' and o.type = 'P'

schema name object name grantee permission_name

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

(0 row(s) affected)

Performance Boost Up:

Use NOCOUNT

Microsoft SQL Server offers a set option called NOCOUNT. It's turned off by default so that each operation returns information regarding the number of rows affected. However, applications don’t need this information. If you turn on the NOCOUNT option, stored procedures won’t return row-count information—and therefore, you’ll save the network overhead involved with communicating that information to the client. To set NOCOUNT, simply insert SET NOCOUNT ON as the first statement in the stored procedure

Use schema name with object name:

The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure.

This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance.

Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method

--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

Do not use the prefix “sp_” in the stored procedure name:

If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use IF EXISTS (SELECT 1) instead of (SELECT *):

To check the existence of a record in another table, we uses the IF EXISTS clause.

The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

Use the sp_executesql stored procedure instead of the EXECUTE statement:
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

Try to avoid using SQL Server cursors whenever possible:

Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.

Keep the Transaction as short as possible:

The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.

Use TRY-Catch for error handling:

Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Use return values:

Queries are often used to verify a piece of information or simply return a single value. When performing code reviews, I have frequently seen an entire row of data returned from a database when only a single integer value was needed. Whenever you need a single value from a query or update, consider using the return value of the stored procedure.

Using a return value is particularly useful when you’re inserting a new record. In terms of code, the only information necessary is the primary key value. To utilize the return values in the stored procedure, simply place the “RETURN <value>” statement at the end of the query as the last command.

XACT_ABORT Behaviour:

Explicit error with raiserror:

create table #t(i int);

insert #t values(1);

(1 row(s) affected)

select * from #t

-----------

(1 row(s) affected)

create procedure sp

set xact_abort on

begin tran

raiserror ('x', 16, 1);

print 'deleting';

delete #t;

commit;

Command(s) completed successfully.

exec sp

Msg 50000, Level 16, State 1, Procedure sp, Line 5

deleting

(1 row(s) affected)

select * from #t

-----------

(0 row(s) affected)

Primary Constraints Error:

create table #t(i int primary key);

insert #t values(1);

(1 row(s) affected)

select * from #t

-----------

(1 row(s) affected)

create procedure sp

set xact_abort on

begin tran

insert #t values(1);

print 'deleting';

delete #t;

commit;

Command(s) completed successfully.

exec sp

Msg 2627, Level 14, State 1, Procedure sp, Line 5

Violation of PRIMARY KEY constraint 'PK__#t________3BD019965FB337D6'. Cannot insert duplicate key in object 'dbo.#t'.

select * from #t

-----------

(1 row(s) affected)

Conclusion:

Explicit RAISERRORs don't abort the batch

System Objects versus System Procedures

Extended Stored Procedures

Internal Procedures

My Sql Note

Pages

Wednesday, 17 August 2016

sp

No comments:

Post a Comment