| Author |
 Topic  |
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
 Posted - 04 January 2005 : 22:18:05
|
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 |
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 04 January 2005 : 22:32:31
|
 I need a way to do it via ASP...
if it's not possible oh well... I'll stick with SQL strings |
-Stim |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 04 January 2005 : 22:50:10
|
| It's not possible. Not natively anyway. |
 |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 04 January 2005 : 23:12:34
|
| 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 |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 04 January 2005 : 23:39:15
|
| 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. |
|
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 04 January 2005 : 23:42:27
|
*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 |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 04 January 2005 : 23:50:20
|
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. |
|
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 05 January 2005 : 00:00:20
|
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 |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 05 January 2005 : 00:04:42
|
| You can not create a stored procedure from ASP, you can only execute the stored procedure that was created on the database server. |
|
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 05 January 2005 : 00:05:46
|
.... hmm... well that sucks...
Back to oldschool strings |
-Stim |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 January 2005 : 03:04:39
|
| 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
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 05 January 2005 : 03:39:13
|
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 |
|
|
|
Da_Stimulator
DEV Team Forum Moderator
USA
3373 Posts |
Posted - 05 January 2005 : 05:43:36
|
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 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 05 January 2005 : 11:05:11
|
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". |
|
|
|
|
Topic |
|
Topic Locked
