September 21, 2001 at 7:30 am
What is wrong with this sp
-------starts here --------
CREATE procedure [GP_MC_Analysis_Vw]
(
@UserName char(50), @Year char(4), @StartDate char(20), @EndDate char(20)
)
as
set nocount on
declare @cmd char(8000)
set @cmd = "setuser "+@UserName+" CREATE VIEW v_MC_Analysis AS "
set @cmd = "SELECTa.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code_Currency, SUM(a.Fina_Amt) AS Invoiced_Amount, "
set @cmd = "0 as Collected_Amount, 0 as Credit_Note_Amount, a.Fina_Code, "
set @cmd = "case when a.Fina_Code_Desc like 'MC Member Fee%' then 'Member Fee' "
set @cmd = "when a.Fina_Code_Desc like 'MC Property Fee%' then 'Prop. Fee'"
set @cmd = "else a.Fina_Code_Unit_Type"
set @cmd = "end as Fina_Code_Unit_Type, a.Fina_Code_Charge_Value --a.Fina_Mem_Numb, "
set @cmd = "FROM v_Invoice_Details_Unpaid a"
set @cmd = "WHERE a.Fina_Acc_Date >="+ @StartDate+" AND a.Fina_Acc_Date <= "+@EndDate+" AND a.Fina_Code_Year = "+@Year
set @cmd = " AND (a.Fina_Code_Desc like 'Man. Charge%' or a.Fina_Code_Desc like 'MC Member Fee%' "
set @cmd = "or a.Fina_Code_Desc like 'MC Property Fee%') "
set @cmd = " GROUP BY a.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code_Currency, a.Fina_Code, a.Fina_Code_Unit_Type,"
set @cmd = "a.Fina_Code_Charge_Value, a.Fina_Code_Desc "
set @cmd = " UNION "
set @cmd = " SELECTa.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code_Currency, 0 as Invoiced_Amount,"
set @cmd = "SUM(a.Fina_Paid_Amt) AS Collected_Amount, 0 as Credit_Note_Amount, a.Fina_Code, "
set @cmd = "case when a.Fina_Code_Desc like 'MC Member Fee%' then 'Member Fee'"
set @cmd = "when a.Fina_Code_Desc like 'MC Property Fee%' then 'Prop. Fee'"
set @cmd = "else a.Fina_Code_Unit_Type"
set @cmd = "end as Fina_Code_Unit_Type, a.Fina_Code_Charge_Value "
set @cmd = "FROM v_Invoice_Details_Paid a"
set @cmd = "Where a.Fina_Acc_Date >="+ @StartDate+" AND a.Fina_Acc_Date <= "+@EndDate+" AND a.Fina_Code_Year = "+@Year
set @cmd = "AND a.Pment_Status <> 'DECLIN' AND a.Pment_Authoriz NOT LIKE '%declin%'"
set @cmd = "AND (a.Fina_Code_Desc like 'Man. Charge%' or a.Fina_Code_Desc like 'MC Member Fee%' "
set @cmd = "or a.Fina_Code_Desc like 'MC Property Fee%')"
set @cmd = "GROUP BY a.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code_Currency, a.Fina_Code, a.Fina_Code_Unit_Type,"
set @cmd = "a.Fina_Code_Charge_Value, a.Fina_Code_Desc"
set @cmd = "UNION"
set @cmd = "SELECT a.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code_Currency, 0 as Invoiced_Amount, 0 AS Collected_Amount, "
set @cmd = "SUM(a.Fina_Amt) AS Credit_Note_Amount, a.Fina_Code, a.Fina_Code_Unit_Type,"
set @cmd = "(select distinct b.Fina_Code_Charge_Value from v_Invoice_Details_Unpaid b"
set @cmd = "where b.Fina_Code_Charge_Value > 0"
set @cmd = "and b.Fina_Code_Year = a.Fina_Code_Year"
set @cmd = "and b.Fina_Club_Code = a.Fina_Club_Code"
set @cmd = "and b.Fina_Code_Currency = a.Fina_Code_Currency"
set @cmd = "and b.Fina_Code_Unit_Type = a.Fina_Code_Unit_Type) as Fina_Code_Charge_Value"
set @cmd = "FROM v_Invoice_Details_Unpaid a "
set @cmd = "Where a.Fina_Amt < 0 AND a.Fina_Acc_Type = 'AC001' "
set @cmd = "AND a.Fina_Inv_Numb IN (select fina_paid_txn_payment from cume_curr_fina_paid "
set @cmd = "where fina_paid_txn_invoice in (select Fina_Inv_Numb from v_Invoice_Details_Unpaid "
set @cmd = "where Fina_Acc_Date >= "+@StartDate+" AND Fina_Acc_Date <= "+@EndDate
set @cmd = "AND Fina_Code_Year = "+@Year +")) "
set @cmd = "and a.Fina_Acc_Date >= "+@StartDate +"AND a.Fina_Acc_Date <= "+@EndDate +"AND a.Fina_Code_Year = "+@Year
set @cmd = "and not isnull (a.Fina_Code_Unit_Type, '') = '' "
set @cmd = "and (a.Fina_Code_Desc like 'MC CN%' or a.Fina_Code_Desc like 'MC Credit Note%' "
set @cmd = "or a.Fina_Code_Desc like 'MC Waived%')"
set @cmd = "AND (a.Fina_Code_Desc not like '%Penalty%' and a.Fina_Code_Desc not like '%RCI%' "
set @cmd = "and a.Fina_Code_Desc not like '%Trustee%' and a.Fina_Code_Desc not like '%Reinstatement%')"
set @cmd = "and a.fina_club_code not in ('roypc')"
set @cmd = "GROUP BY a.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code, a.Fina_Code_Unit_Type, a.Fina_Code_Currency, "
set @cmd = "Fina_Code_Charge_Value, a.Fina_Code_Desc union SELECT a.Fina_Code_Year"
set @cmd = " , a.Fina_Club_Code, a.Fina_Code_Currency, 0 as Invoiced_Amount, 0 AS Collected_Amount, "
set @cmd = " SUM(a.Fina_Amt) AS Credit_Note_Amount, a.Fina_Code, "
set @cmd = "case when a.Fina_Code_Desc like '%Member%' or a.Fina_Code_Desc like '%M-Fee%' then 'Member Fee'"
set @cmd = "when a.Fina_Code_Desc like '%Property%' or a.Fina_Code_Desc like '%P-Fee%' then 'Prop. Fee'"
set @cmd = "else a.Fina_Code_Unit_Type"
set @cmd = "end as Fina_Code_Unit_Type,"
set @cmd = "(select distinct b.Fina_Code_Charge_Value from v_Invoice_Details_Unpaid b"
set @cmd = "where b.Fina_Code_Charge_Value > 0"
set @cmd = "and b.Fina_Code_Year = a.Fina_Code_Year"
set @cmd = "and b.Fina_Club_Code = a.Fina_Club_Code"
set @cmd = "and b.Fina_Code_Currency = a.Fina_Code_Currency"
set @cmd = "and b.Fina_Code_Unit_Type = a.Fina_Code_Unit_Type) as Fina_Code_Charge_Value"
set @cmd = "FROM v_Invoice_Details_Unpaid a "
set @cmd = "Where a.Fina_Amt < 0 AND a.Fina_Acc_Type = 'AC001' "
set @cmd = "AND a.Fina_Inv_Numb IN (select fina_paid_txn_payment from cume_curr_fina_paid "
set @cmd = "where fina_paid_txn_invoice in (select Fina_Inv_Numb from v_Invoice_Details_Unpaid "
set @cmd = "where Fina_Acc_Date >="+@StartDate+" AND Fina_Acc_Date <= "+@EndDate
set @cmd = "AND Fina_Code_Year = "+@Year +")) "
set @cmd = "and a.Fina_Acc_Date >= "+@StartDate+" AND a.Fina_Acc_Date <= "+@EndDate +"AND a.Fina_Code_Year = "+@Year
set @cmd = "and (a.Fina_Code_Desc like 'MC CN%' or a.Fina_Code_Desc like 'MC Credit Note%' "
set @cmd = "or a.Fina_Code_Desc like 'MC Waived%')"
set @cmd = "AND (a.Fina_Code_Desc not like '%Penalty%' and a.Fina_Code_Desc not like '%RCI%' "
set @cmd = "and a.Fina_Code_Desc not like '%Trustee%' and a.Fina_Code_Desc not like '%Reinstatement%')"
set @cmd = "and a.fina_club_code in ('roypc')"
set @cmd = "GROUP BY a.Fina_Code_Year, a.Fina_Club_Code, a.Fina_Code, a.Fina_Code_Unit_Type, a.Fina_Code_Currency, "
set @cmd = "Fina_Code_Charge_Value, a.Fina_Code_Desc setuser "
exec @cmd
set nocount off
---------------------------
when I execute it it says sp not found..
September 21, 2001 at 7:53 am
That Should be SET @cmd = @cmd + <string>
September 22, 2001 at 11:41 am
In my opinion (!) this is not a great solution. If you're going to run all that code each time, why not juse execute the string and return the results. To me, views should be very static. Use a where clause against the view, dont build different versions of the view.
Andy
September 24, 2001 at 11:33 am
I agree with you Andy. Also the problem with this procedure is that ... the second time you run the same procedure.. it will not be able to create the view since it is already created so its no point creating different views everytime depending upon the parameters. Instead use normal selects. This, according to me, would unnecessarily put more load on the server.
quote:
In my opinion (!) this is not a great solution. If you're going to run all that code each time, why not juse execute the string and return the results. To me, views should be very static. Use a where clause against the view, dont build different versions of the view.Andy
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply