Store Procedures in SQL.

                                

Outline

A put away method is just arranged SQL code that you spare so you can reuse the code again and again. So in the event that you consider a question that you compose again and again, rather than writing that inquiry every time you would spare it as a put away methodology and after that simply call the put away strategy to execute the SQL code that you spared as a major aspect of the put away technique.

Notwithstanding running the same SQL code again and again you likewise can pass parameters to the put away system, so relying upon what the need is the put away method can act in like manner in light of the parameter values that were passed.

Investigate each of these points to figure out how to begin with put away strategy advancement for SQL Server.


You can either utilize the framework on the left or tap on the bolts to one side or beneath to look through each of these subjects.

There are different choices that can be utilized to make put away techniques. In these next couple of subjects we will talk about making a basic put away strategy to more propelled alternatives that can be utilized when making put away systems.

As said in the instructional exercise review a put away technique is just put away SQL code that you might want to use again and again. In this case we will take a gander at making a straightforward put away method.

Clarification

Before you make a put away technique you have to comprehend what your final product is, whether you are selecting information, embeddings information, and so on..

In this straightforward case we will simply choose all information from the Person.Address table that is put away in the AdventureWorks database.

So the basic T-SQL code excuting in the AdventureWorks database would be as per the following which will give back all columns from this table.

SELECT * FROM Person.Address

To make a put away system to do this the code would appear as though this:

USE AdventureWorks

GO

Make PROCEDURE dbo.uspGetAddress

AS

SELECT * FROM Person.Address

GO

To call the methodology to give back the substance from the table indicated, the code would be:

Executive dbo.uspGetAddress

- or

Executive uspGetAddress

- or just essentially

uspGetAddress

While making a put away methodology you can either utilize CREATE PROCEDURE or CREATE PROC. After the put away methodology name you have to utilize the watchword "AS" and after that the rest is only the consistent SQL code that you would typically execute.

One thing to note is that you can't utilize the catchphrase "GO" in the put away system. Once the SQL Server compiler sees "GO" it accept it is the end of the clump.

Additionally, you can not change database setting inside the put away technique, for example, utilizing "USE dbName" the purpose behind this is on the grounds that this would be a different bunch and a put away strategy is an accumulation of stand out cluster of articulations.

The genuine force of put away techniques is the capacity to pass parameters and have the put away system handle the contrasting solicitations that are made.
In this subject we will take a gander at passing parameter qualities to a put away method.

Clarification

Much the same as you can utilize parameters with your SQL code you can likewise setup your put away strategies to acknowledge one or more parameter qualities.

One Parameter

In this illustration we will question the Person.Address table from the AdventureWorks database, however as opposed to getting back all records we will restrain it to only a specific city. This illustration accept there will be a careful match on the City esteem that is passed.

USE AdventureWorks

GO

Make PROCEDURE dbo.uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM Person.Address

WHERE City = @City

GO

To call this put away strategy we would execute it as takes after:

Executive dbo.uspGetAddress @City = 'New York'

We can likewise do likewise, however permit the clients to give us a beginning stage to look the information. Here we can change the "=" to a LIKE and utilize the "%" trump card.

USE AdventureWorks

GO

Make PROCEDURE dbo.uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM Person.Address

WHERE City LIKE @City + "%"

GO

In both of the procedure illustrations it accept that a parameter quality will dependably be passed. On the off chance that you attempt to execute the system without passing a parameter esteem you will get a blunder message, for example, the accompanying:

Msg 201, Level 16, State 4, Procedure uspGetAddress, Line 0

System or capacity "uspGetAddress" expects parameter '@City', which was not supplied.

Default Parameter Values

As a rule it is dependably a decent practice to go in all parameter values, yet some of the time it is unrealistic. So in this case we utilize the NULL choice to permit you to not go in a parameter esteem. In the event that we make and run this put away method as is it won't give back any information, since it is searching for any City values that equivalent NULL.

USE AdventureWorks

GO

Make PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL

AS

SELECT *

FROM Person.Address

WHERE City = @City

GO

We could transform this put away method and utilize the ISNULL capacity to get around this. So if a worth is passed it will utilize the quality to contract the outcome set and if a worth is not passed it will give back all records. (Note: if the City section has NULL values this wo exclude these qualities. You will need to include extra rationale for City IS NULL)

USE AdventureWorks

GO

Make PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL

AS

SELECT *

FROM Person.Address

WHERE City = ISNULL(@City,City)

GO

Numerous Parameters

Setting up numerous parameters is anything but difficult to do. You simply need to list every parameter and the information sort isolated by a comma as demonstrated as follows.

USE AdventureWorks

GO

Make PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL

AS

SELECT *

FROM Person.Address

WHERE City = ISNULL(@City,City)

Furthermore, AddressLine1 LIKE "%" + ISNULL(@AddressLine1 ,AddressLine1) + "%"

GO

To execute this you could do any of the accompanying:

Executive dbo.uspGetAddress @City = "Calgary"

- or

Executive dbo.uspGetAddress @City = 'Calgary', @AddressLine1 = "A"

- or

Executive dbo.uspGetAddress @AddressLine1 = "Acardia"

- and so forth...

In a past point we talked about how to pass parameters into a put away strategy, however another choice is to pass parameter values retreat from a put away method. One choice for this might be that you call another put away technique that does not give back any information, but rather returns parameter qualities to be utilized by the calling put away system.

Clarification

Setting up yield paramters for a put away system is fundamentally the same as setting up information parameters, the main contrast is that you utilize the OUTPUT provision after the parameter name to determine that it ought to give back a worth. The yield condition can be indicated by either utilizing the watchword "Yield" or simply "OUT". For these cases we are as yet utilizing the AdventureWorks database, so all the put away strategies ought to be made in the AdventureWorks database.

Basic Output

Make PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

On the other hand it should be possible along these lines:

Make PROCEDURE dbo.uspGetAddressCount @City nvarchar(30), @AddressCount int OUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

To call this put away technique we would execute it as takes after. To begin with we are going to announce a variable, execute the put away methodology and after that select the returned esteemed.

Proclaim @AddressCount int

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

SELECT @AddressCount

This should likewise be possible as takes after, where the put away methodology parameter names are not passed.

Announce @AddressCount int

Executive dbo.uspGetAddressCount 'Calgary', @AddressCount OUTPUT

SELECT @AddressCount

An incredible new alternative that was included SQL Server 2005 was the capacity to utilize the Try..Catch worldview that exists in other advancement dialects. Doing mistake taking care of in SQL Server has not generally been the simplest thing, so this alternative unquestionably makes it much less demanding to code for and handle blunders.

Clarification

In the event that you are not acquainted with the Try...Catch worldview it is essentially two squares of code with your put away techniques that gives you a chance to execute some code, this is the Try area and if there are blunders they are taken care of in the Catch segment.

We should investigate a case of how this should be possible. As should be obvious we are utilizing a fundamental SELECT proclamation that is contained inside the TRY area, yet for reasons unknown in the event that this falls flat it will run the code in the CATCH segment and give back the blunder data.

Make PROCEDURE dbo.uspTryCatchTest

AS

Start TRY

SELECT 1/0

END TRY

Start CATCH

SELECT ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

One exceptionally accommodating thing to do with your put away methodology is to add remarks to your code. This helps you to realize what was done and why for future reference, additionally helps different DBAs or engineers that may need to make alterations to the code.

Clarification

SQL Server offers two sorts of remarks in a put away method; line remarks and piece remarks. The accompanying cases demonstrate to you proper methodologies to include remarks utilizing both procedures. Remarks are shown in green in a SQL Server inquiry window.

Line Comments
'
To make line remarks you simply utilize two dashes "- - " before the code you need to remark. You can remark out one or various lines with this strategy.

In this illustration the whole line is remarked out.

- this system gets a rundown of locations based

- on the city esteem that is passed

Make PROCEDURE dbo.uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM Person.Address

WHERE City = @City

GO

This next illustration demonstrates to you proper methodologies to put the remark on the same line.

- this methodology ge