Procedure Execution

Now that the procedure has been created, the next step is to run it. You’ve already run several stored procedures, including sp_help. To run a procedure, simply type its name.

pr_getprice

price
--------------------------
10.95

The procedure displays the results of the SELECT statement. If you try to run the procedure in combination with another statement, you can run into problems.

select    title_id
from      titles
where     type = “psychology”
pr_getprice

Msg 170, Level 15,  State 1
Line  4: Incorrect  syntax near ‘pr_getprice’.

The SQL Server parser cannot figure out what to do with the procedure name. In order to run the procedure, you need to include the EXECUTE keyword. That tells the server that what follows is a procedure name.

select   title_id
from     titles
where    type = “psychology”
execute  pr_getprice

It’s never wrong to use EXECUTE to run a procedure. EXECUTE is only optional when the procedure name is the first statement in the batch.

---

Note:  The parser expects that the first word in every batch will be a SQL keyword (that is, a word that it knows, like SELECT, DELETE, EXECUTE, CREATE, or SET). If it does not recognize the first word, it assumes that that word is a stored procedure name.

---

As we step through the development of procedures, the syntax for procedure creation and execution will become increasingly complex. For now, here’s the syntax for executing a procedure:

[exec[ute]]  procedure-name

Remember: EXECUTE is only required if the procedure is not the first statement in the back.

Some Practical Advice About Editing

There are some issues associated with editing stored procedures. First, you can’t simply edit the procedure batch and re-create it. The procedure already exists, and SQL Server won’t overwrite an existing object. If you try, you will get this error message:

Msg 2729, Level 16, State 1
Object ‘pr_getprice’ group number 1 already exists in the database.
Choose another procedure name

You have several choices. During this editing session, the easiest thing may be to rename the procedure by adding the number 1 to its name:

create proc pr_getprice2 …

That way you will be able to refer back to earlier procedures.

Later on, when you write stored procedures intended to be part of a production system, you will want to drop and re-create procedures when you make changes to them. The problem is, you can’t perform the drop and the create in the same batch.

/* This won’t work */
drop proc pr_getprice
create proc pr_getprice
as
select  price
from    titles
where   title_id = “PS2091”
return

Msg 111, Level 15, State 1
CREATE PROCEDURE must be the first command in a query batch.

Instead, you need to drop the procedure in the first step and then create it in a separate batch.

/* This works fine */
drop proc pr_getprice
go
create proc pr_getprice
as
select  price
from    titles
where   title_id = “PS2091”
return
go

(No output is provided.) This method works because GO separates batches in a SQL script.

You will also find it easier to edit and test stored procedures if you keep open two connections to the server: one for editing the procedure and one for testing its execution. If you are using ISQL/w, use File | New to open a new connection to the same server using the same login and password and defaulting to the same database.

Using Parameters

Let’s get back to our procedure: there’s a basic problem. It works great if we want to know the price of title PS2091, but what if we were interested in title PS1372? Should we write another procedure to handle that title? Of course not.

Instead you need to rewrite the procedure to allow you to pass a title_id as a parameter and return the correct price.

A parameter is a mechanism allowing a calling program to pass a value to a procedure so that the procedure can operate in response to that value.

To accept a parameter, you need to declare it in the CREATE statement. You can then use the parameter as you would a variable in the procedure.

create proc pr_getprice2
(@title_id char(6))
as
select  price
from    titles
where   title_id = @title_id
return

The declaration of the parameter requires a legitimate data type.

(@title_id char(6))

The data type should match the column data type if it will be used in a WHERE clause, as in this example:

where  title_id = @title_id

Query performance is considerably better if the data type of the parameter is an exact match for the column data type. You should take the time to look up the data type (using sp_help or a similar tool) when you are writing a stored procedure.

Syntax with Parameters

Here is the syntax for creating procedures with parameters:

create proc[edure] <procedure-name>
[ ( @parameter-name datatype [, …]) ]
as
<SQL statements>
return

Execution with a Parameter

How do you pass the parameter to a procedure? (You have already been doing it with the system procedures.) Parameters are passed on the same line after the parameter name:

pr_getprice2 PS1372

price
--------------------------
21.59

The procedure now reports the correct price for the title whose title_id is passed to the server.