Griffiths web design based in Royston, Hertfordshire, United Kingdom

 

An introduction into stored procedures in MS SQL Server

Author:   Michael Griffiths

Date created:   29 July 2016

[postpic=discoverSql.png class=img-responsive center-block title=Sql Server Stored Procedures alt=Sql Server Stored Procedures style=STYLE][/postpic] [hdmd]So what is a stored procedure?[/hdmd] [l]A stored procedure is just a plain old database query, not unlike any other query, with one exception. A stored procedure is intended to be run over and over again. Have you ever had a query that has been used multiple times in the same project? Then that right there is an ideal candidate for a stored procedure. You can just call the sp (that’s what we will call stored procedures for the rest of this article) when and where you need it, saving yourself endless amounts of time and repetition. In addition to sp’s being run over and over again you can also pass them parameters which enables you to create intelligent queries than can adapt to the requirements.[/l] [hdmd]The advantages of stored procedures[/hdmd] [def] [term][l]Maintainability[/l][/term] [desc][l]Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier[/l][/desc] [term][l]Testing[/l][/term] [desc][l]Can be tested independent of the application[/l][/desc] [term][l]Isolation of Business Rules [/l][/term] [desc][l]Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application[/l][/desc] [term][l]Speed / Optimization[/l][/term] [desc][l]Stored procedures are cached on the server Execution plans for the process are easily reviewable without having to run the application[/l][/desc] [term][l]Utilization of Set-based Processing[/l][/term] [desc][l]The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower[/l][/desc] [term][l]Security[/l][/term] [desc][l]Limit direct access to tables via defined roles in the database Provide an “interface” to the underlying data structure so that all implementation and even the data itself is shielded. Securing just the data and the code that accesses it is easier than applying that security within the application code itself[/l][/desc] [/def] [hdmd]When Should You Use Stored Procedures?[/hdmd] [l]Stored Procedures may not always be the right answer for processing data, but there’s also not enough compelling evidence to not use them either. Whether or not to use them determines on your particular situation and ability to develop the Stored Procedure(s) to match. Just like with writing a good, quality application, if you or your developers can write good, quality Stored Procedures, then by all means implement them. If they can’t, then another solution might be best for you. Stored procedures are good for common CRUD operations. For example this blog uses sp’s to add/modify/delete blog posts. These tasks are a perfect fit for sp’s because they are operations that are performed regularly and the database code remains unchanged each time. If, however, you come to a situation that requires a one off query then a stored procedure will not be a good fit.[/l] [hdmd]The syntax[/hdmd] [code]USE DataBaseName; GO CREATE PROCEDURE [SchemaName].[ProcedureName] @Param1Name nvarchar(50) = ‘’, @Param2Name nvarchar(50) = ‘’ AS SET NOCOUNT ON; SELECT Col1Name, Col2Name FROM [SchemaName].[tableName] WHERE Col1Name = @Param1Name AND Col2Name = @Param2Name GO [/code] [l]As you can see the syntax is fairly straight forward and self-explanatory. In our first line we have:[/l] [code]USE DataBaseName; [/code] [l]This line of T-Sql code tells the database server which database we wish to interact with. Next we have the [b]GO[/b] keyword which, as I’m sure you guessed, just tells the SQL Server utilities to send the preceding batch of statements to an instance of SQL Server. Interestingly the [b]GO[/b] keyword is not a [b]T-SQL[/b] statement, it is a command recognised by the [b]osql[/b] and [b]sqlcmd[/b] utilities. Now we move onto:[/l] [code]CREATE PROCEDURE [SchemaName].[ProcedureName][/code] [l]In this line we specify the schema in which our stored procedure lives and the name of the sp. Now we have named our sp we need to give it some parameters:[/l] [code]@Param1Name nvarchar(50) = ‘’, @Param2Name nvarchar(50) = ‘’[/code] [l]As you can see we need to specify the parameter name, which starts with a ‘@’ symbol. We also need to specify a data type, we have specified ours as [b]nvarchar(50)[/b] but any data type is fine as long as it matches the column. We have also given our parameters a default value, which in this particular example is an empty string. Now we have our parameters we arrive at the [b]AS[/b] keyword. All this does is separate the declaration of the procedure from its code (the query that the sp will execute). We also have another line of code, [b]SET NOCOUNT ON[/b]. When set nocount is on the count is not returned. This can help improve performance as the network traffic is reduced. And now for the query that the sp will execute:[/l] [code]SELECT Col1Name, Col2Name FROM [SchemaName].[tableName] WHERE Col1Name = @Param1Name AND Col2Name = @Param2Name[/code] [l]As you can see this is just a simple select statement. We told it what columns to search for data that matches our parameters and we told it which table to look in for the required columns. After our select statement we have another [b]GO[/b] keyword, which as before, tells the Sql server utilities to execute the preceding code. And there you have it, a brief introduction into the world of Microsoft Sql server Stored Procedures. I hope you found this article helpful and easy to read. As always I’d love some feedback (good or bad I don’t mind taking some criticism). I eagerly await your responses :) happy coding to all. If you have a moment or two please share this article around as much as possible, that way as many people as possible can benefit from it.[/l]

About the author

My name is Michael Griffiths, I am a website designer and developer and the owner of Griffiths Web Design. I am based in Royston, Hertfordshire. Most of my work is local, Hertfordshire, Cambridgeshire, Bedfordshire, Essex etc. but that’s not to say I won’t work elsewhere. One of the wonders of modern technology is remote working. So no matter where the client is located it is always possible to connect.

I specialise in creating ASP.NET webforms applications using C#. I use Microsoft SQL Server for database development and on the front end I use Html, CSS and JavaScript. I am also proficient in the use of jQuery and several CSS frameworks.

One of my greatest passions in life is learning. Which is why I chose to pursue a career in web design and development, technology is in a state of constant and rapid evolution which means there is always something new to learn. I also enjoy writing, both fiction and non-fiction.

Comments

Please feel free to leave a comment below:






nate


yo thanks man


Michael Griffiths


No worries nate, happy to help


 
Griffiths Web Design Logo by Michael Griffiths
Griffiths Web Design Logo by Michael Griffiths