Solved

Calling a Stored Proc from within a Stored Proc

Posted on 2008-06-10
19
452 Views
Last Modified: 2008-06-18
I need to call a DateProc from my Import_Export_Proc, have it return the @start_date and @end_date. Both dates are calc'd in the DateProc, nothing needs to be passed into the DateProc.
I saw something like this on the Internet:
SELECT StartDate, EndDate FROM sp_DateProc()
Don't I have to define the parms in the called stored proc like you do in a called func?
Any ideas how I do that?

Thanks  Guys,
Trudye
0
Comment
Question by:Trudye
  • 10
  • 8
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749755
you cannot call a proc inside a SELECT.
you can only use EXEC proc
if you want to use the results, you have to use either a stored function or a view.
if that is not possible, you have to "merge" the code of the 2 procedures, ie do not use 2 procedures.
0
 
LVL 14

Expert Comment

by:rachitkohli
ID: 21749764
It may be a used defined table function, which can be used in the select clause
0
 

Author Comment

by:Trudye
ID: 21750233
I decided to go with the UDF, but I'm getting a msg I don't understand
(Msg 102, Level 15, State 1, Procedure sp_Calc_Acxion_Start_End_Date, Line 20 Incorrect syntax near ')'.).

What I wrote looks just like what's in th book.
wrote the following code:
CREATE Function dbo.sp_Calc_Acxion_Start_End_Date()
RETURNS @Date_Range TABLE
(StartDate as Datetime,
EndDate as DateTime)
AS
BEGIN
--DECLARE @Processing_Date as DateTime, @hDate as DateTime
DECLARE @Processing_Date as DateTime, @StartDate as DateTime, @EndDate as DateTime, @hDate as DateTime
DECLARE @Cnt as Int
DECLARE @HCnt as Int
SET @Processing_Date = (REPLACE(REPLACE(CONVERT(varchar(8), getdate(), 112),'-',''),' ',''))
--SET @Processing_Date = '20080608'
                        Print '00'
                        Print @Processing_Date

--            This runs on a Sunday, insure your processing date is a Saturday
IF DatePart(dw,(DateAdd (Day, -1, @Processing_Date))) = 7
      BEGIN
            IF DatePart(dw, (DateAdd(Day, -41, @Processing_Date))) = 7
                  BEGIN
                        SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                        SET @EndDate = DateAdd(Day, 6, @StartDate)
                        Print '1st'
                        Print @StartDate
                        Print @EndDate
                  END
            ELSE
                  BEGIN
                  -- Subtract one from @Processing_Date until its = Saturday
                        SET @Cnt = 0
                        SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                        WHILE (@Cnt < 7)
                              BEGIN
                                    SET @hDate = DateAdd(Day, -1, @StartDate)            --keep subtracting 1 until Prev Saturday reached
                                    IF DatePart (dw, @hDate) = 7
                                          BEGIN
                                                SET @Cnt = 7
                                          END
                                    ELSE
                                          BEGIN
                                                SET @Cnt =  @Cnt + 1
                                          END
                              END
                        SET @hDate = @StartDate
                        SET @EndDate = DateAdd(Day, 6, @StartDate)
                              Print '2nd'
                              Print 'Start'
                              Print @StartDate
                              Print 'END'
                              Print @EndDate
                              Print 'Processing date'
                              Print @Processing_Date
                  END      
      END
ELSE
      BEGIN
            SET @cnt = 1
            WHILE (@cnt < 7)
                  BEGIN
                  SET @hDate = DateAdd(Day, -1, @Processing_Date) --keep subtracting 1 until Prev Sunday reached      
                        IF DatePart (dw, @hDate) = 1
                              BEGIN
                                    SET @Cnt = 7
                                    SET @Processing_Date = @hDate
                                          Print 'Find PRev Sunday Processing Date'
                                          Print 'Processing date'
                                          Print @Processing_Date
                              END
                        ELSE
                              BEGIN
                                    Print 'Sub another Day'
                                    SET @Cnt =  @Cnt + 1
                                    SET @hDate = DateAdd(Day, -1, @Processing_Date)      
                              END      
                  END
                  -- @Processing_Date s/b = Sunday
                  --If job not processed on a Sunday backup to the prev Sunday (to assign processing date)
Set @HCnt = DatePart(dw,(DateAdd (Day, -1, @Processing_Date)))
Print '@HCnt'
Print @HCnt
            IF DatePart(dw,(DateAdd (Day, -1, @Processing_Date))) = 7
                  BEGIN
                  IF DatePart(dw, (DateAdd(Day, -41, @Processing_Date))) = 7
                        BEGIN
                              SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                              SET @EndDate = DateAdd(Day, 6, @StartDate)
                                                      Print '3rd'
                                                      Print 'Processing date'
                                                      Print @Processing_Date
                                                      Print @StartDate
                                                      Print @EndDate                        
                        END
                  ELSE
                        BEGIN
                  -- Subtract one from @Processing_Date until its = Saturday
                              SET @Cnt = 0
                              SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                              WHILE (@Cnt < 7)
                                    BEGIN
                                          SET @hDate = DateAdd(Day, -1, @StartDate)            --keep subtracting 1 until Prev Saturday reached
                                          IF DatePart (dw, @hDate) = 7
                                                BEGIN
                                                      SET @Cnt = 7
                                                END
                                          ELSE
                                                BEGIN
                                                      SET @Cnt =  @Cnt + 1
                                                END
                              END
                        SET @hDate = @StartDate
                        SET @EndDate = DateAdd(Day, 6, @StartDate)
                              Print '2nd'
                              Print 'Start'
                              Print @StartDate
                              Print 'END'
                              Print @EndDate
                              Print 'Processing date'
                              Print @Processing_Date
                  END      
            END
            ELSE      
                  BEGIN
                        Print 'Error in Processing DATE'
                  END
      END
END
Thanks Guys
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750243
CREATE Function dbo.sp_Calc_Acxion_Start_End_Date()
RETURNS @Date_Range TABLE
(StartDate as Datetime,
EndDate as DateTime)
AS

must be:

CREATE Function dbo.sp_Calc_Acxion_Start_End_Date
(@StartDate as Datetime
,@EndDate as DateTime)
RETURNS @Date_Range TABLE
AS
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750247
next: you CANNOT use PRINT in functions....

let me work this out...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750281
here we go with a valid syntax:
CREATE Function dbo.sp_Calc_Acxion_Start_End_Date(@getdate datetime )
RETURNS @Date_Range TABLE (StartDate DateTime, EndDate DateTime, result varchar(100) )
AS
BEGIN
--DECLARE @Processing_Date as DateTime, @hDate as DateTime
DECLARE @Processing_Date as DateTime, @StartDate as DateTime, @EndDate as DateTime, @hDate as DateTime
DECLARE @Cnt as Int
DECLARE @HCnt as Int
 
SET @Processing_Date = (REPLACE(REPLACE(CONVERT(varchar(8), @getdate, 112),'-',''),' ',''))
 
--            This runs on a Sunday, insure your processing date is a Saturday
IF DatePart(dw,(DateAdd (Day, -1, @Processing_Date))) = 7
      BEGIN
            IF DatePart(dw, (DateAdd(Day, -41, @Processing_Date))) = 7
                  BEGIN
                        SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                        SET @EndDate = DateAdd(Day, 6, @StartDate)
                        INSERT INTO @date_range VALUES (@StartDate, @EndDate, '1st')
                  END
            ELSE
                  BEGIN
                  -- Subtract one from @Processing_Date until its = Saturday
                        SET @Cnt = 0
                        SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                        WHILE (@Cnt < 7)
                              BEGIN
                                    SET @hDate = DateAdd(Day, -1, @StartDate)            --keep subtracting 1 until Prev Saturday reached
                                    IF DatePart (dw, @hDate) = 7
                                          BEGIN
                                                SET @Cnt = 7
                                          END
                                    ELSE
                                          BEGIN
                                                SET @Cnt =  @Cnt + 1
                                          END
                              END
                        SET @hDate = @StartDate
                        SET @EndDate = DateAdd(Day, 6, @StartDate)
                        INSERT INTO @date_range VALUES (@StartDate, @EndDate, '2ns')
                  END      
      END
ELSE
      BEGIN
            SET @cnt = 1
            WHILE (@cnt < 7)
                  BEGIN
                  SET @hDate = DateAdd(Day, -1, @Processing_Date) --keep subtracting 1 until Prev Sunday reached      
                        IF DatePart (dw, @hDate) = 1
                              BEGIN
                                    SET @Cnt = 7
                                    SET @Processing_Date = @hDate
                              END
                        ELSE
                              BEGIN
                                    SET @Cnt =  @Cnt + 1
                                    SET @hDate = DateAdd(Day, -1, @Processing_Date)      
                              END      
                  END
                  -- @Processing_Date s/b = Sunday
                  --If job not processed on a Sunday backup to the prev Sunday (to assign processing date)
            Set @HCnt = DatePart(dw,(DateAdd (Day, -1, @Processing_Date)))
            IF DatePart(dw,(DateAdd (Day, -1, @Processing_Date))) = 7
                  BEGIN
                  IF DatePart(dw, (DateAdd(Day, -41, @Processing_Date))) = 7
                        BEGIN
                              SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                              SET @EndDate = DateAdd(Day, 6, @StartDate)
                        INSERT INTO @date_range VALUES (@StartDate, @EndDate, '3rd')
                        END
                  ELSE
                        BEGIN
                  -- Subtract one from @Processing_Date until its = Saturday
                              SET @Cnt = 0
                              SET @StartDate = DateAdd(Day, -41, @Processing_Date)
                              WHILE (@Cnt < 7)
                                    BEGIN
                                          SET @hDate = DateAdd(Day, -1, @StartDate)            --keep subtracting 1 until Prev Saturday reached
                                          IF DatePart (dw, @hDate) = 7
                                                BEGIN
                                                      SET @Cnt = 7
                                                END
                                          ELSE
                                                BEGIN
                                                      SET @Cnt =  @Cnt + 1
                                                END
                              END
                        SET @hDate = @StartDate
                        SET @EndDate = DateAdd(Day, 6, @StartDate)
                        INSERT INTO @date_range VALUES (@StartDate, @EndDate, '4th')
                  END      
            END
            ELSE      
                  BEGIN
                        INSERT INTO @date_range VALUES (NULL, NULL, 'Error in Processing DATE')
                  END
      END
 
  RETURN
END
go
declare @getdate datetime 
set @getdate = getdate()
select * from dbo.sp_Calc_Acxion_Start_End_Date(@getdate)
go
drop function dbo.sp_Calc_Acxion_Start_End_Date

Open in new window

0
 

Author Comment

by:Trudye
ID: 21750324
angellll, thank you for responding so quickly.I have inserted your code and was about to test it when a question occured to me.  I use the Print to ck my code. If I can't use print what are my alternatives.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21750361
you can do the insert like the last one:

                        INSERT INTO @date_range VALUES (NULL, NULL, 'Error in Processing DATE')
0
 

Author Comment

by:Trudye
ID: 21751185
I'm having a problem with my Dates, I have included my Statements and my Output.
Statements:
BEGIN
            Print 'Ck Processing Date'
            Print @Processing_Date
SET @hDate = DateAdd(Day, -1, @Processing_Date)
            Print 'Ck hdate'      
            Print @hDate

Output Im getting:
Ck Processing date
06/10/2008
Ck hdate
Jun  9 200
Msg 8115, Level 16, State 2, Procedure sp_Calc_Acxion_Start_End_Date, Line 65
Arithmetic overflow error converting expression to data type datetime.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21751246
SET @Processing_Date = (REPLACE(REPLACE(CONVERT(varchar(8), @getdate, 112),'-',''),' ',''))
is not good.

this is better:
SET @Processing_Date = CONVERT(datetime, CONVERT(varchar(10), @getdate, 120), 120)
0
 

Author Comment

by:Trudye
ID: 21758408
Ok, I finally got the UDF working after doing some tweaking. Now the BIG question, how do I call the UDF from my stored procedure?
With a SELECT all statement?

Thanks,
Trudye

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21759168
yes, starting with:
declare @g datetime
set nocount on
set @g = getdate()
 
SELECT g.StartDate, g.EndDate 
FROM dbo.sp_Calc_Acxion_Start_End_Date(@g) p
 
 
or to join with a existing table:
 
 
declare @g datetime
set nocount on
set @g = getdate()
 
SELECT t.*
FROM dbo.sp_Calc_Acxion_Start_End_Date(@g) p
JOIN yourtable t
  ON t.some_date_field BETWEEN g.startdate and g.enddate

Open in new window

0
 

Author Comment

by:Trudye
ID: 21760641
Sorry to keep bugging you but I have another question.
I have three fields in my table (that returns from the UDF) StartDate, EndDate, Result. So do I have to return the enter table as one field (nvarchar) and then convert the
dates back to date fields?
CREATE Function dbo.sp_Calc_Acxion_Start_End_Date(@getdate datetime )
RETURNS @Date_Range TABLE (StartDate DateTime, EndDate DateTime, result varchar(100) )

Also I the return date is 2008-0426000.00.00.000 format. Can I reformat it during the return?

Last but not least, can I open a new question I under estimated the number of points associated with this question?

Thanx,
Trudye
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21760695
>I have three fields in my table (that returns from the UDF) StartDate, EndDate, Result. So do I have to return the enter table as one field (nvarchar) and then convert the
dates back to date fields?

???? sorry but I did not understand that...

>Also I the return date is 2008-0426000.00.00.000 format. Can I reformat it during the return?
yes, check out the CONVERT(varchar(10), yourfield, 101) syntax or the like (101 means mm/dd/yyyy)

>Last but not least, can I open a new question I under estimated the number of points associated with this question?
just increase the points here, if you wish, up to 500.


0
 

Author Comment

by:Trudye
ID: 21761175
My Return statement was:
ALTER Function dbo.udf_Calc_Acxion_Start_End_Date(@getdate datetime )
RETURNS @Date_Range TABLE (StartDate DateTime, EndDate DateTime, result varchar(100) )

If I make my return statement:
declare @g datetime
set nocount on
set @g = getdate()
 
SELECT g.StartDate, g.EndDate
FROM dbo.sp_Calc_Acxion_Start_End_Date(@g) p

Then @g cannot be Declared as datetime, it will have to be Declared as nvarchar (120).
Then once I get it into the calling routine I have to parse the date(s) out and convert it back to datetime?
Am I right or have I completely missed the boat?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21762210
>Am I right or have I completely missed the boat?
I fear so...

this code:
declare @g datetime
set nocount on
set @g = getdate()
 
SELECT g.StartDate, g.EndDate
FROM dbo.sp_Calc_Acxion_Start_End_Date(@g) p

is how to USE the function, and is not part of the function itself...
0
 

Author Comment

by:Trudye
ID: 21804048
I cannot seem to access the fields in the table after they are returned by the Select statement. How do I access g. Startdate and use it in the Insert Into statement?

ALTER PROCEDURE [dbo].[sp_Processing_1st_Pass]
AS
BEGIN
declare @g datetime
set nocount on
set @g = getdate()

SELECT g.StartDate, g.EndDate, g.Result
FROM dbo.Acxion_Start_End_Date(@g)

                        /* Clear Input table */      
            TRUNCATE table zGE_RCF_POS_IMPORT

                        /*  Get records from DMS */
            INSERT INTO POS_IMPORT (Lname)
            SELECT DISTINCT Name
            FROM NameMstr
            WHERE EFDT >= g.StartDate AND EFDT <=  g.EndDate AND
                  NameTag =1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21806513
you proc code is a bit wrong, at least as from what I can tell.

>TRUNCATE table zGE_RCF_POS_IMPORT
you truncate that table, but:
>INSERT INTO POS_IMPORT (Lname)
which is a different table; is that right?


>I cannot seem to access the fields in the table after they are returned by the Select statement.
you only have 1 SELECT which returns the result from the function.
the insert/select will not return anything to the end-user, apart from the fact that the current syntax is wrong (the alias g is not know for the select)
0
 

Author Comment

by:Trudye
ID: 21817992
Thanks Savant for all your help I figured it out. I wrote a SET statement that execs a Select statement
The result is the start and end dates are places in scalar fields
@endDate = Select .......
Not sure if that is the smartest way to accomplish the task but it did accomplish the task.
Thanks again for all your help and for hanging in there with me,
Trudye
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question