stored proc tutorial

[kleinma]

I have never really used stored procedures because I never really had a need to. I would just use standard SQL calls to the DB via ADO in my apps.

I have a pretty complex filter I need to apply and the SQL statement keeps getting bigger and more cumbersome, so I would like to convert it to a stored proc, but I am not too sure how they work.

I understand the idea of them and I sort of know how to create them, but I am looking for maybe a site or something that gives a basic overview with some examples.

Im using SQL Server 2000

I guess what I don't get is, the syntax shows as "create procedure" but do you need to "create" it each time? or is that just the first time its created? why when I use this syntax in the query analyzer, does it not show up in the stored procedures section of my database?

[si_the_geek]

Moving to a stored proc is a good idea, as it encapsulates the methods of the query (so you can access it from any program that connects without re-writing), and it also lets SQL Server 'compile' the query so that it executes more quickly.

I like the idea that a few of our more experienced DB members have, which is to have everything in SP's, as it makes for a nice n-tier design (and for some, a very simple front end which can be used for any DB just by adding a few settings to the DB).


You need to "Create" each time you modify a SP (after you delete it first), as this is effectively the 'compiling' process. The reason you don't see it listed after running the Create is because the SQL Server client tools do not auot-refresh, you need to do this yourself (right-click on the SP's section, and click refresh).

I don't know of any sites that have tutorials etc, but we do have many examples with explanations in this forum (especially from szlamany and kaffenils).

[zubairkhan]

Here in this forum i have posted a threat regarding stored procedure issue, both phased a posted from visual basic side and stored procedure as well, you can get basic idea for creating stored procedure and calling it from visual basic.

[techgnome]

The first time, the SP has to be created. After that, it only needs to be ALTERed. Or, as our standards dictate... it can be DROPed and then reCREATEd.

-tg

[szlamany]

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.

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

[techgnome]

With the exception of the Use part, that's the way we do it as well. Then everything is checked into SourceSafe where it gets labeled. We then have an app that we've built that will (based on a list of ticket numbers.... which correspond to the labels on the SS projects) can take all the .SQL files, check for dependencies, and create a single SQL script file with all of our SPs for that particular release.

-tg

[szlamany]

The only reason for the USE part is that we have three distinct business products we develop here - and each has a unique DB name (obviously!).

But we have a single UI for all three of these products.

So when a SPROC is ripped from one product folder to another (we share some common sproc - non-business specific) we copy the .SQL file and in QA do a REPLACE ALL - that get's the USE {DB} and the GRANT...TO {DBROLE} all in one shot. We also do a REPLACE ALL on the SPROC name if we are ripping for a new purpose and that takes care of the DROP/CREATE and GRANT part as well.

With the 3 different db's it's easy for my staff to be in QA and have the wrong DB selected - the USE makes sure of proper alignment. We try and force a convention of always entering the DATABASE through ENTERPRISE MANAGER and launching QA from the TOOLS menu within - we also have several different SQL sand-box servers/laptop's on the network...

[techgnome]

I hear ya... our problem is similar... one product, but multiple clients.... there have been times when we accidently ran Client X's script in Client Y's DB! Fortunately it only affects us here (each client runs their own db, sending us a back up each week for support).

Not to mention I don't know how many times the script has been run in the master database....

-tg

[szlamany]

Not to mention I don't know how many times the script has been run in the master database....

That one scares me the most, because they then quickly try to clean up the mess and trail they left and you know where that gets you

[techgnome]

here it gets you 10-15...

-tg

[kleinma]

thanks guys for the info... i am going to look it over as well as 1 or 2 sites i found with some info and see what I can come up with.

Basically this app is for internal use, so its probably not going to need to be as complex as the steps you guys take, as there are only 2 of us in the office who use this app.

Its in our billing software, and its a filter screen where you can pull up customer accounts by filtering on many aspects of the account (many of which are in seperate tables) so my SQL statement (while I am proud of myself that it actually works) becomes increasingly harder to maintain. I feel a stored proc where i can create recordsets and further filter them down based on the filter criteria is a better solution.

[kleinma]

where do you guys do most of your SP writing? query analyzer or in enterprise manager?

[techgnome]

Query Analyzer... always.... EM does strange things with your procs when editing in there...

-tg

[kleinma]

i was starting to notice that

[si_the_geek]

QA has a much nicer interface for them too, rather than that silly little window.

[szlamany]

QA always...

[kleinma]

If I need a 3 state bit, like (True, False, and N/A) as a param passed in, can I use null for the 3rd state?

for example, if a param of type bit is needed, can I pass either 0,1, or Null as a value?

Reason being is that this filter has some checkboxes, and dropdowns,

like a checkbox that says include all accounts with/without (with/without is a combobox) an email address

so if its not even checked, i want to pass null to let the SP know to not even need to deal with that part of the filter, and then obviously if they did check the checkbox, a 0 or 1 for true/false for the with/without

or is it just easier to use an int and pass 1 of 3 numbers in to do the check?

[kleinma]

If I need a 3 state bit, like (True, False, and N/A) as a param passed in, can I use null for the 3rd state?

I did a small test and it appears this will work

[kleinma]

I think I have what I need to get rolling for now, ill post back if I run into any more problems. I have the proc created and returning rows on a simple query, so now I will work on the guts of the actual filter.

[techgnome]

I usualy use a tiny int for that kind of information: -1, 0, 1 ( or Null, 0, -1/1).....
It almost surprises me that it works .... but at the same time not surprised that it does.

Hmmm....

-tg

[kleinma]

so do you guys usually use 2 windows in QA, one that has the sp code that you can keep running to recompile it when you make changes, and then another window with an exec statement with params so you can test the output? or can those be combined into 1 window?

[szlamany]

I like to use null for N/A on a sproc parameter myself...

And in QA, I usually leave a line like this:

--exec somesproc

and by just highlighting the exec somesproc (which is commented out by the -- characters, you will run just the selected text.

[brucevde]

I tend to use one window for simple testing. The Execute is at the end (preceded by a Go), so hitting F5 recompiles/recreates the sproc and then immediately tests the changes.

[kleinma]

and you always leave some test data for the input params then i would imagine right?

[szlamany]

and you always leave some test data for the input params then i would imagine right?

I do include "test params" for the EXEC SPROC command that is commented out...

Or the calls to UDF's (note the two --SELECT statements)...

Use Funds
DROP Function GetCode_F
Go

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
--SELECT DBO.GETCODE_F('1234 (X)')
--SELECT DBO.GETCODE_F('TESTSTRING (ABC)')
CREATE FUNCTION dbo.GetCode_F (@FullString varchar(100))
RETURNS varchar(20) AS  
BEGIN
	Declare @EB int
	Declare @RS varchar(20)
	Set @FullString=RTrim(@FullString)
	If Right(@FullString,1)=')'
	Begin
		Set @EB=CharIndex('(',Reverse(@FullString))
		If @EB<>0 Set @RS=SubString(@FullString,Len(@FullString)-@EB+2,@EB-2)
	End

	RETURN @RS
END
GO

GRANT EXECUTE ON dbo.GetCode_F TO FundsUser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[kleinma]

ok so if I want to return a recordset from this SP (which would be data from more than 1 table), but I want to extensivly filter it also based on passed in params, would I create a temp table in the SP and filter in there? or can you use select statements and filter the result set down until its what you want and then return it?

[szlamany]

ok so if I want to return a recordset from this SP (which would be data from more than 1 table), but I want to extensivly filter it also based on passed in params, would I create a temp table in the SP and filter in there? or can you use select statements and filter the result set down until its what you want and then return it?

Well - that depend...

It's so based on what criteria and index matches you have.

We like to usually have one big-old SELECT+JOINS that we WHERE clause with all the criteria.

If it behaves well, then we go with it.

If it does not then we are forced to consider either SUB-QUERY's, TEMP TABLES or TABLE VARIABLES.

TABLE VARIABLES are my favorite.

DECLARE @TBLVAR TABLE (COL1 int, COL2 varchar(10))

But they start behaving poorly with many rows - work better then TEMP TABLES with very few rows.

Sometimes we do lots if IF/BLOCKS to decide which query to execute...

[kleinma]

would a logical thing for to do be to create a temp table that returns the joined recordset as needed, but then deletes records based on the rest of the criteria, and then returns the final recordset with only the needed rows?

[szlamany]

I have done it that way. Sometime the "selection criteria" is so "complex" to grab...

Set NoCount On

Declare @IdList Table (StuId int, Bldg int, Grade varchar(2), Team varchar(2), HomeRoom varchar(5))

Insert into @IdList Select StuId,Bldg,Grade,Null,Null From ActiveStudent_V Where Yr=@Yr and Bldg=@Bldg

If IsNull(@Grade_Range,'')<>'' Delete From @IdList Where Grade<@LowGrade or Grade>@HighGrade

Update @IdList Set Homeroom=(Select Top 1 TV.Room From Class_T CL 
				Left Join Course_T CR on CR.Yr=CL.Yr and CR.Bldg=CL.Bldg and CR.Crs=CL.Crs
				Left Join Time_T TV on TV.Yr=CL.Yr and TV.Bldg=CL.Bldg and TV.Crs=CL.Crs and TV.Sectn=CL.Sectn
							and TV.TimeVec=CL.TimeVec
				WHERE (CL.StuId = IL.StuID 
					AND CL.Yr = @Yr AND CL.Bldg = @Bldg AND CR.CrsType='H')
					ORDER BY CL.AddDate Desc)
		From @IdList IL

If IsNull(@Homeroom,'')<>'' Delete From @IdList Where Homeroom<>@Homeroom

Update @IdList Set Team=RT.Team
		From @IdList IL 
		Left Join Room_T RT on RT.Yr=@Yr and RT.Bldg=@Bldg and RT.Room=IL.HomeRoom

If IsNull(@Team,'')<>'' Delete From @IdList Where Team<>@Team

[kleinma]

Is there a way to declare a temp table with the same set of columns as a table in the database? or do I just need to manually write out each column for the temp table?

[kleinma]

Also guys, I set up a test vb.net app to test interaction between the .net data classes and the stored Proc, you know adding the params and what not.

I got it working ok, but one of my params is a type int, and if I pass 0 as the value, my .net app errors, (with the lovely and descriptive, "System Error" description) but if i pass 1, it works. Any idea why that would be?

EDIT: I discovered if I pass the int params as strings, it does not error.

'ERRORS
CMD.Parameters.Add("@OmitDemoAcct", 0)
'DOES NOT ERROR
CMD.Parameters.Add("@OmitDemoAcct", "0")
 
'DOES NOT ERROR
CMD.Parameters.Add("@OmitDemoAcct", 1)
'DOES NOT ERROR
CMD.Parameters.Add("@OmitDemoAcct", "1")

[szlamany]

Is there a way to declare a temp table with the same set of columns as a table in the database? or do I just need to manually write out each column for the temp table?

Not that I am aware of - but this thread is getting a bit long for some people to look at - maybe post a new thread?

BTW - your other post - about the 0/string and .net - I'm just getting into .Net myself now - not sure I understand that issue at all...

[techgnome]

Not that I am aware of - but this thread is getting a bit long for some people to look at - maybe post a new thread?

BTW - your other post - about the 0/string and .net - I'm just getting into .Net myself now - not sure I understand that issue at all...

Actualy, there is a way to do that:

SELECT TOP 0 *
INTO #MyTempTableName -- if the temp table is previously un defined, this will create it.
FROM tblSomeTableToCopyFrom

If you want the data too, then remove the Top 0.....

Also, you can specify the cols, and the order too.

It's innefficient, but it does work.

-tg

[szlamany]

Actualy, there is a way to do that:
It's innefficient, but it does work.

-tg

I thought I'd seen that in ACCESS threads - that does work in MS SQL Server as well?

Interesting...

Why do you say inefficient??