Writing and Calling Stored Procedures

In my previous post we built a Cheesy Database using simple SQL Scripts. Now I want to build on that by coding a stored procedure. Stored procedures are a ColdFusion developer’s best friend. You can use stored procedures to store some/most/all of your SQL logic. This is a great way to organize your code (stored procedures are generally faster than inline queries), optimize your application’s performance and help to protect your system from SQL Injection.

Writing a basic Stored Procedure is not a difficult task. First lets take a look at our inline query…

Example 1

1
2
3
4
5
6
7
8
9
10
11

<cfquery name="addCheesesToFlavors" datasource="CheesyDB">
	INSERT INTO
		CheesesToFlavors
			(
			flavorId,
			cheeseId
			)
	VALUES
		<cfqueryparam value="#variables.flavorId#" cfsqltype="cf_sql_int" />,
		<cfqueryparam value="#variables.cheeseId#" cfsqltype="cf_sql_int" />
</cfquery>

In this simple INSERT statement we are inserting a new record into CheesesToFlavors. Note the use of CFQUERYPARAM (a tag that all ColdFusion developers should be using).

Writing A Stored Procedure

Now lets take the above query and convert it to a stored procedure. To add a new stored procedure browse to [your_database_engine] > CheesyDB > Programmability > Stored Procedures, then right click on “Stored Procedures” and select “New Stored Procedure…”. Now you are ready to write some code!

First we declare our Stored Procedure and our input variables…

Example 2a

1
2
3
4

CREATE  PROCEDURE uspAddCheesesToFlavors
	@cheeseId INT,
	@flavorId INT
AS

Line 1 declares that “this is a stored procedure named ‘uspAddCheesesToFlavors’” Lines 2 and 3 are the two input variables that the procedure expects, and the datatype expected is int. Lastly we have the AS keyword.

Next we insert the record into the database…

Example 2b

5
6
7
8
9
10
11
12
13

INSERT INTO
	CheesesToFlavors
		(
		flavorId,
		cheeseId
		)
SELECT
	@flavorId,
	@cheeseId

This simple INSERT statement is similar to one that you would find in your inline queries. On line 11 we’ve replaced the “VALUES” keyword found in our inline query with the “SELECT ” keyword. On lines 12 and 13 we output the same two variables that we declared at the top of the procedure.

Calling Your Stored Procedure

Example 3

1
2
3
4

<cfstoredproc procedure="uspAddCheesesToFlavors" datasource="CheesyDB">
	<cfprocparam type="in" cfsqltype="cf_sql_int" value="#variables.flavorId#" />
	<cfprocparam type="in" cfsqltype="cf_sql_int" value="#variables.cheeseId#" />
</cfstoredproc>

Yes it’s that easy. Specify your procedure name and datasource name in the CFSTOREDPROC tag, declare your input variables in CFPROCPARAM tags and you are all set. Note that your CFSTOREDPROC tags have to be in the same order as your variable declarations in order for your procedure to work as intended!

Many Cheeses, Many Flavors

Since we’re using our stored procedure to insert records into an intermediary table that joins Cheeses and Flavors we need to be mindful of duplicate records. If we attempt to insert a duplicate record into this table we will throw an error. We only have to add the code in example 2d to prevent our database from inserting duplicate records…

Example 2d

14
15
16
17
18
19
20
21
22
23
24

WHERE NOT EXISTS
	(
	SELECT
		flavorId,
		cheeseId
	FROM
		CheesesToFlavors
	WHERE
		flavorId = @flavorId
		AND cheeseId = @cheeseId
	)

For your cutting and pasting pleasure, here are all of the “Example 2″ code samples merged into one place…

Example 4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE  PROCEDURE uspAddCheesesToFlavors
	@cheeseId INT,
	@flavorId INT
AS
INSERT INTO
	CheesesToFlavors
		(
		flavorId,
		cheeseId
		)
SELECT
	@flavorId,
	@cheeseId
WHERE NOT EXISTS
	(
	SELECT
		flavorId,
		cheeseId
	FROM
		CheesesToFlavors
	WHERE
		flavorId = @flavorId
		AND cheeseId = @cheeseId
	)

Booya!