Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure execution from within a data retrieval select statements. Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 11:53 AM
Points: 3, Visits: 13
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
Post #1377835
Posted Friday, October 26, 2012 2:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 6,249, Visits: 7,404
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1377853
Posted Monday, October 29, 2012 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 11:53 AM
Points: 3, Visits: 13
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.
Post #1378245
Posted Monday, October 29, 2012 12:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 6,249, Visits: 7,404
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1378397
Posted Tuesday, October 30, 2012 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 15, 2013 11:53 AM
Points: 3, Visits: 13
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.

Post #1378751
Posted Tuesday, October 30, 2012 4:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 6,249, Visits: 7,404
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1379068
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse