[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored procedure

Posted on 2006-06-02
4
Medium Priority
?
486 Views
Last Modified: 2008-02-01
Hello, I'm trying to right a stored procedure. I just need someone to verify that this is correct:

create procedure sp_cashflow
@var1 int , @var2 int
AS
create table #cashflow(Payment_Number varchar(50), Tranaction_Number varchar(50),
Descriptions varchar(100), Invoice_Applied_To varchar(50),
RATransactionDate datetime NULL , Invoice_Amount_Applied varchar(50),
Receipt_Document_Date varchar(50), Receipt_Apply_Date varchar(50),
Customer_code varchar(50), Customer varchar(50), Payment_Code varchar(50),
Receipt_Type varchar(50), GL_TRX_id varchar(50), Cash_Account_Code varchar(50))

/* select * from #cashflow */

/*The insert will putt all data gathered from a table and place it in the #cashflow table */
insert into #cashflow
select distinct
'Payment_Number'              = a.doc_ctrl_num,
'Transaction_Number'          = a.trx_ctrl_num,
'Descriptions'                = a.doc_desc,
'Invoice_Applied_To'          = b.apply_to_num,
'RATransactionDate'           = NULL,
'Invoice_Amount_Applied'      = CONVERT(varchar, CONVERT(decimal(20,2), b.amt_applied)),
'Receipt_Document_Date'       = SUBSTRING (convert (varchar(11), CONVERT(datetime, DATEADD(dd,(a.date_doc - 639906),

'01/01/1753'))),1,11),
'Receipt_Apply_Date'          = SUBSTRING (convert (varchar(11), CONVERT(datetime, DATEADD(dd,(a.date_applied -

639906), '01/01/1753'))),1,11),
'Customer_Code'               = a.customer_code,
'Customer'                    = c.address_name,
'Payment_Code'                = a.payment_code,
'Receipt_Type'                = CASE a.payment_type
                                when 1 then 'Cash Receipt'
                                when 2 then 'On-Account payment'
                                when 3 then 'Credit Memo Applied'
                                when 4 then 'On-Account Credit Memo Applied'
                                END,
'Gl_Trx_Id'                   = a.gl_trx_id,
'Cash_Account_Code'           = a.cash_acct_code  

FROM artrx a
inner join artrxpdt b on a.trx_ctrl_num  = b.trx_ctrl_num
inner join armaster c on a.customer_code = c.customer_code
WHERE a.trx_type = 2111 and
      b.apply_to_num in
                    (SELECT doc_ctrl_num
                     FROM artrxcdt
                    WHERE substring(gl_rev_acct,11,4) = '8100')and
                                          a.date_applied between @var1 and @var2            
ORDER BY a.doc_ctrl_num


This stored procedure needs to pass in two values. Is this syntax correct?
0
Comment
Question by:DancingFighterG
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16816950
a.date_applied between @var1 and @var2            

looks like you want to compare 2 dates?

>@var1 int , @var2 int
should then rather be
@var1 datetime, @var2 datetime
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 200 total points
ID: 16817332
you could avoid the create table statment...
and just do a Select ....
                       into #casdhflow
                       from ...

i'd always finish/exit a stored procedure via a Return and a ReturnCode
then you can have common shop standard on checking for errors...
 
(and you need to add some error checking into the procedure...
  to validate confirm your business logic...)

also Added Set Nocount on  

create procedure sp_cashflow
  @var1 Datetime
, @var2 datetime
AS
Set Nocount ON
Declare @RC INT
Set @RC = 0

create table #cashflow(Payment_Number varchar(50), Tranaction_Number varchar(50),
Descriptions varchar(100), Invoice_Applied_To varchar(50),
RATransactionDate datetime NULL , Invoice_Amount_Applied varchar(50),
Receipt_Document_Date varchar(50), Receipt_Apply_Date varchar(50),
Customer_code varchar(50), Customer varchar(50), Payment_Code varchar(50),
Receipt_Type varchar(50), GL_TRX_id varchar(50), Cash_Account_Code varchar(50))

/* select * from #cashflow */

/*The insert will putt all data gathered from a table and place it in the #cashflow table */
insert into #cashflow
select distinct
'Payment_Number'              = a.doc_ctrl_num,
'Transaction_Number'          = a.trx_ctrl_num,
'Descriptions'                = a.doc_desc,
'Invoice_Applied_To'          = b.apply_to_num,
'RATransactionDate'           = NULLIF(getdate(),Getdate()),
'Invoice_Amount_Applied'      = CONVERT(varchar(50), CONVERT(decimal(20,2), b.amt_applied)),
'Receipt_Document_Date'       = SUBSTRING (convert (varchar(11), CONVERT(datetime, DATEADD(dd,(a.date_doc - 639906), '01/01/1753'))),1,11),
'Receipt_Apply_Date'          = SUBSTRING (convert (varchar(11), CONVERT(datetime, DATEADD(dd,(a.date_applied - 639906), '01/01/1753'))),1,11),
'Customer_Code'               = a.customer_code,
'Customer'                    = c.address_name,
'Payment_Code'                = a.payment_code,
'Receipt_Type'                = CASE a.payment_type
                                when 1 then 'Cash Receipt'
                                when 2 then 'On-Account payment'
                                when 3 then 'Credit Memo Applied'
                                when 4 then 'On-Account Credit Memo Applied'
                                END,
'Gl_Trx_Id'                   = a.gl_trx_id,
'Cash_Account_Code'           = a.cash_acct_code  

FROM artrx a
inner join artrxpdt b
   on a.trx_ctrl_num  = b.trx_ctrl_num
inner join armaster c
   on a.customer_code = c.customer_code

WHERE a.trx_type = 2111
  and a.date_applied between @var1 and @var2            
  and EXISTS (SELECT doc_ctrl_num
                FROM artrxcdt as X
               WHERE substring(gl_rev_acct,11,4) = '8100'
                 AND b.apply_to_num = X.doc_ctrl_num)
ORDER BY a.doc_ctrl_num

... Rest of your procedure goes here


RETURN @RC

GO
0
 

Author Comment

by:DancingFighterG
ID: 16832963
ok.
0
 

Author Comment

by:DancingFighterG
ID: 16833271
I can't use the datetime for the variables because I am using a platinum date system. From there I do a conversion. It has to done this way because the info is setup like that. That's why I made the var variables ints.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question