scriptygoddess

I've been learning about stored procedures and calling them from ASP pages. So just wanted to jot down some examples/templates…

(note from my technical editor, Mike: This code is specific to MS SQL Server and probably wouldn't work with other databases.)

Stored Procedure:

/*set DataBase*/
Use enterDatabaseNameHere
/*Check to see if table exists*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enterTableNameHere]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[enterTableNameHere]
GO
/*Create Table*/
CREATE TABLE [dbo].[enterTableNameHere] (
[firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phonenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emailaddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[timestamp] [datetime] NOT NULL,
[idnumber] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
/*Grant permission On table */
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[enterTableNameHere] TO [enterDBUserNameHere]
GO
/********************************************************/
/*Create Stored procedure that inserts data */
/*Check to see if it already exists*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enterStoredProcedureNameHere]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[enterStoredProcedureNameHere]
GO
/*Create Stored Procedure that inserts data */
CREATE PROCEDURE enterStoredProcedureNameHere
@vcfirstname varchar (50),
@vclastname varchar (50),
@vcaddress1 varchar (50),
@vccity varchar (50),
@vcstate varchar (50),
@vczip varchar (50),
@vcphonenumber varchar (50),
@vcemailaddress varchar (50)
As
DECLARE @dttimestamp datetime
SET @dttimestamp=Getdate()
INSERT INTO enterTableNameHere
(firstname, lastname, address1, city, state, zip, phonenumber, emailaddress, timestamp)
VALUES (@vcfirstname, @vclastname, @vcaddress1, @vccity, @vcstate, @vczip, @vcphonenumber, @vcemailaddress, @dttimestamp)
GO
/*Grant permission On Stored Procedure */
GRANT Execute On [dbo].[enterStoredProcedureNameHere] To [enterUserNameHere]
GO
/********************************************************/
/*Create stored procedure that selects data */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enterSelectingStoredProcedureNameHere]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[enterSelectingStoredProcedureNameHere]
GO
CREATE PROCEDURE enterSelectingStoredProcedureNameHere
AS
SELECT * from enterTableNameHere
GO
/* Grant permission On stored procedure */
GRANT Execute On [dbo].[enterSelectingStoredProcedureNameHere] To [enterDBUserNameHere]
GO

Run all that in the SQL Query Analyzer in your database. You'll only need to do that once.

Calling/Using the stored procedure from your ASP page:

<%@ Language=VBScript %>
<%
option explicit
response.Buffer = True
%>
<!– #include file="adovbs.inc" –>
<%
'declare your vairables
'DB connection related variables
dim cn
dim cmd
dim rs
'variables from form
dim pfirstname
dim plastname
dim paddress1
dim pcity
dim pstate
dim pzip
dim pphonenumber
dim pemailaddress
set cn=server.CreateObject("ADODB.connection")
set cmd=server.CreateObject("ADODB.command")
'Define your parameters using the following format:
'Set myParameter = Command.CreateParameter (Name, [Type – in database], [Direction], [Size – in database], [Value – I'm setting this later])
set pfirstname=cmd.CreateParameter("vcfirstname",adVarChar,adParamInput, 50)
set plastname=cmd.CreateParameter("vclastname",adVarChar,adParamInput, 50)
set paddress1=cmd.CreateParameter("vcaddress1",adVarChar,adParamInput, 50)
set pcity=cmd.CreateParameter("vccity",adVarChar,adParamInput, 50)
set pstate=cmd.CreateParameter("vcstate",adVarChar,adParamInput, 50)
set pzip=cmd.CreateParameter("vczip",adVarChar,adParamInput, 50)
set pphonenumber=cmd.CreateParameter("vcphonenumber",adVarChar,adParamInput, 50)
set pemailaddress=cmd.CreateParameter("vcemailaddress",adVarChar,adParamInput, 50)
'open your database connection. Edit where noted
cn.open "Provider=SQLOLEDB.1;Password=enterYourPassword;User ID=enterYourUsername;initial Catalog=enterYourDatabaseName;Data Source=enterYourSQLBoxName"
set cmd.ActiveConnection=cn
cmd.CommandText="enterYourStoredProcedureName"
'append variables to parameters
cmd.Parameters.Append pfirstname
cmd.Parameters.Append plastname
cmd.Parameters.Append paddress1
cmd.Parameters.Append pcity
cmd.Parameters.Append pstate
cmd.Parameters.Append pzip
cmd.Parameters.Append pphonenumber
cmd.Parameters.Append pemailaddress
'set variables values (using data from incoming form)
pfirstname.Value=request.form("firstname")
plastname.Value=request.form("lastname")
paddress1.Value=request.form("address1")
pcity.Value=request.form("city")
pstate.Value=request.form("state1")
pzip.Value=request.form("zip")
pphonenumber.Value=request.form("phonenumber")
pemailaddress.Value=request.form("emailaddress")
'execute stored procedure
set rs=cmd.Execute (,,adCmdStoredProc)
'close out all connections
set rs=nothing
set cmd=nothing
set cn=nothing
'send to confirm page
response.redirect ("confirm.asp")
%>