Command: SQL Proc 101

[diggable]

SQL Proc help

I need help accessing SQL Proc results, I've done some searching but no

comprehensive detailed tutorial anywhere. If there's 1 it's for older versions of DW.

What I have:

SQL2005

DWCS4

ASP

I can connect to SQL2005 via ODBC

I can create SQL queries, recordsets and get the result in a page

I can create a form, submit form and return a recordset

I know the basic stuff

Now on to the next challenge:

I have a SQL procedure that if table A exists it drops it,

runs a query,populates the result in  table A

then presents it in a result

Now, I need to translate this to DW using Command: SQL Procedure

--------------------------------------------------------------------------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[NewProductsRangedToStores]
AS
BEGIN
SET NOCOUNT ON;

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].

select distinct spr.StoreID,spr.ProductID,p.ProductDescription,
storerangestartdate StartDate,storerangeenddate EndDate
into A
from storeproductrange spr
join product p
on spr.productid=p.productid
where(storerangestartdate between getdate()      
and convert(nvarchar,getdate()+21,101))
and spr.ProductID=101304433
order by 2,1
END

-------------------------------------------------------------------------------------

Create a page with input query for ProductID

Submit it and returns a page with the SQL PRoc results

What do I need to do? Documentation for COmmand: SQL Procedures

in Adobe's documentation is limited. The new window in creating

SQL Procedures in DW has new variables, there's no info in the documentation:

Name, Type, Direction, Size, Default Value, Run-Time Value

What do I need to do to send the data to SQL Proc?

Should I change the "spr.productid=10130433" to "spr.productid=mmcolparam"

So, base on my SQL Proc above, what/how do I populate the variables?

what do I need to get the "Return Recordset named", do I name this myself?

do I create a sep recordset?

Lots of questions, hoping there's some easier way documentation somewhere.

I have done some searching on this in the forum, but there's no comprehensive detail

about putting it all together.

thanks in advance!

[bregent]

I don't think DW directly supports stored procedures. You'll need to code it by hand. Call the sproc using the command object.

[diggable]

thanks for the reply, I pounded on this thing so far I've gotten a little bit further.

in the COmmand window

in the SQL field box, I entered the sql proc   dbo.newproductsrangedtostores

I then checked the Return Recordset Named: I entered Result1

I thought you have to create a new recordset, but when I entered the recordset I created

it errored out. I decided to name the recordset Result1 then it took it.

Once I clicked OK on the Command Window, it created it in the Bindings window.

Voila! all of the values were listed  Result1.StoreID, Result1.ProductID, Result1.Startdate etc.

It was just like creating a Recordset, but pulling the query from SQL Proc.

So far so good, I then dragged the Result1.storeid etc to the page / table and the result came out.

Now, next challenge is how do I pass a parameter ie. ProductID from a submit form to the SQL Proc?

Unfortunately, I'm not much of a coding person so I wouldn't know how to write code, unless

I copy /paste it from somewhere an dknow which one's to change.

What are these Variables? +-

Name, Type, Direction, Size, Default Value, Run-Time Value

Thanks in advance!