Stored Procedure

Format | Sytem SP | User SP | Temporary SP | Parameter& Eg

EXECUTION (query analyzer )
1.Execute SP manually: Open Stored Procedure, right click SP, Open and execute /debug
2.Execute automatically [|>] : EXEC sp_name ‘parameter1’, ‘parameter2’ in panel
3.Show Code: Right drag SP and Create on panel

FORMAT

Sql Query: Select column1, column2 From Table Where conditions

CREATE PROCEDURE dbo.sp_myStoredProcedure
AS
Select column1, column2 From Table1
Go
EXEC sp_myStoredProcedure 0, ‘ the string’ , 3.51

Back

SYSTEM STORED PROCEDURE
- Automatically created when SQL server installed
- [master\Stored Procedure]. Can be accessed from any DB. Eg , northwind , model
- Has sp_prefix . eg . sp_helpdb , sp_helptext , sp_helprotect
- sp_helpdb // list out general information all db in system

eg.
Use northwind
go
sp_helpdb

- exec sp_helptext ‘sp_name’ // list stored procedure code

Back

USER-DEFINED STORED PRCEDUIRE(created by user)
- Command : CREATE PROC name eg. Create Proc showdbname
- Created [Master] with sp_ , can be access from other db. /* Look current first, then master */

eg
Use master
Go
CREATE PROC sp_showdbname
AS
Select ‘master’

Use Pubs

Exec sp_showdbname //it execs sp in master from pubs cos pubs has no sp_showdbname
Go

Back

TEMPORARY STORED PROCEDURE
- Stored Procedure created by user, stored in tempdb, drop when no connection.
- Or use DROP PROCEDURE eg. Drop procedure showdbname
- local Store Pro : # Global : ##

eg.

Create Proc #getdbname
As
Select db_name() AS database_name
Go

eg
Use northwind
Go
CREATE PROC sa.getcurrentime //stored [northwind]
As
Select current_timestamp //it is system object
Go
EXEC sp_help ‘getcurrentime’
EXEC sp_helptext ‘getcurrentime’

Result

getcurrentime dbo stored procedure 2006-01-11 09:43:10.533

CREATE PROC getcurrentime
As
Select current_timestamp

2. Use master

go
CREATE TABLE sqlstatus ( //create table sqlstatus
Lasttime DATETIME
)

go

CREATE PROC insertsqlstatus //create stored procedure
As
Insert sqlstatus value (current_timestamp)
Go

EXEC sp_procoption ‘insertsqlstatus’ , ‘ startup’ , ‘true’

Back

PARAMETER IN SP(@var =local variable , @@var=global var)

- sysobjects (table) : store general info
- syscomments : stores SP code
- syscolumns: stores param info and table attribute

eg
1. use northwind

CREATE PROC dbo.getemployeelastname
@emlastname varchar(40)
AS
Select * from Employees where lastname like ‘%’+@emlastname + ‘%’

Go

2. use northwind

CREATE PROC dbo.ListCustomersByCity
@Country nvarchar(30)='%' // any char
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City

3. use northwind

go // split the code. Create must be after GO

CREATE PROC dbo.getemployeeaddress
@employeeid int
As
Declare @employeeaddress nvarchar(120)
set @employeeaddress='select address, city ,region, postalcode,country
from employees where employeeid=' + @employeeid '
exec ( @employeeaddress)
GO

Back

Create SP ( SQL Server Management Studio)
1. DB > Programbility>RC Stored Procedure > New SP
2. Query>Specify value for template params
* author, date , params, dataype
3. Repalce statement after AS " Select * from customers where city=@city"
>parse (check synax) > Execute (create sp)

Modify SP
1. DB>Programmabilty>Store Procedures>RC> Modify> Make changes> Parse > execute.

Execute SP
1. SP> Execute Stored procedure> supply params > OK

OR

use northwind
go
exec CustomerInfo 1

Stored Procedure vs User Defined Function