304 Chapter 7 " Building a SQLXML Web Service Application Looking at the code for one stored procedure, you can add as many as you like based on what functionality you want to have. Here you can look at the code for the addEmp stored procedure, which is used for adding employees. create proc addEmp @Name nvarchar(50), @Title nvarchar(50) as insert into Employees (EmpName,EmpTitle) Values (@Name,@Title) GO The syntax is fairly simplethe main thing to remember is that if you want to pass parameters you must declare them before the keyword AS. Here is a stored procedure that we have named showPastDueJobs that will be used to gen- erate our report on overdue projects: create proc showPastDueJobs as select j.JobTitle, SUM(t.total) as TOTAL from Track t join Jobs j on t.JobID = j.JobID where TOTAL >= TimetoCompletion group by JobTitle GO As you can see there are no parameters declared, so you can write a select statement to return your results.You want to use stored procedures because they offer a huge gain in performance over using ad hoc queries against the database. Using stored procedures will give you precompiled and optimized results, which will be waiting for delivery, as opposed to having a SQL Server parse the query, www.syngress.com Figure 7.4 Stored Procedure Menu