codetoad.com
  ASP Shopping CartForum & BBS
  - all for $20 from CodeToad Plus!
  
  Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || Forums || RAM 
Search Site:



Home » ASP » Article

Create an ASP SQL Stored Procedure

Article by:  Jeff Anderson  ( 1362 ) (3/11/2003)
Bookmark us now! Add to Favourites
Email a friend! Tell a friend
Sponsored by: FindMyHosting - Web Hosting Search
Summary: A beginners guide to setting up a stored procedure in SQL server and calling it from an ASP page.
Viewed: 147160 times Rating (144 votes): 
 4.1 out of 5
  Rate this Article   Read Comments   Post Comments

Create an ASP SQL Stored Procedure


Stored Procedures are very powerful database tools. They are essentially mini-programs and using SQL you can achieve many of the programmatic tricks you might use in an ASP page, including loops, if else statements and so on.

The useful thing about stored procedures is that they separate out the database activity from the displaying. This keeps your ASP pages very simple and makes maintenance a lot easier.

Here I'll introduce you to the basics of using a stored procedure and passing a parameter to it.

CREATING THE STORED PROCEDURE

Open Enterprise Manager, and open your database in the tree structure. Right click on Stored Procedures and select 'New Stored Procedure'

Let's create a procedure called getproducts

Select All Code


Here we have created a variable called
@product_id
and passed it to our SQL select statement. All variables have the @ sign before them in SQL language.

Now, from within your ASP page, you can call this procedure in a number of ways.

As the procedure in this case is a select statement, you will most likely want to receive the results into a recordset:

Select All Code


If however the procedure was an update statment - for example,
update products set delivered=1 where product_id=@product_id

then you might want simply to execute the procedure as follows:

Select All Code







CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums
Rate this article:     Poor Excellent
View highlighted Comments
User Comments on 'Create an ASP SQL Stored Procedure'
Posted by :  Archive Import (Lorna) at 12:42 on Tuesday, July 08, 2003
Thanks! Tried the example on the MS site but this worked first time.
Posted by :  pathak at 00:43 on Wednesday, March 03, 2004
i have this problem that i havent been able to solve for almost a week now.....
i am designing this SQL server backend & ASP frontend software....
how do i update several fields at a time concurrently from the ASP??? the update query needs a primary key for the row to be selected but that primary key is being generated by SQL server as a counter.....so i am just defining the variable where this primary key is being stored. <%=rs(call_id))%> call_id being 1,2,3... upon generation

how do i select attributes pertaining to a particular call_id so that i make changes in that row upon pressing the save button?
Posted by :  atlwebguy at 18:27 on Monday, April 05, 2004
I have a syntax question:

how can i pass multiple variables into the example above. For instance my stored procedure will look something like Select * from myTable Where column1 = @variable1 and column2 = @variable2

........
I can't get the syntax in the .asp page correct. I've tried


Connection.Execute "myStoredProc" & variable1, & variable2

and

Connection.Execute "myStoredProc" & variable1 & variable2

and

Connection.Execute "myStoredProc" & variable1 & variable2 &


and countless others.


Any suggestions? Thanks!
Posted by :  Arniethebomb at 01:10 on Tuesday, April 06, 2004
Hi atlwebguy,

Please try to include space between the stored proc name and the parameters. like
this:

Connection.Execute "MystoredProc " & variable1 & "," & variable2

if your variable1 is a string parameter try this:
Connection.Execute "MystoredProc '" & variable1 & "'," & variable2

If both parameters are string make the second parameter as string also .


regards,

Posted by :  Xenova at 20:33 on Tuesday, April 27, 2004
does this work with mySQL aswell?
Posted by :  jermcode at 00:40 on Saturday, May 15, 2004
Xenova-

MySQL supports stored procedures only in the newest version, but it doesn't support transactions or transactional integrity unless you use berkeleydb or innodb, both of which are not free. The stored procedure support in MySQL is basically a way to store statements, and provides no speed enhancements or compilation at all. Quite the opposite in fact, the manual suggests that the stored procedures will typically take LONGER to execute. This is not the case with commercial RDBMS packages- MSSQL will always be able to execute stored procedures faster.

To answer your question- "yes." But don't bother.

jeremy
Posted by :  jermcode at 00:43 on Saturday, May 15, 2004
>>how do i update several fields at a time concurrently from the ASP???

You can pass multiple parameters, like this:

create proc SX_Update_Employee
(@employeeid int, @firstname varchar(20), @lastname varchar(20),@phone varchar(10))
AS

update employee set firstname=@firstname,lastname=@lastname,phone=@phone where employeeid=@EmployeeID

go


(the employeeid int field is the primary key.)


jeremy
Posted by :  jermcode at 00:49 on Saturday, May 15, 2004

PS- be sure that when you call this from your asp that you fix single quotes (apostrophies) like this:

set db=createobject("ADODB.Connection")
db.open "dsnName","user","password" 'dsn-example, dsnless will work as well
sql = "SX_Update_Employee " & employeeid & ","
sql = sql & "'" & replace(firsname,"'","''") & "',"
sql = sql & "'" & replace(lastname,"'","''") & "',"
sql = sql & "'" & replace(phone,"'","''") & "'"
db.execute sql
db.close 'always clean up after yourself
set db=nothing

*note that doing the sql=sql& part is extremely bad practice and will result in poorly performing execution. I only broke it out that way for clarity in posting!
Posted by :  abinash at 04:59 on Thursday, April 21, 2005
how to send error message to client throw stroed procedure in asp
Posted by :  ranjana at 05:22 on Friday, July 22, 2005
how to update procedure using asp script
Posted by :  sheeri at 16:36 on Sunday, June 11, 2006
hello will anybody help me that i have got a stored procedure in sql which returns a xml through 'for xml explicit' i want to call that stored procedure from a ASP page directly and want to load that xml in normal object of Dom document .... will anybody plz give me a line of code for this...plz..i want it urgently a stored procedures dont accpet any argument..plz....thank u in advance


To post comments you need to become a member. If you are already a member, please log in .

 



RELATED ARTICLES
ASP Format Date and Time Script
by Jeff Anderson
An ASP script showing the variety of date and time formats possible using the FormatDateTime Function.
Creating a Dynamic Reports using ASP and Excel
by Jeff Anderson
A simple way to generate Excel reports from a database using Excel.
Create an ASP SQL Stored Procedure
by Jeff Anderson
A beginners guide to setting up a stored procedure in SQL server and calling it from an ASP page.
ASP Shopping Cart
by CodeToad Plus!
Complete source code and demo database(Access, though SQL compatible) to an ASP database driven e-commerce shopping basket, taking the user through from product selection to checkout. Available to CodeToad Plus! Members
Email validation using Regular Expression
by Jeff Anderson
Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP.
Creating an SQL Trigger
by Jeff Anderson
A beginners guide to creating a Trigger in SQL Server
The asp:checkbox and asp:checkboxlist control
by David Sussman, et al
Checkboxes are similar to radio buttons, and in HTML, they were used to allow multiple choices from a group of buttons.
ASP.NET Forum Source Code
by ITCN
Complete open source website Forum and Discussion Board programmed in Microsoft dot Net 1.1 Framework with Visual Basic.
The asp:listbox control
by David Sussman, et al
The next HTML server control that we'll look at, <asp:listbox>, is very much related to <asp:dropdownlist>.
Concatenate strings in sql
by Jeff Anderson
A brief introduction to concatenating strings in an sql query (using SQL server or access databases).








Recent Forum Threads
•  Nested Javascripts
•  ISP says linefeeds are causing problems
•  Linker Error Unresolved External Borland Builder 2006
•  Have multiple ASP.NET web projects share pages and controls
•  Re: How can I read ASCII data file in C++
•  Trouble writing programs
•  Validation Problem
•  Date formating from user input
•  Need help for writing javascript code global empty fields validation


Recent Articles
What is a pointer in C?
Multiple submit buttons with form validation
Understanding Hibernate ORM for Java/J2EE
HTTP screen-scraping and caching
a javascript calculator
A simple way to JTable
Java Native Interface (JNI)
Parsing Dynamic Layouts
MagicGrid
Caching With ASP.Net


© Copyright codetoad.com 2001-2006