Post reply

passing more than one value to a SP

  • August 21, 2012 at 9:49 am

    #265639

    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

    #1528212

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 22, 2012 at 7:16 am

    #1528612

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 22, 2012 at 7:29 am

    #1528626

    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.

    😀

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • August 22, 2012 at 8:19 am

    #1528664

    --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

    platenr char(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

    #1528834

    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.

    - Craig Farrell

    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

    #1529253

    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