Stored procedure execution from within a data retrieval select statements.

  • I have a stored procedure that calculates a quarter number. It accepts an input date and returns a quarter number based upon the start and end dates in a period master file.

    Can this procedure be called/executed within an SQL program that reads an inventory history table passing it the transaction date from the record read? I'm fairly new to SQL so bear with me.

    Sample code:

    declare @Quarter varchar(1)

    use BPCSARC

    select a.RLPROD,

    a.LIC#,

    b.LICNBR,

    b.LICNME,

    b.RYLPCT,

    c.IPROD,

    c.IDESC,

    d.TTDTE,

    d.TQTY,

    d.TPRIC,

    d.TVAL,

    d.TWHS,

    d.TLOCT

    --exec getQuarter @DateIn=TTDTE, @QuarterNumber=@Quarter output

    from (((LRIM a

    left join LRLM b on

    a.LIC# = b.LICNBR)

    left join IIM c on

    a.RLPROD = c.ILPROD)

    left join ITH d on

    c.IPROD = d.TPROD)

    where LIC# = 62 or LIC# = 91 and

    TTDTE >= 20090101 and

    TTDTE <= 20120331

    order by RLPROD

  • Welcome to the forums. 🙂

    First, no, you can't call a procedure in-line of another SQL statement to run per row. Those are functions in SQL Server, and they're usually performance killers.

    Secondly, if you show us the code you are using in your proc, we should be able to show you how to make it optimal for large queries and get you into some good practices. If you take a look at the link in my first signature, you'll see what we'd really prefer to help you get that working well.

    In general, however, when you're working with quarters, weekends, holidays, etc, what we usually work with is a calendar table. Setting it up originally can be a bit of a pain but from there you can automate it to be kept up to date, and it solves a host of run-time computational problems, like determining a date's quarter for reporting.


    - 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

  • Hers is my procedure:

    USE BPCSARC

    GO

    --// Create Stored Procedure with OUTPUT parameter

    CREATE PROCEDURE getQuarter

    @DateIn INT,

    @QuarterNumber VARCHAR(1) OUTPUT

    AS

    BEGIN

    SELECT @QuarterNumber = (case when PERIOD >= 01 and PERIOD <= 03 then '1'

    when PERIOD >= 04 and PERIOD <= 06 then '2'

    when PERIOD >= 07 and PERIOD <= 09 then '3'

    when PERIOD >= 10 and PERIOD <= 12 then '4'

    end)

    FROM GPM

    WHERE PEND <= @DateIn

    end

    GO

    Being an Iseries RPG programmer, I'm trying to relate coding similarities between RPGIV ILE to SQL.

    All of our libraries and files have been exported to the SQL Server and eventually the Iseries is disappearing so I'm taking a crash course in SQL programming.

    Any help will be greatly appreciated.

  • It looks like you may already have a calendar table in GPM. Can you give us the DDL and a few sample rows from that?


    - 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

  • GPM "Period Master" PPMID "Record Id" varchar(2)

    PCMPNY "Company No" Numeric(2,0)

    PYEAR "Fiscal Year" numeric(4,0)

    PERIOD "Fiscal Period" numeric(2,0)

    PSTART "Period Start Date" numeric(8,0)

    PEND "Period End Date" numeric(8,0)

    PM 20 2012 1 20120102 20120129

    PM 20 2012 2 20120130 20120226

    PM 20 2012 3 20120227 20120401

    PM 20 2012 4 20120402 20120429

    PM 20 2012 5 20120430 20120527

    PM 20 2012 6 20120528 20120701

    PM 20 2012 7 20120702 20120729

    PM 20 2012 8 20120730 20120826

    PM 20 2012 9 20120827 20120930

    PM 20 2012 10 20121001 20121028

    PM 20 2012 11 20121029 20121125

    PM 20 2012 12 20121126 20121230

    The end result of this task is to track inventory movements by quarter. I realize my approach to this task is not very efficient but it's the only way I could think of at this time. Old habits sometimes die hard.

    If you could point me in the right direction, that would be most helpful.

    Thanks again.

  • bkatoch (10/30/2012)


    The end result of this task is to track inventory movements by quarter. I realize my approach to this task is not very efficient but it's the only way I could think of at this time. Old habits sometimes die hard.

    If you could point me in the right direction, that would be most helpful.

    Thanks again.

    So we're talking apples to apples, and since you seem like a nice enough guy to keep around, when we request DDL and data we're looking for directly runnable data, not just a formatted set, like so:

    CREATE TABLE #GPM

    (PPMID VARCHAR(2) NOT NULL,--Record ID

    PCMPNY NUMERIC( 2, 0) NOT NULL,-- Company Number

    PYear Numeric( 4, 0) NOT NULL,-- Fiscal Year

    Period NUMERIC( 2,0) NOT NULL,-- Fiscal Period

    PStart NUMERIC(8,0) NOT NULL,-- Period Start Date

    PEnd NUMERIC(8,0) NOT NULL-- Period End Date

    )

    INSERT INTO #GPM

    SELECT'PM', 20, 2012, 1, 20120102, 20120129 UNION ALL

    SELECT'PM', 20, 2012, 2, 20120130, 20120326

    Now, to move past that. SQL Server has the DATETIME (and DATE) datatypes, and you'll find they're usually more effective for this than Numerics are. However, that said, you don't need an extra function for your dates, all you need is another column in your GPM table indicating the quarter. It'll repeat for the necessary rows the same way Fiscal Year would.

    From there, you're looking at joining the dates from the table into the GPM table. Now, usually a range isn't the best of choices for direct joins like this but I'm not sure how much data you have coming off the primary table, and this table seems small enough that doing a between join shouldn't hurt you much. I'd avoid the function entirely and adjust the ranged calendar table you have here. I'd personally also switch PStart and PEnd into DATETIMEs.


    - 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply