Executing SQL Stored Procedure from ASP

Results 1 to 3 of 3

Thread: Executing SQL Stored Procedure from ASP

  1. #1
    Join Date
    Dec 1969

    Default Executing SQL Stored Procedure from ASP

    Trying to figure out how to execute a SQL stored procedure from an ASP page and have the results of the procedure shown on that ASP page. I also would like to be able to pass a value back to the SQL Stored P.<BR><BR>Any ideas, suggestions folks??<BR><BR>Robert

  2. #2
    peterjl@austec.net.au Guest

    Default Please checkout the FAQ's first

    This is one of the most asked questions in this forum, and it is amply answered in the ASPFAQ:<BR><BR>http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=76

  3. #3
    IanMJ Guest

    Default RE: Executing SQL Stored Procedure from ASP

    Hi Robert,<BR><BR>You create a stored procedure in SQL Server by right clicking on the Stored Procedures icon within your database and selecting &#039;New Stored Procedure&#039;. SQL server puts some default code in there...<BR><BR>CREATE PROCEDURE (PRODECURE NAME) AS<BR><BR>Change that code and give the procedure a name like this...<BR><BR>CREATE PROCEDURE sp_myprocedure @variable int AS<BR><BR>Then create your SQL query. The @variable int is a parameter that you candefined in the procedure to ensure that sql server listens for a valule to pass into your procedure. in this case I&#039;m creating an integer variable called variable.<BR><BR>So you&#039;ll end up with something like...<BR><BR><BR>CREATE PROCEDURE sp_myprocedure @variable int AS<BR><BR>SELECT * from myTable where something = avalue<BR><BR><BR>Then when you create your ADODB connection in asp, instead of creating an sql statement that reads "SELECT * FROM WHATEVER" (or whatever), you create an sql statement that calls your procedure and passes the value in the statement like this:<BR><BR>Dim objRS,sqlStat<BR>sqlStat = "sp_myprocedure " & Session("User_id")<BR>set objRS=Server.CreateObject("ADODB.Recordset")<BR>ob jRS.Open sqlStatProd, objConn<BR><BR>This&#039;ll execute your procedure and put the return values into your recordset objRS.<BR><BR>I hope this makes sense, I&#039;ve had a few glasses of Wine with me dinner :)<BR><BR>Any probs, let me know.<BR><BR>Regards,<BR><BR>Ian<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts