Pages

▼

Wednesday, 20 November 2013

SP

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
GO

SELECT *
FROM authors
GO

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
as
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:

GO
set nocount on
GO

create proc rule_test
as
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 :


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

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 VIEW,
      and CREATE RULE.

create proc rule_test
as
begin
select city from AdventureWorks2008.person.Address where AddressID = 535
create proc test as
begin
select 10
end
end

Msg 156, Level 15, State 1, Procedure rule_test, Line 6
Incorrect syntax near the keyword 'proc'.

Alternative Way:

create proc rule_test
as
begin
select city from AdventureWorks2008.person.Address where AddressID = 535
exec('create proc test as
      begin
      select 10
      end')
end

            Command(s) completed successfully.


  • 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



Referencing Objects in Stored Procedures:

When you create a stored procedure, object names that are referenced inside the stored procedure are assumed to be owned by the creator of the stored procedure.

If the creator of the stored procedure does not use the fully qualified names of the objects referenced in the stored procedure, access to that stored procedure is restricted to the user who created it.

When statements such as ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX,UPDATE STATISTICS, and DBCC are used, you must qualify the names of the objects if other users will use the procedure. This is because SQL Server does not resolve the names of the tables until the procedure is actually run. To alleviate problems with this, you should
ensure that all the objects in your database are owned by the same person.

For those of you who have worked in other databases, such as Oracle, this may seem like an extremely unwise thing to do from a security standpoint. Let me assure you that you can keep adequate security with SQL Server even with all objects having the same owner.

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
AS
SELECT *
FROM authors
GO
sp_helptext uspTestProcedure
GO
ALTER PROCEDURE uspTestProcedure
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
sp_helptext uspTestProcedure
GO

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.


ODBC Options:

When an ODBC application first connects to SQL Server, it will set several options that might affect the way your stored procedures operate. Those options are as follows:

  SET QUOTED_IDENTIFIER ON
  SET TEXTSIZE 2147483647
  SET ANSI_DEFAULTS ON
  SET CURSOR_CLOSE_ON_COMMIT OFF
  SET IMPLICIT_TRANSACTIONS OFF

Because non-ODBC applications do not set these options, you should test every stored procedure that will interact with ODBC applications.

Test with these options both on and off to ensure that the application will work exactly the way you expect. If a particular stored procedure needs one of these options set differently than the way it is set by ODBC, you must issue the SET command at the beginning of the stored procedure. That SET statement will be in effect only until the execution of the stored procedure has completed. After the procedure has completed its execution, the server reverts to the original settings.

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 
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
go
create proc test as
begin
select * from test_table
end
go

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
AS
SELECT au_lname, au_fname, phone
FROM authors
GO

CREATE PROCEDURE proc_c11_2
AS
EXEC proc_c11_1
GO

GRANT EXECUTE ON proc_c11_1 TO public
GO

sp_helprotect proc_c11_1
GO

sp_depends proc_c11_2
GO

/** Stop Here **/


/** Second Section **/
DROP PROCEDURE proc_c11_1
GO

CREATE PROCEDURE proc_c11_1
AS
SELECT au_lname, au_fname, phone, state
FROM authors
GO

sp_helprotect proc_c11_1
GO

sp_depends proc_c11_2
GO

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.

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'
go


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'
go


Schema name object name grantee     permission_name

dbo               test        public      EXECUTE


IF OBJECT_ID('test') is not null
drop proc test
go
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'
go

schema name        object name           grantee      permission_name
---------------------------------------------------------------------

(0 row(s) affected)


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]][,...
n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ]
AS
<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.

AS
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
AS
SELECT au_lname, au_fname, phone
FROM authors
GO

CREATE PROCEDURE proc_c11_4
AS
EXEC proc_c11_3
GO

GRANT EXECUTE ON proc_c11_3 TO public
GO

sp_helprotect proc_c11_3
GO

sp_depends proc_c11_4
GO

/** Stop Here **/

/** Second Section **/
ALTER PROCEDURE proc_c11_3
AS
SELECT au_lname, au_fname, phone, state
FROM authors
GO


sp_helprotect proc_c11_3
GO

sp_depends proc_c11_4
GO

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
AS
PRINT @intTestValue + @intTestValue
GO

CREATE PROCEDURE proc_c11_6
AS
EXEC proc_c11_5 5
GO

EXEC proc_c11_6
GO
/** Stop Here **/

/** Second Section **/
ALTER PROCEDURE proc_c11_5
@chrTestValue CHAR(1)
AS
PRINT @chrTestValue + @chrTestValue
GO
EXEC proc_c11_6
GO

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.


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
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
GO

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
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname = 'Bennet'
GO

CREATE PROCEDURE usp_6_2_blotchethalls
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname = 'Blotchet-Halls'
GO

CREATE PROCEDURE usp_6_2_yokomoto
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname = 'Yokomoto'
GO

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 Parameters

CREATE PROCEDURE usp_6_3
@vchAuthorLName VARCHAR(40)
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname = @vchAuthorLName
GO

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>
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.

< 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

CREATE PROCEDURE usp_6_5_1
@vchAuthorLName VARCHAR(40) = 'Bennet'
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname = @vchAuthorLName
GO

EXEC usp_6_5_1
GO

CREATE PROCEDURE usp_6_5_2
@vchAuthorLName VARCHAR(40) = NULL
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname = @vchAuthorLName
GO

EXEC usp_6_5_2
GO

CREATE PROCEDURE usp_6_5_3
@vchAuthorLName VARCHAR(40) = '%'
AS
SELECT au_lname + ', '+ au_fname, phone
FROM authors
WHERE au_lname LIKE @vchAuthorLName
GO

EXEC usp_6_5_3
GO
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.

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) = '%'
AS
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
GO

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
GO


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>


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
AS
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

GO
EXEC usp_6_7 15
GO
EXEC usp_6_7 12
GO
EXEC usp_6_7 8
GO

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.



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.



Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter

Here is the question – How to Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter. Stored Procedures are very old concepts and every day I see more and more adoption to Stored Procedure over dynamic code.

When we have almost all of our code in Stored Procedure it is very common requirement that we have need of one stored procedure’s result to be passed as another stored procedure’s parameter.

Let us try to understand this with a simple example. Please note that this is a simple example, the matter of the fact, we can do the task of these two stored procedure in a single SP but our goal of this blog post is to understand how we can pass the result of one SP to another SP as a parameter.

Let us first create one Stored Procedure which gives us square of the passed parameter.

-- First Stored Procedure
CREATE PROCEDURE SquareSP
@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam
-- Additional Code
RETURN (@MyFirstParamSquare)
GO

Now let us create second Stored Procedure which gives us area of the circle.

-- Second Stored Procedure
CREATE PROCEDURE FindArea
@SquaredParam INT
AS
DECLARE @AreaofCircle FLOAT
SELECT @AreaofCircle = @SquaredParam * PI()
RETURN (@AreaofCircle)
GO

You can clearly see that we need to pass the result of the first stored procedure (SquareSP) to second stored procedure (FindArea).

We can do that by using following method:

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter

DECLARE @ParamtoPass INT, @CircleArea FLOAT
-- First SP
EXEC @ParamtoPass = SquareSP 5
-- Second SP
EXEC @CircleArea = FindArea @ParamtoPass
SELECT @CircleArea FinalArea
GO

You can see that it is extremely simple to pass the result of the first stored procedure to second procedure.
You can clean up the code by running the following code.
-- Clean up
DROP PROCEDURE SquareSP
DROP PROCEDURE FindArea
GO































































Stored procedures are managed by means of the data definition language (DDL) commands: CREATE,ALTER, and DROP.

CREATE must be the first command in a batch; the termination of the batch ends the creation of the stored procedure.

The following example creates a very simple stored procedure that retrieves data from the ProductCategory table in the AdventureWorks2008 database:

use AdventureWorks2008
go
create procedure test as
begin
select * from Production.ProductCategory
end

Dropping a stored procedure removes it from the database. Altering a stored procedure replaces the entire existing stored procedure with new code.

When modifying a stored procedure, altering it is preferable to dropping and recreating it, because the latter method removes any permissions.

Executing a stored procedure:

When calling a stored procedure within a SQL batch, the EXECUTE command executes the stored procedure with a few special rules. EXECUTE is typically coded as EXEC.

If the stored-procedure call is the first line of a batch (and if it’s the only line, then it’s also the first line), the stored-procedure call doesn’t require the EXEC command. However, including it anyway won’t cause any problems and it prevents an error if the code is cut and pasted later.

The following two-system stored procedure calls demonstrate the use of the EXEC command within a batch:

test;
EXEC test;

Compiling stored procedures
N/A

Stored procedure encryption

When the stored procedure is created, its text is saved in a system table. The text is not stored for the execution of the stored procedure, but only so that it may be retrieved later if it needs to be modified.

The sp_helptext system stored procedure will extract the original text of the stored procedure:

EXEC sp_helptext ‘test’;

Result:
Text
CREATE procedure test  
as  
begin 
select * from Production.ProductCategory 
end
If the stored procedure is created with the WITH ENCRYPTION option, the stored procedure text is not directly readable.

It’s common practice for third-party vendors to encrypt their stored procedures. The following ALTER command stores the “test” procedure with WITH ENCRYPTION and then attempts to read the code:

alter procedure test
with encryption
as
begin
select * from Production.ProductCategory
end

EXEC sp_helptext ‘test’;

Result:
The text for object ‘test’ is encrypted.

SP Types:

System stored procedures

These stored procedure are already defined in Sql Server. These are physically stored in hidden Sql Server Resource Database(Master data base) and logically appear in the sys schema of each user defined and system defined database. These procedure starts with the sp_ prefix. Hence we don't use this prefix when naming user-defined procedures. 

Here is a list of some useful system defined procedure.

sp_rename
It is used to rename an database object like stored procedure,views,table etc.
sp_changeowner
It is used to change the owner of an database object.
sp_help
It provides details on any database object.
sp_helpdb
It provide the details of the databases defined in the Sql Server.
sp_helptext
It provides the text of a stored procedure reside in Sql Server
sp_depends
It provide the details of all database objects that depends on the specific database object.


Any procedures beginning with sp_ in the master database can be executed from any database.

If a name conflict exists between a system stored procedure and a stored procedure in the local user database, the system stored procedure in the local database is executed.

Best Practice
When creating stored procedures, use a consistent naming convention other than sp_ to name your stored procedures.

Using sp_ can only cause name conflicts and confusion. I sometimes add the p prefix to the names of stored procedures, but even no prefix is better than sp_.



Extended Procedure

Extended procedures provide an interface to external programs for various maintenance activities. 
These extended procedures starts with the xp_ prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.

Example Below statements are used to log an event in the NT event log of the server without raising any error on the client application.

1.    declare @logmsg varchar(100)
2.    
3.   set @logmsg = suser_sname() + ': Tried to access the dotnet system.'
4.    
5.   exec xp_logevent 50005, @logmsg
6.    
7.   print @logmsg
8.    

Example The below procedure will display details about the BUILTIN\Administrators Windows group.
EXEC xp_logininfo 'BUILTIN\Administrators'

User Defined SP:
These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.

CLR Stored Procedure:
CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#.

We can nest stored procedures and managed code references in Sql Server up to 32 levels only. This is also applicable for function, trigger and view.

The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.

In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a stored procedure
Using stored procedures within queries
Executing remote stored procedures

Passing Data to Stored Procedures
A stored procedure is more useful when it can be manipulated by parameters.

The test stored procedure created previously returns all the product categories, but a procedure that performs a task on an individual row requires a method for passing the ProductCategory ID to the procedure.

SQL Server stored procedures may have numerous input and output parameters (SQL Server 2005 increased the number of parameters from 1,024 to 2,100 to be exact).

Input parameters

You can add input parameters that pass data to the stored procedure by listing the parameters after the procedure name in the CREATE PROCEDURE command. Each parameter must begin with an @ sign,and becomes a local variable within the procedure. Like local variables, the parameters must be defined with valid data types. When the stored procedure is called, the parameter must be included (unless the parameter has a default value).

The following code sample creates a stored procedure that returns a single product category. The @ ProductCategoryID parameter can accept integet input.

The value passed by means of the parameter is available within the stored procedure as the variable @ ProductCategoryID in the WHERE clause:

use AdventureWorks2008
go
alter procedure test(@ProductCategoryID int)
as
begin
select * from Production.ProductCategory
where ProductCategoryID=@ProductCategoryID
end

When the following code sample is executed, the integet value 3 is passed to the stored procedure and substituted for the variable in the WHERE clause:

EXEC test 3

Result:
ProductCategoryID    Name                     rowguid                ModifiedDate
3        Clothing 10A7C342-CA82-48D4-8A38-46A2EB089B74    1998-06-01 00:00:00.000

If multiple parameters are involved, the parameter name can be specified in any order, or the parameter values listed in order. If the two methods are mixed, then as soon as the parameter is provided by name, all the following parameters must be as well.

Parameter defaults

You must supply every parameter when calling a stored procedure, unless that parameter has been created with a default value.

You establish the default by appending an equal sign and the default to the
parameter, as follows:

CREATE PROCEDURE Schema.StoredProcedure (
@Variable DataType = DefaultValue
)

use AdventureWorks2008
go
alter procedure test(@ProductCategoryID int =1 )
as
begin
select * from Production.ProductCategory
where ProductCategoryID=@ProductCategoryID
end
If u execute a SP “test” without an input parameter 1 will be passed,If u pass any value then that value will overwrite the default value.
Exec test

ProductCategoryID    Name rowguid    ModifiedDate
1    Bikes      CFBDA25C-DF71-47A7-B81B-64EE161AA37C 1998-06-01 00:00:00.000

EXEC TEST 3

ProductCategoryID    Name rowguid    ModifiedDate
3    Clothing   10A7C342-CA82-48D4-8A38-46A2EB089B74 1998-06-01 00:00:00.000

Table-valued parameters
New to SQL Server 2008 are table-valued parameters (TVPs). The basic idea is that a table can be created and populated in the client application or T-SQL and then passed, as a table variable, into a stored procedure or user-defined function.

In every complex application I’ve worked on, there’s been a requirement that a complex transaction be passed to the database — orders and order details, flights and flight legs, or an object and a dynamic set of attributes. In each case, the complete transaction includes multiple types of items.

Without considering TVPs, there are three traditional ways to solve the problem of how to pass multiple types of items in a transaction to SQL Server — none of them particularly elegant:

■ Pass each item in a separate stored procedure call — AddNewOrder, AddNewOrder
Details — repeatedly for every item, and then CloseNewOrder.

This method has two primary problems.
First, it’s very chatty(Expensive), with multiple trips to the server.

Second, if the client does not complete the transaction, a process on the server must clean up the remains of the unfinished transaction.

■ Package the entire transaction at the client into XML, pass the XML to SQL Server, and shred the XML into the relational tables. This solution is significantly cleaner than the first method,but there’s still the extra work of shredding the data in the server.

■ Concatenate any data that includes multiple rows into a comma-delimited string and pass that as a varchar(max) parameter into the stored procedure. Like the XML solution, this method does achieve a single stored procedure call for the complex transaction, but it involves even more coding complexity than the XML method and will prove to be difficult to maintain.

Table-valued parameters provide a fourth alternative. A table can be created in ADO.NET 3.5 or in T-SQL and passed into a SQL Server stored procedure or user-defined function.

The first step is to define a table type in SQL Server for consistency when creating the TVP:

CREATE TYPE tvp_test AS Table (
Seq_no int identity(1,1),
Id int
);

Once the table type is created, it can be seen in Object Explorer in the Database ➪ Programmability ➪ Types ➪ User-Defined Table Types node.

With the table type established, the stored procedure can now be created that references the table type.

The table must be defined as the table type name with the READONLY option. The TVP parameter will look like a table variable inside the stored procedure.Code can reference the data as a normal other data source except that it’s read-only. Some have criticized this as a major drawback. I don’t see it that way. If I want to modify the data and return it, I think the best way is to return the data as a selected result set.

use AdventureWorks2008
go
create procedure test(@var tvp_test readonly)
as
declare @ProductCategoryID int,
@max_count int,
@count int
begin
set @count = 1
select @max_count = COUNT(*) from @var
while(@count <= @max_count)
begin
select @ProductCategoryID = Id from @var
where seq_no = @count
set @count = @count+1
select * from Production.ProductCategory
where ProductCategoryID=@ProductCategoryID
END
END

Proc execution TVP as input argument:

declare
@var as tvp_test
insert into @var
select 1
union all select 2
union all select 3
union all select 4
EXEC TEST @var
Go

at

No comments:

Post a Comment