Calling sprocs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calling sprocs

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-03-06 : 05:05:46

Hi,

I was wondering if anybody knows the fastest or the best way to call a stored proc from an asp page with and without parameters. Also can you call more than one at a time within the same connection instead of opening a new connection everytime.

Thanks

Leah

Edited by - leahsmart on 03/06/2003 05:21:25

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-06 : 13:18:16

There's no "fastest and best" way. You're probably using ADO, read up on ADO Command objects in MSDN. And yes, you can call more than one command on one connection , although they will run synchronously by default (synchronously means that the command will not return until it's done processing), so the commands will not be running in parallel. You do not need to open a new connection, although connection pooling or disconnecting users and reconnecting them is common to conserve connection resources.
The basic syntax to calling a stored proc is (with vb syntax):
Dim cmd as new adodb.command
with command
.activeconnection = conn
.commandtype = adcmdstoredproc
.commandtext = storedprocname
.parameters(1).value = val1
.parameters(2).value = val2
.execute
end with
with .parameters(0).value .... (this holds the return value)

Sarah Berger MCSD

leahsmart
Posting Yak Master

133 Posts

Posted - 2003-03-07 : 04:30:27

Hi, Thanks for your reply. How do you send a named parameter? My sproc requests a parameter called @CountyLetter and I get an error:

Procedure 'sp_Map_Counties' expects parameter '@CountyLetter', which was not supplied.

How do I name the parameter?

Set cmd_Counties = Server.CreateObject("ADODB.Command")
with cmd_Counties
.activeconnection = con_Company
.commandtype = 4
.commandtext = "sp_Map_Counties"
.parameters(1).value = Request("CountyLetter")
.execute
end with

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-07 : 04:49:01

See

www.nigelrivett.com
Call stored procedures from ASP

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources