Help with Store Procedures

[welshone]

Hello,
I usually put my SQL queries embedded into my asp pages, but I want to start using stored procedures ( MS SQL 7).

how can I change this to a store procedure and pass parameters from my asp page to the sp ?
;
strSQL = ""
'set connection string to local variable-I use a DSN-less connection

Set conn = Server.CreateObject("ADODB.Connection&quot
conn.open "EdNoticeBoard","Ednoticeboard","ednoticeboard"

%>
<%
'build the sql statement based on the input from the form
strSQL = &quot;INSERT INTO tblInTouch(Title, Memo, MyType, FileLocation, Name, Email, Telephone, StartDate, EndDate, UserIP, UserName)&quot;
strSQL = strSQL & &quot; SELECT &quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;Title&quot & &quot;' as Title,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;Memo&quot & &quot;' as Memo,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;MyType&quot & &quot;' as MyType,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;FileLocation&quot & &quot;' as FileLocation,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;Name&quot & &quot;' as Name,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;Email&quot & &quot;' as Email,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;Telephone&quot & &quot;' as Telephone,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;StartDate&quot & &quot;' as StartDate,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;EndDate&quot & &quot;' as EndDate,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;UserIP&quot & &quot;' as UserIP,&quot;
strSQL = strSQL & &quot;'&quot; & request(&quot;UserName&quot & &quot;' as UserName&quot;

conn.execute(strSQL)

thank you in advance,
Jamie

 

[cfk]

For a single database call the efficiency of your server
will not be greatly changed whether you use an sp or
run the sql in you page,

First your sp

CREATE PROCEDURE sp_insertthing
@Title VARCHAR(50),
@Memo VARCHAR(500),
@MyType VARCHAR(10),
@FileLocation VARCHAR(50),
@Name VARCHAR(50),
@Email VARCHAR(50),
@Telephone VARCHAR(50),
@StartDate DATETIME,
@EndDate DATETIME,
@UserIP VARCHAR(15),
@UserName VARCHAR(50)
AS
INSERT INTO INTO tblInTouch(Title, Memo, MyType, FileLocation, Name, Email, Telephone, StartDate, EndDate, UserIP, UserName)
VALUES (@Title,@Memo etc)

NEXT YOU ASP

DIM co
SET co = SEREVR.CreateObject(&quot;adodb.command&quot
SET co.ActiveConnection = cn
co.CommandType = 4
co.CommandText = &quot;[name of sp]&quot;
co.Parameters.Append co.CreateParameter(&quot;@Title&quot;,200,1,5)
do for all params
co(&quot;@Title&quot = Request.Form(&quot;Title&quot
do for all again
co.Execute
SET co = NOTHING

BIT of a pain for one query

the createparam example is for a varchar
for int use
co.Parameters.Append co.CreateParameter(&quot;@int&quot;,3,1)
and for an output parameter use
co.Parameters.Append co.CreateParameter(&quot;@int&quot;,3,2)

Sorry its a bit rushed.
Its 5.23 and i've had enough of work
so i'm of to the pub for a pint.

 

[welshone]

Cheers cfk,

however , looks like there is no point, I didn't realise I needed to do :
co.Parameters.Append co.CreateParameter(&quot;@Title&quot;,200,1,5)
for all parameters to be passed to the sp.

looks lke using sp's is more mork than necessary !
espcially if it isn't going to increase the query speed.

thanks again,
Jamie