Originally posted by Dubya007
ok thanx, do you know where there is a place that i can read up on stored procedures? i'm kinda scared to try one as i don't want to mess the DB up. but i'm willing to give it a try.
You should have no fear about creating a simple stored procedure that does a SELECT statement, for example.

We create all of our SPROCS in QUERY ANALYZER with a .SQL script file. Some people might choose to just load them into the DB and then edit them in the DB, but since we have so many customers, we need to distribute changes to SPROCS, so having them in .SQL files kind of treats them like SOURCE vs COMPILED.

At any rate - here is a simple .SQL script that we execute in QA. It's stored in a text file called GETAPPELE_P.SQL - created in QA and saved/edited/re-saved from QA.

The first thing we do is a USE statement - making sure we are "in the right DB" - we have lots and lots of DB's. The two SET statements are just boilerplate we've been using forever - don't even remember why...

The "if exists" statement will DROP the SPROC if it's already in the DB.

Then we have a very simple SELECT statement - all this SPROC does is return a RS. It's as if the SELECT was in-line in VB code - no danger to the DB with this. The SELECT is wrapped in a "CREATE PROCEDURE" statement and "ended" by a GO. The SET NOCOUNT ON is a standard statement we use in SPROCS to keep ADO from getting those little "1 row(s) affected" message in the RS - these can sometimes get in the way.

At the end we GRANT EXEC permission to this SPROC to the "SQL role" that needs it - and then finish up with some standard "SET" statements.

Whenever we go into QA and change this SPROC, we save it (so the .SQL text file is kept up to date) and then press the "EXECUTE QUERY" button on the top toolbar. Basically dropping and re-creating the SPROC in the DB.

SPROCS are great - you do not have to give users access to tables, only SPROCS - so they are a secure way to offer your data to the user group. They are pre-compiled - so they run faster.

Plus they can be very complex - we actually adjudicate medical claims in a SPROC - it's 2472 lines long!!

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].[GetAppEle_P]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAppEle_P]
GO

Create Procedure GetAppEle_P
as
  Set NoCount On
  Select AER.AppID, AER.ROleID
  FROM AppElemRole_T AER
  WHERE AER.AppId < 10000 and AER.RoleId<1000
  ORDER BY AER.AppId DESC
GO
GRANT EXEC ON GetAppEle_P to StufilesUser
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO