SQL Server Stored Procedures: Understanding Your Database

sql server stored proceduresSQL Server stored procedures turn those ugly strings in your code into tiny little programs on your database.  One issue with building SQL queries in your code is a hack known as SQL injection. With stored procedures, you’re protected from SQL injection provided you don’t use dynamic SQL in your statements. SQL Server stored procedures are a bit different than other front-end code languages, so it takes some practice before you get to know how to build and run them.

Learn how to work with SQL Server and stored procedures

Creating a Stored Procedure

Just like creating a table, SQL Server uses the “create” keyword to create the procedure. The following is sample code that creates a stored procedure named “myproc”:

CREATE PROCEDURE myproc (

@num1 int

@num2 int

)

AS

GO

In the above code, the “create procedure” (you can also use “create proc” as a shortcut) is the main syntax to create a SQL Server stored procedure. The following “myproc” statement is your stored procedure’s name. You’ll need this name when you call your stored procedure in your code whether it’s C#, PHP or any other language.

The text in parenthesis is the parameters used for input. These parameters are what protect you from SQL injection. When you pass these parameters from your code regardless of the input, it’s contained within the parameters. As long as you don’t use dynamic SQL to build your SQL statement, you avoid SQL injection with stored procedures.

Understand stored procedures and SQL Server syntax at Udemy.com

In the above sample code, the two parameters require integer input. You cannot pass another type of input. For instance, if you try to pass a string or a decimal number, the code will display an error.

Lastly, the “GO” statement instructs the SQL compiler to execute the statements. Your SQL code is placed between the GO and the AS statements. The above code is just the syntax used to create a stored procedure. However, there are no SQL statements, so the procedure does nothing. The following code adds a select statement to your procedure:

CREATE PROCEDURE myproc (

@num1 int,

@num2 int

)

AS

select * from orders where ordernumber = @num1 and ordercount = @num2

GO

If you’re familiar with SQL code, you can identify what the above procedure accomplishes. The statement selects all columns from the orders table. The parameters @num1 and @num2 are used to filter the results. In this instance, only orders with a specific order number and count are returned. You can use your SQL parameters for any type of filtering and manipulation of data.

Maintain your SQL Server with maintenance plans.

For instance, the following code creates a stored procedure that inserts a user’s first and last name into the “users” table:

CREATE PROCEDURE myproc (

@fname varchar(50),

@lname varchar(50)

)

AS

insert into users (first_name, last_name) values (@fname, @lname)

GO

Notice the stored procedure’s parameter data types are different than the previous sample. The “varchar” data type is used for strings. There are other string data types, but the varchar data type lets you enter a string of 1 character or hundreds. In this example, the varchar data type is set at 50. This means that your code can pass up to 50 characters to the parameter. If you accidentally pass more than 50 characters, any characters after the 50-character limit will be truncated. It’s this reason that you should always ensure that your stored procedure parameters allow for plenty of characters.

You can also retrieve the data you just inserted into the database. This is useful when you have a site that asks users to insert information. You insert the information and then retrieve it for confirmation. For instance, when your users add items to a shopping cart, you store these items in the database. Before the user checks out, you then display items for confirmation.

The following SQL statement inserts a first and last name and then retrieves the recently added data:

CREATE PROCEDURE myproc (

@fname varchar(50),

@lname varchar(50)

)

AS

insert into users (first_name, last_name) values (@fname, @lname)

select * from users where userid = @@identity

GO

In the above code, a user’s first and last name are entered into the users table. Then, the most recent record is retrieved. In this example, notice the “@@identity” used in the select statement. There are some internal functions to SQL Server, and this is one property you can use to grab the last identity record entered into the database. This code assumes that there is an auto-incremented identification column. It’s considered bad table design if you don’t have a primary key that identifies unique rows in your tables. Some table designers use other unique identifiers, but many designers use an auto-incremented integer.

You can also delete and update records in your stored procedures. The following is a stored procedure that updates records based on the user ID:

CREATE PROCEDURE myproc (

@fname varchar(50),

@lname varchar(50),

@userid int

)

AS

update users set first_name=@fname, last_name=@lname where userid=@userid

select * from users where userid = @userid

GO

The above code updates a user with a new first and last name. Notice another parameter was added. The @userid parameter is passed to ensure that only one user is updated. If you eliminated that one parameter, you would update the entire table. Obviously, you would not want to update all records with the same first and last name, so you add the userid parameter in the “where” clause.

You also have the option to alter existing stored procedures. To alter a procedure, you use the “alter” keyword. The following statement alters a stored procedure:

alter PROCEDURE myproc (

@fname varchar(50),

@lname varchar(50),

@userid int

)

AS

select * from users where userid = @userid

GO

If you ran the above code, it will change the existing “myproc” procedure to the new code. In this instance, a select statement runs. Running this statement overrides any current statements in your stored procedure, so make sure you really want to alter a procedure before changing it.

Stored procedures are not difficult if you already write SQL statements. However, converting any inline SQL code will protect your application from SQL injection.