Stored Procedures In TransactSQL Creative Commons License Curt

Stored Procedures In Transact-SQL Creative Commons License – Curt Hill

Introduction • In a previous presentation we examined the programming features of SQL Server’s extensions to SQL • What we did not cover are Procedures or Functions – Important aspects of real programming • Today we fix that • First, how to execute • Second, a sampling of predefined stored procedures • Third, how to define Creative Commons License – Curt Hill

Executing a procedure • A stored procedure is started by the Execute statement – Which may be abbreviated to Exec • The format is: EXEC[UTE] [ @ret = ] spname [ parms ] – Where – Spname is the name of procedure – The return value is optional and placed in a previously declared variable – The parameters vary per procedure Creative Commons License – Curt Hill

Notes • The parameters may be defined by name or positionally • To define by name use: @parm = value • The values passed to a stored procedure may be constants, variables or table values • Next we will survey a few predefined stored procedures and then look at some example calls Creative Commons License – Curt Hill

Some predefined • Predefined stored procedures have a common naming convention – Always starts with sp_ or xp_ • There are more than 350 predefined ones – Just a few of these will be examined Creative Commons License – Curt Hill

List 1 • sp_add_job – adds a batch job to the system • See also sp_add_jobstep and sp_add_jobschedule, sp_update_job, etc. • sp_addlogin – add a login name and password • sp_addmessage – add an error message to system error message table • sp_catalogs – displays catalog listing Creative Commons License – Curt Hill

List 2 • sp_columns – display catalog info for tables or views • sp_databases – display the databases on this server • sp_executesql – execute a statement within a string • sp_help – display help – many others such as sp_helpindex • sp_indexes – display index information Creative Commons License – Curt Hill

List 3 • sp_lock – displays information in locks • sp_monitor – displays information on resources • sp_password – update the password for a login • sp_primarykeys – duh • sp_rename • sp_tables Creative Commons License – Curt Hill

Execute examples Creative Commons License – Curt Hill

The other prefix • There a series of stored procedures that start with xp_ • These are typically system commands, rather than database commands • An example is: xp_cmdshell ‘cmd’ • The cmd is passed to windows to be executed Creative Commons License – Curt Hill

Defining our own • We may also define our own stored procedures • A stored procedure is just that: Transact SQL code that is stored on the server • It may then be used just like a predefined • The Create Procedure command creates and stores it on the server Creative Commons License – Curt Hill

Create Procedure • Form: CREATE PROC[EDURE] procedure_name [; number] parms as statements • Where – Procedure_name is the name – Number is optional and creates a group of procedures – Parms is discussed next – Statements are the body of the procedure Creative Commons License – Curt Hill

Parameters • There are zero or more parameters • The form: @parm type [VARYING] [= value] [OUTPUT] • The name is first, followed by the type • Varying is only used if this is a cursor type • Output means that the parameter is a reference type • The = gives a default value Creative Commons License – Curt Hill

Notes • The parameter must follow the normal naming convention – Start with @ if normal variable – Start with letter if cursor • Cursors must use varying and output Creative Commons License – Curt Hill

Connection • Stored procedures belong to a particular database • Therefore the use is almost always required • However, Create Procedure must be the first command in a batch • Thus separate with a GO Creative Commons License – Curt Hill

Example 1 • A stored procedure that does a simple query: Create Procedure My. Proc AS Select f_name, f_naid From faculty Where f_age > 40 • Stored procedures are precompiled, so executing this is faster than the normal query Creative Commons License – Curt Hill

Return value • A stored procedure may return an integer only • It does this with the return statement • The form of the execute then becomes: exec @var = Proc parms Creative Commons License – Curt Hill

Count Rows • Here is an example that will count rows in this query – This could be done easily with an aggregate function but this is a nice example • There are two pieces – The stored procedure – The use of it Creative Commons License – Curt Hill

Counter Definition use college go Create Proc Counter as return declare @count int, @name varchar(20) set @count = 0 declare curses Cursor for select f_name from faculty where f_age > 40 open curses fetch next from curses into @name while (@@FETCH_STATUS >= 0) begin set @count = @count + 1 fetch next from curses into @name end deallocate curses @count Creative Commons License – Curt Hill

Counter Use use college declare @acount int set @acount = -11 exec @acount = Counter print @acount Results in no table output but only this in messages 14 Creative Commons License – Curt Hill

Output values • If the procedure needs to produce something other than one integer, then reference parameters are needed • These have OUTPUT as a suffix on the parameter declaration • They also need these in the actual parameter list • What does this tell us about Microsoft designed languages? Creative Commons License – Curt Hill

Last Example • Lets find the highest and lowest ratios again • The stored procedure is simliar to the version in the Transact SQL presentation • Only the prefix is different Creative Commons License – Curt Hill

High Low Defined use college go create proc high_low @least_ratio real output, @least_name varchar(20) output, @most_ratio real output, @most_name varchar(20) output as declare fac_curse cursor for select f_name, f_age, f_years from faculty where f_age > 40 open fac_curse -- Rest is same Creative Commons License – Curt Hill

High Low Called use college declare @lr real, @mr real, @ln varchar(20), @mn varchar(20) exec high_low @lr output, @ln output, @mr output, @mn output print 'Lowest '+@ln + ' ' + convert(varchar(20), @lr) print 'Highest '+@mn + ' ' + convert(varchar(20), @mr) Creative Commons License – Curt Hill

Execute again • The normal purpose of the Execute command is to start a stored procedure • It has another use as well • It may execute a string – Literal – Variable • The string should be a query or other executable command Creative Commons License – Curt Hill

Notes • The string option allows us to pass a query or other command to a stored procedure • This will mess with optimization • Normally a stored procedure is precompiled – However it cannot precompile a command it has not received Creative Commons License – Curt Hill

Other procedure commands • We remove procedures with: DROP PROCEDURE proc_name • This must be executed within the correct database • We may also Alter the stored procedure Creative Commons License – Curt Hill

Functions • A function is a procedure that returns a value • The syntax is similar: Create Function name ( @parameter type, … ) Returns type Begin body Return expression End Creative Commons License – Curt Hill

There is More • SQL Server has no monopoly on these things • Oracle, My. SQL, Postgre. SQL, DB 2 among others, have both procedures and functions • There should be no surprise that – The capabilities are similar – The syntax is different Creative Commons License – Curt Hill

Finally • A stored procedure saves us work for frequently used code • It is faster than the normal because it is precompiled • It may also be used by anyone on the database Creative Commons License – Curt Hill