Please Help with Stored Proc 
Author Message
 Please Help with Stored Proc

I am trying to write a stored procedure in SQL Server 7 that will be
called from an ASP page. Here is what I want to do:

CREATE PROCEDURE insert_coupon

)



generated from a nested query, like 'SELECT product_id FROM products
WHERE product_type = 1' or something like that, but I get an error
saying that I can't do a subquery in this context.

Is there any way to get that product_id parameter from a SQL

variable, nor how to assign it the value returned from the SELECT
statement.

Anyone who can help, please respond, but please don't bombard me with
stuff like "Why don't you just do this?"  I need to do it this way for
reasons too numerous to get into.  Just tell me if there is a way to
write this procedure, and if so, how to go about it.

Thanks in advance.

Sent via Deja.com http://www.***.com/
Before you buy.



Tue, 08 Oct 2002 03:00:00 GMT
 Please Help with Stored Proc

I think your problem has a fairly simple answer but I'm not real clear
on your ddl for the coupons table.
Will it have a row for each product_id that the CouponNumber is related
to? (I hope), or do you require one row that has the CouponNumber and

comma separated string? (Ugh not even in the 1st normal form!)


Quote:

> I am trying to write a stored procedure in SQL Server 7 that will be
> called from an ASP page. Here is what I want to do:

> CREATE PROCEDURE insert_coupon
> (

> )

> INSERT INTO coupons (coupon_number, product_id) VALUES


> generated from a nested query, like 'SELECT product_id FROM products
> WHERE product_type = 1' or something like that, but I get an error
> saying that I can't do a subquery in this context.

> Is there any way to get that product_id parameter from a SQL

> variable, nor how to assign it the value returned from the SELECT
> statement.

> Anyone who can help, please respond, but please don't bombard me with
> stuff like "Why don't you just do this?"  I need to do it this way for
> reasons too numerous to get into.  Just tell me if there is a way to
> write this procedure, and if so, how to go about it.

> Thanks in advance.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Tue, 08 Oct 2002 03:00:00 GMT
 Please Help with Stored Proc
Yes, you are correct.  The same coupon may exist in several rows, each
row having a different product_id from the product table.
Ex.

coupon_number   product_id
-------------   ----------
ABC123              57
ABC123              93

As I enter the new coupon, I need to grab the appropriate id from the
product table.

Does this help?  You're right.  It seems like it should be a fairly
simple thing.  Possibly I could execute the subquery (SELECT statement)
and assign the result to a variable in the SP.  Then pass THAT variable
in as the product_id, but I have no clue how to do this.

Can you help?  If not, thanks for your reply anyway.

Bawlmer

Sent via Deja.com http://www.deja.com/
Before you buy.



Tue, 08 Oct 2002 03:00:00 GMT
 Please Help with Stored Proc
Now I understand. You won't need a variable for the product_id's
affected.

I think this will work for you:


as
insert into coupons
(coupon_number, product_id)


Quote:

> Yes, you are correct.  The same coupon may exist in several rows, each
> row having a different product_id from the product table.
> Ex.

> coupon_number   product_id
> -------------   ----------
> ABC123              57
> ABC123              93

> As I enter the new coupon, I need to grab the appropriate id from the
> product table.

> Does this help?  You're right.  It seems like it should be a fairly
> simple thing.  Possibly I could execute the subquery (SELECT
statement)
> and assign the result to a variable in the SP.  Then pass THAT
variable
> in as the product_id, but I have no clue how to do this.

> Can you help?  If not, thanks for your reply anyway.

> Bawlmer

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Tue, 08 Oct 2002 03:00:00 GMT
 Please Help with Stored Proc
Thanks, SQLBoy.  I will try this as soon as I can and let you know.
The actual SQL call is a little bit bigger.  The INSERT statement is
inserting 8 values, but seven of them are being passed into the SP from
ASP.  The only one not passed in is the product_id which I need to get
from the product table (the SELECT statement).  I will try this and see
if it works.

Thanks again, and I'll get back to you.

Bawlmer

Sent via Deja.com http://www.deja.com/
Before you buy.



Tue, 08 Oct 2002 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Please Help, Nested stored proc is inconsitent

2. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

3. ADO Stored Proc...AAAAAAAAAAAARRG please help

4. please help stored proc issue

5. Stored Proc Date conversion Problem, Please Help

6. PLEASE HELP - Dynamic SQL in a stored proc

7. PLEASE HELP - stored proc

8. calling Stored Proc with InputOutput params - help please!

9. -----need help with stored proc---PLEASE

10. calling Stored Proc with InputOutput params - help please!

11. !!!! PLEASE HELP ::stored proc call dies with ODBC

12. Query/View/Stored Proc structure - please help!?


 
Powered by phpBB® Forum Software