A simple explanation is a stored procedure is a bit of T-SQL code that can take input parameters and is intended for use in queries that are regularly executed against SQL Server. It is “stored” within the database engine (hence the name).
I’m not going to cover all of the benefits here, but I wanted to touch a bit on the security aspects which (outside of the inherent modular coding and speed benefits) in my opinion are the most important.
You can assign security to a stored procedure without having to give users permissions on the underlying tables that are involved.
So for example using the following to create a simple table on tempDB:
[sql]SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(‘dbo.tblDrink’, ‘U’) IS NOT NULL DROP TABLE dbo.tblDrink;
GO
CREATE TABLE dbo.tblDrink
(
drinkID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
drinkType VARCHAR(15) NOT NULL
);[/sql]
Then create a stored procedure:
[sql]
CREATE PROC spDrinks
@drink VARCHAR(15)
AS
INSERT INTO dbo. tblDrink (drinktype) VALUES (@drink);
[/sql]
I could then give “userA” only EXECUTE permissions like so:
[sql]GRANT EXECUTE ON spDrinks to userA;[/sql]
The effect is that userA can run the spDrinks stored procedure (and in effect insert into the tblDrinks), but does not have any direct insert permission on the underlying tblDrinks table.
So, if userA tried to run a direct query like a SELECT against this table they would get something like:
And if userA executed the procedure:
[sql]EXECUTE dbo.spDrinks ‘Lemonade'[/sql]
(note: the EXECUTE and dbo prefix is not required but saves SQL Server time searching for the correct schema. Plus it’s simply best practice)
This would insert “Lemonade” into tblDrinks.
Can help mitigate SQL injection attacks.
Take this .NET snippet for example:
[html]<asp:TextBox id="openbox" runat="server"/>[/html]
Seems harmless enough right? A simple ASP.NET textbox. No problems so far.
So then your developer does this little gem which allows the value of the textbox to be passed directly into the SQL Statement:
string sqlCommand = 'INSERT INTO tblDrink (drinkType) VALUES (' + openbox.Text + ')';
Not really a problem until Harry the Hacker comes along and dumps the following into the text field and submits the form:
[sql]lemonade’;SELECT * FROM tblDrink–[/sql]
OR even worse
[sql]lemonade’;DROP TABLE tblDrink–[/sql]
(bye bye table)
But if you have set your security correct and are using parameters with stored procedures if I try and pass in the same injection string I get this:
No table drops, no data outside of the scope of the stored procedure being possible.
In summary I would say that stored procedures can make your life a lot easier with the security (not to mention the modularity of them). I find myself regularly creating and calling them.
