Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Creating stored procedure via ASP
 New Topic  Topic Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 04 January 2005 :  22:18:05  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
I've read plenty of articles on Stored procs, none of which explained how to create a stored proc via an ASP page, they all refer to creating one in EM, them calling it via asp...

So how would one go about creating a stored proc via ASP? for example just something simple such as the following:


CREATE PROCEDURE sp_CreateTable
As
CREATE TABLE TableName (blah blah blah, blah blah blah)
Go

-Stim

dayve
Forum Moderator

USA
5820 Posts

Posted - 04 January 2005 :  22:26:06  Show Profile  Visit dayve's Homepage
Creating stored procedures is done on the database server and not within ASP. You can EXECUTE stored procedures from ASP.

Here is a good introduction into Stored Procedures in an ASP environment.

http://www.macronimous.com/resources/stored_procedures_for_ASP_and_VB_Programmers.asp


Edited by - dayve on 04 January 2005 22:28:04
Go to Top of Page

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 04 January 2005 :  22:32:31  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
I need a way to do it via ASP...

if it's not possible oh well... I'll stick with SQL strings

-Stim
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts

Posted - 04 January 2005 :  22:50:10  Show Profile  Visit dayve's Homepage
It's not possible. Not natively anyway.

Go to Top of Page

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 04 January 2005 :  23:12:34  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
Ok just to clarify... is a Stored Procedure the same as a stored query is in access? For example, when you open up an Access db you have "tables", "queries", "forms", "reports"... so on...

-Stim
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts

Posted - 04 January 2005 :  23:39:15  Show Profile  Visit dayve's Homepage
No it is not the same, Stored Procedures are actual code that is handled by the database server to perform certain functions/operations, not just queries.

Go to Top of Page

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 04 January 2005 :  23:42:27  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
*starts scouring the net*

There's got to be a way... what about all those downloadable apps that use stored procs?...

You mean to tell me that they setup the sql database via ASP but the end user has to create the stored procs via EM?

-Stim
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts

Posted - 04 January 2005 :  23:50:20  Show Profile  Visit dayve's Homepage
dude, read the link I posted. that will give you some good basic info.
quote:
You mean to tell me that they setup the sql database via ASP but the end user has to create the stored procs via EM?

Not sure what you mean by this statement, but end users do not create stored procedures. In larger organizations, programmers request stored procedures to be created by the database administrators and/or programmers are given a library of stored procedures available at their disposal.

Some reasons for creating stored procedures is simply to have the database handle some of the operations in mass updates of relational databases. This makes the process a lot faster than handling it within a web application.

Go to Top of Page

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 05 January 2005 :  00:00:20  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
I read the link... I understand all about using stored proc's via asp, but I want to create one to be used, from asp.

I dont know if I'm getting what I'm trying to do across clearly enough...

I'm creating an ASP application, and for some of the queries I'd like to use stored procs, specifically for login...

Now when the user gets the code, he starts off with an empty database lets say, I want to create the tables (using sql strings, easy), then use stored procs for login & data retrieval instead of inline sql strings.

Since they start off with an empty database, I presume I need to create these stored procs before I can make use of them, but I'm stuck on creating them without entering EM and doing it that way.

In that link you posted there was:
quote:

3. From ASP Code - Write the procedures, create the connection with the DB using ADO, call the procedures directly from your code.



Yet nowhere in there (that I saw) was outlined how to create a stored proc via ASP/ADO. It explains well how to use ASP/ADO to use a stored proc, but not to create one.

I've got all the code written, and created a couple of test procedures via EM Access 2k2 connected to SQL DB, and tested out using them with some test pages, but I need to create one via ASP/ADO/Whatever if its possible.

-Stim

Edited by - Da_Stimulator on 05 January 2005 00:02:35
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts

Posted - 05 January 2005 :  00:04:42  Show Profile  Visit dayve's Homepage
You can not create a stored procedure from ASP, you can only execute the stored procedure that was created on the database server.

Go to Top of Page

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 05 January 2005 :  00:05:46  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
.... hmm... well that sucks...

Back to oldschool strings

-Stim
Go to Top of Page

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 05 January 2005 :  03:04:39  Show Profile  Visit Gremlin's Homepage
I dont see why you can't via ASP actually, you will of course have to execute the correct TSQL to create the Stored Procedure but it should be doable (I've never personally tried to be honest but all your doing is executing a CREATE query basically).

Kiwihosting.Net - The Forum Hosting Specialists
Go to Top of Page

Doug G
Support Moderator

USA
6493 Posts

Posted - 05 January 2005 :  03:39:13  Show Profile
Gremlin is right :)

I don't have any problems creating sp's from asp, at least within my own network. There may be sql permission issues that I don't see locally.

Otherwise, a simple con.execute "Create Procedure somename AS SELECT whatever FROM table" works fine for me.


======
Doug G
======
Computer history and help at www.dougscode.com
Go to Top of Page

Da_Stimulator
DEV Team Forum Moderator

USA
3373 Posts

Posted - 05 January 2005 :  05:43:36  Show Profile  Send Da_Stimulator an AOL message  Send Da_Stimulator a Yahoo! Message
sweet!

*starts experimenting*

So I can use inline what wouldnt be inline via EM or Access?

IE:

Create Procedure myProc
  @input1,
  @input2
As
  Select @input1, @input2 from wherever
Go


Is valid being

strSql = "Create Procedure myProc @input1 @input2 As Select @input1, @input2 from wherever Go"
Conn.execute(strsql)

?

-Stim

Edited by - Da_Stimulator on 05 January 2005 05:48:07
Go to Top of Page

D3mon
Senior Member

United Kingdom
1685 Posts

Posted - 05 January 2005 :  06:21:43  Show Profile  Visit D3mon's Homepage
yes, that 'should' work in theory :) (Never tried it myself!)


Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won"
Go to Top of Page

dayve
Forum Moderator

USA
5820 Posts

Posted - 05 January 2005 :  11:05:11  Show Profile  Visit dayve's Homepage
When it works, let me know. I'll then properly remove my foot from my mouth.

I'm curious as to the limitations you will run into though because I have some pretty in depth stored procedures on my sql server. Besides, even if it was possible, what benefit would you receive from using ASP to create a stored procedure? Certainly it will add some latency to the process you are trying to create/run.

I'd still have to see it really work. Call me "Doubting Thomas".

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000 Version 3.4.07