Here's an example of a SPROC - actually a .SQL script created (typed that is) in QUERY ANALYZER.

We create all our SPROC's this way - so we can archive them with SOURCE SAFE and easily distribute them to customer machines.

The USE statement is T-SQL to indicate the DATABASE that we want to touch...

The SET statements are boilerplate that I cannot even remember the reasons for...

The if exists business will see if the object is already in the database and drop it.

Then the obvious Create PROCEDURE. All this stuff broken up by GO statements - which are simply directives to QUERY ANALYZER to send each piece as a separate batch to the server for processing.

Followed by one of our most important lines of all - the GRANT EXECUTE ON rptDailyAttNurse TO StufilesUser - this applies the proper access to the users to this SPROC. We happen to only allow SPROC access to the database and table - no user has SELECT/UPDATE/DELETE type access to tables.

This particular SPROC does quite a few things - many IF/blocks to check values entered and determine what might be returned. Followed by a complex little SELECT/UNION/SELECT to give back a recordset to the users. Followed by a INSERT INTO to move some data and actually a final call to another SPROC to perform some other actions.

Our UI sees the parameter of @Commit$N_Y and asks the user "Commit?" with a "N" default - N or Y are possible answers. We do that so the SPROC can run in an un-committed mode so the user can see if they get errors and if they are happy re-run with Y for COMMIT.

Code:
USE Stufiles

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rptDailyAttNurse]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[rptDailyAttNurse]
GO

CREATE PROCEDURE rptDailyAttNurse
	 @PassConnId int
	,@RetStat int Output
	,@RetText varchar(100) Output
	,@RetMode int Output
	,@RetGrid int Output
	,@RetExtra varchar(100) Output
	,@PassMode int
	,@PassExtra varchar(10)
	,@PassTest varchar(10) 
	,@Yr int
	,@Bldg varchar(50)
	,@Commit$N_Y varchar(1)
AS

Declare @RB int

Set @RetStat=0
Set @RetText=''
Set @RetMode=0
Set @RetGrid=0
Set @RetExtra=''

Set NoCount On

Declare @W_Bldg int

If @PassMode=2
Begin
	If @PassExtra in ('1','3')
	Begin
		Set @RetStat=-1
		Set @RetText='VIEW not available from this query line'
		Return
	End
	If @PassExtra='2'     -- @Bldg
	Begin
		If ISNUMERIC(@Bldg) = 1 Set @W_Bldg = Cast(@Bldg as int)
		Select Top 201 BG.BldgName+' ('+Cast(BG.Bldg as varchar(6))+')'
		,BG.Bldg "Value" From Building_T BG 
		Where BG.BldgName like @Bldg+'%' or (@W_Bldg <> 0 and @W_Bldg = BG.Bldg)
		Order by 1
		If @@RowCount=0
		Begin
			Set @RetStat=-1
			Set @RetText='No matching Building information found'
			Return
		End
	End
	Return
End

If IsNumeric(@Bldg)<>1
Begin
	Set @RetStat=-1
	Set @RetText='Must use VIEW to resolve BUILDING NAME'
	Return
End

Begin Tran

Set @RB=0
If @Commit$N_Y<>'Y' Set @RB=1

Select 'Bad Data - not posted'
	,NurseData
	,BU.BldgName
From Nurse_T ND
Left Join Building_T BU on BU.Bldg=@Bldg
Where ND.NurseStat='X' and ND.Yr=@Yr and ND.Bldg=@Bldg

Union

Select 'Data being posted'
	,'Updating '+Cast(StuId as varchar(6))+' with '+AttType+', in time='+NurseITime+', out time='+NurseOTime
	,BU.BldgName
From Nurse_T ND
Left Join Building_T BU on BU.Bldg=@Bldg
Where ND.NurseStat='E' and ND.Yr=@Yr and ND.Bldg=@Bldg

Order by 1,2

Insert into Attendance_T
Select StuId,Yr,Bldg,NurseDate,'A',NurseITime
,IsNull((Select Max(AttSeq)+1 From Attendance_T ATT
	Where ATT.StuId=NU.StuId and ATT.Yr=@Yr and ATT.Bldg=@Bldg and ATT.Attdate=NurseDate and ATT.AttRecType='A' and ATT.AttTime=NurseITime),0)
	+Case When NU.AttType='DN' Then 1 else 0 End
,AttType,0,NurseOTime,'1',null,GetDate() From Nurse_T NU Where NurseStat='E' and Yr=@Yr and Bldg=@Bldg

If @@Error<>0 Set @RB=1


Update Nurse_T Set NurseStat='1' Where NurseStat='X' and Yr=@Yr and Bldg=@Bldg
Update Nurse_T Set NurseStat='2' Where NurseStat='E' and Yr=@Yr and Bldg=@Bldg

If @RB=0
Begin
	Exec utility_reopenofficepostings
	Commit
End
Else
Begin
	Rollback
End

Go

GRANT EXECUTE ON rptDailyAttNurse TO StufilesUser
Go

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO