Link to home
Start Free TrialLog in
Avatar of Mateen
Mateen

asked on

How to call a sql server stored procedure from powerbuilder

I have created the following procedure in sql server 2000

-- procedure starts
declare @year smallint
declare @month smallint
declare @company_code varchar(2)

set @year=2003
set @month=10
set @company_code='02'


declare @budget_n_exp table
      (ser_no int identity(1,1),
      dept_name varchar(70),
      expense numeric(10,0),
      budget numeric(10,0),
      mth smallint,
      yr  smallint,
      per smallint)

while @month between 10 and 12
begin
      insert @budget_n_exp
     select       x.dept_name,
      expense=round(sum(x.iss_qty * x.rate),0),
      budget = isnull(sum(distinct budget_det.budget_qty),0),
      mth=@month,
      yr=@year,
      per=@month-9
from
(
SELECT       dept_code=issm.iss_to,
      dept_name=dept.dept_name,
      item_code=issd.item_code,
            iss_qty = sum(issd.issue_qty),  
      rate    =
      (SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
          FROM      str_receive_det recd,  
                    str_receive_mst recm
      WHERE ( recm.trans_no     = recd.trans_no )
        and ( recm.company_code = recd.company_code )  
        and ( recd.item_code    = issd.item_code)
        and ( recm.company_code = @company_code)
        and ( year(recm.trans_date)      <= @year)
        and ( month(recm.trans_date)      <=
        case when year(recm.trans_date) = @year then @month else 12 end))

FROM       str_issue_det issd,  
             str_issue_mst issm,
       str_item item,
       department dept
WHERE ( item.item_code = issd.item_code )
  and ( issm.trans_no =  issd.trans_no  )
  and ( issm.company_code = issd.company_code )
  and ( dept.dept_code = issm.iss_to)
  and ( year(issm.issue_date) = @year )
  and ( month(issm.issue_date) = @month )
  and ( issm.company_code = @company_code)
group by issm.iss_to,
       dept.dept_name,
       issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
  and budget_det.year_no  = @year
  and budget_det.month_no = @month
group by x.dept_name

set @month=@month+1
end


set @month=1
while @month between 1 and 9
begin
      insert @budget_n_exp
     select       x.dept_name,
      expense=round(sum(x.iss_qty * x.rate),0),
      budget = isnull(sum(distinct budget_det.budget_qty),0),
      mth=@month,
      yr=@year+1,
      per=@month
from
(
SELECT       dept_code=issm.iss_to,
      dept_name=dept.dept_name,
      item_code=issd.item_code,
            iss_qty = sum(issd.issue_qty),  
      rate    =
      (SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
          FROM      str_receive_det recd,  
                    str_receive_mst recm
      WHERE ( recm.trans_no     = recd.trans_no )
        and ( recm.company_code = recd.company_code )  
        and ( recd.item_code    = issd.item_code)
        and ( recm.company_code = @company_code)
        and ( year(recm.trans_date)      <= @year+1)
        and ( month(recm.trans_date)      <=
        case when year(recm.trans_date) = @year+1 then @month else 12 end))

FROM       str_issue_det issd,  
             str_issue_mst issm,
       str_item item,
       department dept
WHERE ( item.item_code = issd.item_code )
  and ( issm.trans_no =  issd.trans_no  )
  and ( issm.company_code = issd.company_code )
  and ( dept.dept_code = issm.iss_to)
  and ( year(issm.issue_date) = @year+1 )
  and ( month(issm.issue_date) = @month )
  and ( issm.company_code = @company_code)
group by issm.iss_to,
       dept.dept_name,
       issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
  and budget_det.year_no  = @year+1
  and budget_det.month_no = @month
group by x.dept_name

set @month=@month+1
end
-- procedure end
now in my actual database
select * from @budget_n_exp
give following result
ser        dept_name          expense  budget  mth       yr           per
1      ACCOUNT       24716      2000      10      2003      1
2      AIR JET       44179      143750      10      2003      1
3      AIR JET       1218      5000      10      2003      1
4      AIR JET       78119      63250      10      2003      1
5      BOILER      36238      23000      10      2003      1
6      CHILLER       57696      92000      10      2003      1
7      ELETRICA      2989      5750      10      2003      1
8      FOLDING      25860      5750      10      2003      1
9      GENERAL       34668      23000      10      2003      1
10      INDIGO C      4015953      1667500      10      2003      1
11      MASTER I      106250      115000      10      2003      1
12      PACKING      50533      5750      10      2003      1
13      PACKING       275939      299000      10      2003      1
14      SANFORIZ      5242      57500      10      2003      1
15      WASHING      55076      17250      10      2003      1
16      WASHING       147188      460000      10      2003      1
17      WORK SHO      4568      11500      10      2003      1
18      ACCOUNT       17570      2000      11      2003      2
19      AIR JET       43646      143750      11      2003      2
20      AIR JET       189931      63250      11      2003      2
21      CHILLER       271275      92000      11      2003      2
22      ELETRICA      2402      5750      11      2003      2
23      FOLDING      29653      5750      11      2003      2
24      GENERAL       4498      23000      11      2003      2
25      INDIGO C      4451377      1667500      11      2003      2
26      MASTER I      54458      115000      11      2003      2
27      PACKING      26598      5750      11      2003      2
28      PACKING       194833      299000      11      2003      2
29      SANFORIZ      12160      57500      11      2003      2
30      WASHING      34499      17250      11      2003      2
31      WASHING       189627      460000      11      2003      2
32      WORK SHO      1513      11500      11      2003      2
33      ACCOUNT       15182      2000      12      2003      3
34      AIR JET       76705      143750      12      2003      3
35      AIR JET       6133      5000      12      2003      3
36      AIR JET       198427      63250      12      2003      3
37      BOILER      365486      23000      12      2003      3
38      CHILLER       120099      92000      12      2003      3
39      ELETRICA      2232      5750      12      2003      3
40      FOLDING      19962      5750      12      2003      3
41      GENERAL       14241      23000      12      2003      3
42      INDIGO C      4477673      1667500      12      2003      3
43      MASTER I      154352      115000      12      2003      3
44      PACKING      28170      5750      12      2003      3
45      PACKING       201776      299000      12      2003      3
46      WASHING      144747      17250      12      2003      3
47      WASHING       186800      460000      12      2003      3
48      WORK SHO      1677      11500      12      2003      3
49      ACCOUNT       14720      0      1      2004      1
50      AIR JET       87593      0      1      2004      1
51      AIR JET       8711      0      1      2004      1
52      AIR JET       242401      0      1      2004      1
53      BOILER      22829      0      1      2004      1
54      CHILLER       44866      0      1      2004      1
55      ELETRICA      635      0      1      2004      1
56      FOLDING      67726      0      1      2004      1
57      GENERAL       78033      0      1      2004      1
58      INDIGO C      4465681      0      1      2004      1
59      MASTER I      274688      0      1      2004      1
60      PACKING      35658      0      1      2004      1
61      PACKING       229903      0      1      2004      1
62      SANFORIZ      9360      0      1      2004      1
63      WASHING      136247      0      1      2004      1
64      WASHING       109311      0      1      2004      1
65      WORK SHO      8955      0      1      2004      1
66      AIR JET       71439      0      2      2004      2
67      CHILLER       4840      0      2      2004      2
68      MASTER I      10112      0      2      2004      2
69      SANFORIZ      4016      0      2      2004      2

Now in powerbuilder i want to create a datawindow with this sql
select * from (the above data)
where yr = :retrieval_argument_year
and mth=:retrieval_argument_month

That is i want my procedure to return a whole table
and how to call/access this table from within powerbuilder.



Avatar of namasi_navaretnam
namasi_navaretnam
Flag of United States of America image

Mateen,

This is what you can do:
1) Add thia line at the end of your stored procedure
select * from  @budget_n_exp


2) Creare a datawindow with stored procedure data source. When you run "create datawindow wizard" you will see an option to select store procedure as data source for the datawindow. Name the datawindow as "d_get_budget_info"

Add two input parameter to your stored proc as follows:
Create Procedure sp_GetMyResults
(
 @aYear int,
 @aMonth int
)
as
Begin

-- Your Sp Code here


--End of proc
select * from  @budget_n_exp
where yr = @aYear and @mth = @aMonth

end


3) Now you can use datastore to retrieve data

datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result  = create datastore

lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject(SQLCA)
ll_rc = lds_result.Retrieve(ll_year, ll_month)

MessageBox("Debug", string(ll_rc))      

If IsValid(lds_host) Then Destroy lds_host

To make it more efficient you can even modify the stored proc to insert data only for a given month and year.

Regards,

Namasi

One more point,

insert @budget_n_exp <-- syntax like that was introduced in sql 2000 only. If PB does not like to for some reason, try this at the end of procedure.

select *
into #temp
from  @budget_n_exp
where yr = @aYear and mth = @aMonth  <-- also correct the syntax in my previous post

select * from #temp

Or even from the beggining insert into #temp instead of @budget_n_exp

In that case you will create #temp as follows:
Create table #temp   (ser_no int identity(1,1),
     dept_name varchar(70),
     expense numeric(10,0),
     budget numeric(10,0),
     mth smallint,
     yr  smallint,
     per smallint)

Then :

insert #temp
     select      x.dept_name,
     expense=round(sum(x.iss_qty * x.rate),0),
     budget = isnull(sum(distinct budget_det.budget_qty),0),
     mth=@month,
     yr=@year+1,
     per=@month
     ....other code

HTH
Avatar of gajender_99
gajender_99



ls_statement = "select * from @budget_n_exp"
     
dw_recpt.Modify (" DataWindow.Table.Procedure= '"+ls_statement+"'")

dw_recpt.settransobject(as_transaction)
as_transaction.AutoCommit = true
ll_rows = dw_recpt.retrieve()
as_transaction.AutoCommit = false


 
Correctionn:

This
select * from  @budget_n_exp
where yr = @aYear and @mth = @aMonth

should be

select * from  @budget_n_exp
where yr = @aYear and mth = @aMonth

or
select * from  #temp
where yr = @aYear and mth = @aMonth

HTH
Avatar of Mateen

ASKER

When I posted this question I have in mind that
a procedure will be declared in such a way that it will return a table
and then i could write

select *
from
dbo.MyDefinedProcedure(year_value,month_value)

that is i never imagined that i will have to go so long.
will have to use datastore.

Isn't it possible that a datawindow be created with sql select
instead of stored procedure as the store_procedure is returning table.
I am unable to explain as english is not my native lanugage.
have you tried

ls_statement = "select * from @budget_n_exp"
     
dw_recpt.Modify (" DataWindow.Table.Procedure= '"+ls_statement+"'")

dw_recpt.settransobject(as_transaction)
ll_rows = dw_recpt.retrieve()


Avatar of Mateen

ASKER

Please consider this

--This example returns an inline table-valued function.

USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)

now i can write
select *
from
SalesByStore()  -- i don't know the exact syntax.

Should I change my procedure to function. if so then how.
No. You do not need to convert SP to Function. Absolutely not. But all you need to do is return a resultset using "select" at the end. It seems that all you are doing is insert into table @budget_n_exp. All you need to do is add a select statement at the end of stored proc to retuen a result set using "select". Then you can use that stored procedure as the source for you datawindow. I have hundreds of datawindow like that.

have you tried what I suggested at the end of proc.

select *
into #temp
from  @budget_n_exp
where yr = @aYear and mth = @aMonth  <-- also correct the syntax in my previous post

select * from #temp

If you post your proc I will modify it for you.

HTH

Namasi.

Create a datawindow out of this proc and everything will be just fine.

Create procedure sp_GetMyResults
(
 @aYear int,
 @aMonth int
)
AS
BEGIN

declare @year smallint
declare @month smallint
declare @company_code varchar(2)

set @year=2003
set @month=10
set @company_code='02'


CREATE TABLE #budget_n_exp table
     (ser_no int identity(1,1),
     dept_name varchar(70),
     expense numeric(10,0),
     budget numeric(10,0),
     mth smallint,
     yr  smallint,
     per smallint)

while @month between 10 and 12
begin
     insert #budget_n_exp table
     select      x.dept_name,
     expense=round(sum(x.iss_qty * x.rate),0),
     budget = isnull(sum(distinct budget_det.budget_qty),0),
     mth=@month,
     yr=@year,
     per=@month-9
from
(
SELECT      dept_code=issm.iss_to,
     dept_name=dept.dept_name,
     item_code=issd.item_code,
           iss_qty = sum(issd.issue_qty),  
     rate    =
     (SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
         FROM     str_receive_det recd,  
                   str_receive_mst recm
     WHERE ( recm.trans_no     = recd.trans_no )
       and ( recm.company_code = recd.company_code )  
       and ( recd.item_code    = issd.item_code)
       and ( recm.company_code = @company_code)
       and ( year(recm.trans_date)     <= @year)
       and ( month(recm.trans_date)     <=
       case when year(recm.trans_date) = @year then @month else 12 end))

FROM      str_issue_det issd,  
            str_issue_mst issm,
      str_item item,
      department dept
WHERE ( item.item_code = issd.item_code )
  and ( issm.trans_no =  issd.trans_no  )
  and ( issm.company_code = issd.company_code )
  and ( dept.dept_code = issm.iss_to)
  and ( year(issm.issue_date) = @year )
  and ( month(issm.issue_date) = @month )
  and ( issm.company_code = @company_code)
group by issm.iss_to,
      dept.dept_name,
      issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
  and budget_det.year_no  = @year
  and budget_det.month_no = @month
group by x.dept_name

set @month=@month+1
end


set @month=1
while @month between 1 and 9
begin
     insert #budget_n_exp table
     select      x.dept_name,
     expense=round(sum(x.iss_qty * x.rate),0),
     budget = isnull(sum(distinct budget_det.budget_qty),0),
     mth=@month,
     yr=@year+1,
     per=@month
from
(
SELECT      dept_code=issm.iss_to,
     dept_name=dept.dept_name,
     item_code=issd.item_code,
           iss_qty = sum(issd.issue_qty),  
     rate    =
     (SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
         FROM     str_receive_det recd,  
                   str_receive_mst recm
     WHERE ( recm.trans_no     = recd.trans_no )
       and ( recm.company_code = recd.company_code )  
       and ( recd.item_code    = issd.item_code)
       and ( recm.company_code = @company_code)
       and ( year(recm.trans_date)     <= @year+1)
       and ( month(recm.trans_date)     <=
       case when year(recm.trans_date) = @year+1 then @month else 12 end))

FROM      str_issue_det issd,  
            str_issue_mst issm,
      str_item item,
      department dept
WHERE ( item.item_code = issd.item_code )
  and ( issm.trans_no =  issd.trans_no  )
  and ( issm.company_code = issd.company_code )
  and ( dept.dept_code = issm.iss_to)
  and ( year(issm.issue_date) = @year+1 )
  and ( month(issm.issue_date) = @month )
  and ( issm.company_code = @company_code)
group by issm.iss_to,
      dept.dept_name,
      issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
  and budget_det.year_no  = @year+1
  and budget_det.month_no = @month
group by x.dept_name

set @month=@month+1
end

select *
from #budget_n_exp table
where mth = @aMonth and
         yr = @aYear


DROP TABLE #budget_n_exp table

END


2) To retrieve try:

long ll_year = 2004, ll_month = 2, ll_rc
dw_1.Retrieve(ll_year, ll_month)  // dw_1 is associated with a dw created using stored proc sp_GetMyResults


OR

datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result  = create datastore

lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject(SQLCA)
ll_rc = lds_result.Retrieve(ll_year, ll_month)

MessageBox("Debug", string(ll_rc))    

If IsValid(lds_host) Then Destroy lds_host


Regards.
Avatar of Mateen

ASKER

Hi namasi:

I created the procedure spgetmyresults in sql server 2000
as u stated.
then i selected datawindow->tabular->stored_procedure->system proedure->and selected sp_getmyresults;1
The database does not currently support retrieval of procedure text.
next->next->finish
cannot create datawindow 'arguments for select are invalid or incomplete.

I am not understanding your this part.
2) To retrieve try:

long ll_year = 2004, ll_month = 2, ll_rc
dw_1.Retrieve(ll_year, ll_month)  // dw_1 is associated with a dw created using stored proc sp_GetMyResults

OR
datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result  = create datastore

lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject(SQLCA)
ll_rc = lds_result.Retrieve(ll_year, ll_month)

MessageBox("Debug", string(ll_rc))    

If IsValid(lds_host) Then Destroy lds_host

why i have to say
long ll_year = 2004, ll_month = 2, ll_rc

Consider i am in preview mode and when i click rows->retrieve
the retrieval argument popup window should come where i
will give the value of year and month just as i do with other reports.

Please see my next comment also
Avatar of Mateen

ASKER

Hi namasi:

I created a sample table colour with column 'colour_code'  and inserted values '01','02','03','04','05'. then in query analyzer i wrote

create FUNCTION udf_delit ()
RETURNS TABLE
AS
RETURN (SELECT * from colour )

then in powerbuilder i did
datawindow->tabular->sql select->next->cancel->design->convert to syntax->select * from udf_delit() where colour_code=:ra_code->design->retrieval arguments->:ra_code (string)->next->next->finish
then rows->retrieve
the argument taking window popped up and i supplied '04' and ok.
and the required row is retrieved.

I want to do this way.
ASKER CERTIFIED SOLUTION
Avatar of namasi_navaretnam
namasi_navaretnam
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Mateen,

Also, make sure "manual result set" option is NOT CHECKED when associating your stored proc with the procedure.



Mateen

This will work. But there is no need to convert SPs to functions like that. Believe me!!! :) Functions have they are own issue when you write a complicated one.

create FUNCTION udf_delit ()
RETURNS TABLE
AS
RETURN (SELECT * from colour )

go

I am sure this will work as a stored procedure, too.

CREATE Procedure sp_delit
AS
BEGIN
 SELECT * from colour
END


I will run into many issues when using stored functions than stored procedures with complicated logics...

Regards.

Namasi.