New to SQL : Create Stored Procedure

[iwrk4dedpr]

Hello,

I'm in dire need of some help. I've taken control of a project for our training department. It uses a database on a SQL 7 database server. Which I've never used.

I've created a new table and now want to create a stored procedure ( that would be called from a .asp web page ), however, I've been less than successful. I've read in various places that using stored procedures is more efficient than sending the SQL string each time. So that's what I'm trying to accomplish. The following is some of the datagbase specific information.


Table Inforamtion
Name: tbl_ReviewQuestions

Fields
Category
Version
Section
Number
Question


SQL statment that I came up with ( query parameters are [] )
SELECT *, Category, Version, Section, Number
FROM tbl_ReviewQuestions
WHERE (Category = [Enter Category])
ORDER BY Category, Version, Section, Number


The real questions are these.
1. How do I create a stored procedure?
2. How do I test the stored procedure?

I'm using the SQL Server Enterprise Manager. Any help will be greatly appreciated.

 

[mrdenny]

You'll want to do all this in Query Analyzer not Enterprise Manager.

To create the procedure, you'll want this.

create procedure {Procedure_Name}
   @Category varchar(100)
as
SELECT *, Category, Version, Section, Number
FROM tbl_ReviewQuestions
WHERE Category = @Category
ORDER BY Category, Version, Section, Number
go

To run the procedure you'll use something like this.

exec {Procedure_Name} '{Category}'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[iwrk4dedpr]

Denny,

Thanks for the reply. Ok I've created and saved the procedure

CREATE procedure dbo.GetEvalQuestions
   @Category varchar(10)
as 
SELECT *, Category, Version, Section, Number
FROM tbl_ReviewQuestions
WHERE Category = @Category
ORDER BY Category, Version, Section, Number
GO

as per your post. The syntax checker states that the sytax is ok.

However, the question now is how do I execute the procedure?

Where do I put the following?

exec GetEvalQuestions 'EES'

I would like to test the procedure in the Manager environment before commiting the procedure to the actual project.

Again thanks for any help in advance!!!!!!!!!!

 

[JamesLean]

As Mr Denny said, you should be using Query Analyzer to execute these scripts, not Enterprise Manager.

If you use QA you can execute the EXEC statement exactly the same as you executed the CREATE PROC statement.

--James