Stored procedures are extremely useful objects. Not only do they store T/SQL scripts for later execution, but they also provide us with an extremely important security barrier between the user interface and the database. The security barrier is used to prevent the users from needing SELECT, INSERT, UPDATE and/or DELETE rights directly to the database tables and views.
This is done through what is called permissions chaining. When a user has rights to execute a stored procedure they are given temporary rights to use the table objects within the procedures which are used by the table.
Creating stored procedures is very easy. Take your Transact SQL code and put it below the CREATE PROCEDURE command, and end the batch. Like all other database objects the name of the stored procedure must be unique within the schema (or owner for SQL 2000 and below). As an example lets create a stored procedure which returns the names of all the tables in the current database.
CREATE PROCEDURE ShowTables AS
SELECT schema_name(schema_id), name
FROM sys.tables
GO
As you can see the basic syntax is very simple. To run this stored procedure we simply run the stored procedure name.
exec ShowTables
You can add in input parameters to handle filtering, or which would need to be inserted into a table. An input parameter is simply a variable which you set when you run the procedure. You can access the value of the input parameter within the stored procedure as you would any other variable. Let’s look at the same procedure but this time we want to filter the tables by the first letter.
CREATE PROCEDURE ShowTables
@FilterChar NVARCHAR(2)
AS
SET @FilterChar = @FilterChar + '%'
SELECT schema_name(schema_id), name
FROM sys.tables
WHERE name LIKE @FilterChar
GO
In this example as you can see we take the input parameter, and add the % wild card, then use the variable to filter down the records to see only the records which start with the character we supply. Running the stored procedure with an input parameter is just as easy.
exec ShowTables @FilterChar=N'C'
We can also use output parameters to get values back from the stored procedures. Output parameters are used basically in the same way that input parameters are, however you add the OUTPUT keyword after the parameter. Within the stored procedure simply set the output variable to the value you want it to return to the calling code. This can be done anywhere within the stored procedure, as long as the variable still holds the value when the stored procedure has completed it’s execution the value will be returned to the calling code. First lets look at the code to create the stored procedure.
CREATE PROCEDURE ShowTables
@FilterChar NVARCHAR(2),
@RowCount INT OUTPUT
AS
SET @FilterChar = @FilterChar + ‘%’
SELECT schema_name(schema_id), name
FROM sys.tables
WHERE name LIKE @FilterChar
SET @RowCount = @@ROWCOUNT
GO
We run the stored procedure in much the same way we do with the input parameter.
DECLARE @RowCount INT
exec ShowTables @FilterChar=N’C’, @RowCount=@RowCount OUTPUT
SELECT @RowCount
In this case we are simply returning the row count as a second record set, but you’ll get the basic idea.
As I wrote earlier you can add records to a table with the stored procedure.
CREATE PROCEDURE InsertTable
@Id INT,
@Value VARCHAR(20)
ASINSERT INTO SomeTable
(Id, Value)
VALUE
(@Id, @Value)
GO
As you can see it’s a very basic method. It’s a regular insert statement with the parameters passed to it.
I know that this was a bit longer than the other posts, but I hope that you found it worth while.
Denny
4 Responses
Great artcile
exec ShowTables @FilterChar=N’C’
In the above command, why do you put “N” right after the equal sign? It ran it with and without it, gave me the same results.
Thanks…
Thank you.
The N is used to tell SQL Server that the data being sent in is unicode. Since when you create tables you are not limited to the latin character set that we use in the English language the sample code is setup to include to allow for these other characters. If for example you had created the table using russian characters as the table name you need need to put the N in front of the string.
When dealing with latin characters the N is usually optional, but it is better to put it in.
“I know that this was a bit longer than the other posts, but I hope that you found it worth while.”
_____________
Mr. Denny,
You never have to apologize for your posts being too long. For me, they always seem succinct, easy to assimilate, and they simplify any subject you post about.
I appreciate you devoting your time and energy to produce them. They are always worth my time to read them.
LTC