stored procedure not recognized

[Laura K]

I posted yesterday and got no response. I have more info so I will post
again. I am using a stored proc which loops through three tables until it
finds a product ID. When the ID is found it puts the values into a
temporary table. Then the item's sizes are listed. I can not get
dreamweaver to take the procedure and create a dataset. It gives me an
unknown error has occured. It has something to do with the syntax of the
proc becaues another simplier proc works in DW.

Can someone look at my code and give me an idea? It works just fine in the
query analizer.

Thanks
Laura K

I am using asp.net vb

PROCEDURE spGetProductSizes3
@prodId int
AS

create table #temp (pSize varchar(50))

insert into #temp (pSize)
select (w.strWaistSize + ' X ' + l.strInseam) as pSize
from tblJctProductsWaistSizes as s
JOIN tblWaistSizes as w
on s.intWaistSizeID = w.intWaistSizeID
JOIN tblinseamsizes as l on l.intInseamSizeId=s.intInseamSizeId
where s.intProductId=@prodId

-- Pants do not have a waist size so look into sizes table
IF @@ROWCOUNT = 0
BEGIN
insert into #temp (pSize)
select s.strSize as pSize
from tblSizes as s
JOIN dbo.tblJctProductsSizes as ps on ps.intSizeId = s.intSizeId
where ps.intProductId = @prodId
END

--Finally go into the shoes
if @@ROWCOUNT = 0
BEGIN
insert into #temp (pSize)
select (convert(varchar(10),ss.intShoeSize) + ' X ' + sw.strWidth) as
pSize
from dbo.tblShoeSizes as ss
JOIN dbo.tblJctProductsShoeSize s
on s.intShoeSizeID = ss.intShoeSizeID
join dbo.tblShoeWidths as sw
on sw.intShoeWidthID = s.intShoeWidthID
where s.intProductID = @prodId
END


SELECT pSize
FROM #temp

[Laura K]

Ok worked this one out myself.

Dreamweaver's dialog box doesn't see it but I did it by hand using the exact
same notations that dreamweaver would have used so all is well. Still it
would be nice if DW could have been a little more user friendly.


"Laura K" <klkazanAT@ATcharter.net> wrote in message
news:d62s6s$nto$1@forums.macromedia.com...

>I posted yesterday and got no response. I have more info so I will post
>again. I am using a stored proc which loops through three tables until it
>finds a product ID. When the ID is found it puts the values into a
>temporary table. Then the item's sizes are listed. I can not get
>dreamweaver to take the procedure and create a dataset. It gives me an
>unknown error has occured. It has something to do with the syntax of the
>proc becaues another simplier proc works in DW.
>
> Can someone look at my code and give me an idea? It works just fine in
> the query analizer.
>
> Thanks
> Laura K
>
> I am using asp.net vb
>
> PROCEDURE spGetProductSizes3
> @prodId int
> AS
>
> create table #temp (pSize varchar(50))
>
> insert into #temp (pSize)
> select (w.strWaistSize + ' X ' + l.strInseam) as pSize
> from tblJctProductsWaistSizes as s
> JOIN tblWaistSizes as w
> on s.intWaistSizeID = w.intWaistSizeID
> JOIN tblinseamsizes as l on l.intInseamSizeId=s.intInseamSizeId
> where s.intProductId=@prodId
>
> -- Pants do not have a waist size so look into sizes table
> IF @@ROWCOUNT = 0
> BEGIN
> insert into #temp (pSize)
> select s.strSize as pSize
> from tblSizes as s
> JOIN dbo.tblJctProductsSizes as ps on ps.intSizeId = s.intSizeId
> where ps.intProductId = @prodId
> END
>
> --Finally go into the shoes
> if @@ROWCOUNT = 0
> BEGIN
> insert into #temp (pSize)
> select (convert(varchar(10),ss.intShoeSize) + ' X ' + sw.strWidth) as
> pSize
> from dbo.tblShoeSizes as ss
> JOIN dbo.tblJctProductsShoeSize s
> on s.intShoeSizeID = ss.intShoeSizeID
> join dbo.tblShoeWidths as sw
> on sw.intShoeWidthID = s.intShoeWidthID
> where s.intProductID = @prodId
> END
>
>
> SELECT pSize
> FROM #temp
>