August 21, 2012 at 9:49 am
I need to execute this stored procedure. Problem is that I need to pass in more than one value. At present I am passing in unitNr 37, I need to pass in a list of unit numbers. How can I do this. Thanks in advance.
USE [Rentway]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[up_Vehicle_Depreciation_Calculation]
@UnitNr = '37'
GO
August 21, 2012 at 9:55 am
Table-valued parameters http://msdn.microsoft.com/en-us/library/bb510489.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2012 at 7:16 am
CELKO (8/22/2012)
Re-write the proc to use a table valued parameter and lock yourself into proprietary MS. Or you can Google us a set of two articles I did on long parameters lists.
Stored procedures are pretty much proprietary, no matter what system.
correct me if I'm wrong, but you cannot even write a simple proc that does SELECT ColumnName FROM Table that is cross-DBMS compliant in ANY language. starting with data types and variable declaration, Nothing is cross compatible, as far as i know.
Lowell
August 22, 2012 at 7:29 am
Re-write the proc to use a table valued parameter and lock yourself into proprietary MS. Or you can Google us a set of two articles I did on long parameters lists...
I have some idea too:
Let's stop use English - the proprietary of Her Majesty and Co. You are locking yourself into the language which is spoken by just few people.
Let use standard and fully portable one: Esperanto - the most widely spoken international language. It's even approved for use by scientists (eg. recommended by the French Academy of Sciences ). And the best thing is: there are even some people who do speak it. :w00t:
SELECTY * DE TABLO KIE IDIOTO_NIVELO = 'Plej Alta Ebla'
:hehe:
So, it-s a clear win, as when r code will need to be ported into LibraPro.Net+-DB (this will be common database system in 23th century) it will take no time at all.
😀
August 22, 2012 at 8:19 am
--The problem with your code Luiz is that it is calculating the two periods together, but the date_block (20120406) is between the second period, so I need a way of only calculating up until the date_block (20120406) . The figure of depr I need from the below sql is 3925.58, this figure is made up of 24 months at 151..45 and 2 months at 145.39 . It seems the query is not taking into account the Date_block date.
Declare @date as datetime
set @date ='20120731'
DECLARE @vehicle TABLE (
unitnr int,
value decimal( 18, 8), --changed the data type
date_block datetime, --changed the data type
type_fleet char(2), --added column
platenrchar(9)) --added column
DECLARE @Vehicle_Depreciations TABLE (
Start_Date datetime,
End_Date datetime,
Vehicle_Depreciation_Value decimal( 18, 8), --changed the data type
unitnr int)
--Changed ALL dates format
INSERT @Vehicle
SELECT 29, 8057.85, '20120406', 'of', '08D12345'
INSERT @Vehicle_Depreciations
SELECT '20100301', '20120229' ,151.45,29 UNION ALL
SELECT '20120301', '20140801' ,145.39, 29
select'For Sale' as Type,
v.unitnr,
v.value,
v.platenr,
v.Type_Fleet,
--vd.Start_Date, --If you uncomment this fields, you'll get a detail for each depreciation period
--vd.End_Date,
v.Date_Block ,
--vd.Vehicle_Depreciation_Value,
sum(CASE WHEN vd.END_DATE >@DATE--checks if end_date is greater than the date passed
THEN DATEDIFF(MONTH,vd.START_DATE,DATEADD(day,1,@DATE))--determines the months of depreciation from start_date till date passed
WHEN vd.END_DATE > v.Date_Block-- checks if end_date is past Date_Blocked for sale
THEN DATEDIFF(MONTH,vd.START_DATE,v.Date_Block) --if so, then determines the months of depreciation from start_Date till dat_blocked
ELSE DATEDIFF(MONTH,vd.START_DATE,vd.END_DATE+1) END * vd.Vehicle_Depreciation_Value )AS depr --determines the months of depreciation between start_date and end_date
from @vehicle v inner join @Vehicle_Depreciations vd on v.unitnr = vd.UnitNr
WHERE v.Date_Block is not null
group by v.unitnr, v.value, v.Date_Block,v.platenr, v.Type_Fleet
August 22, 2012 at 12:12 pm
As already mentioned table valued parameters are a great tool here, but if you want a simpler input (but more work at the proc level) XML or comma delimited strings are usable too. I'd avoid XML unless you have value pairs (ie: are passing rows, not values) but the comma delimited string along with the delimitedstring8k (search this site for it) will give you a table valued function that'll split it down for you to use as a joined table to restrict the results.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 23, 2012 at 7:51 am
sorry posted on wrong thread. with previous post
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply