Link to home
Start Free TrialLog in
Avatar of Trudye
TrudyeFlag for United States of America

asked on

Calling a Stored Proc from within a Stored Proc

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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.
Avatar of rachitkohli
rachitkohli
Flag of India image
It may be a used defined table function, which can be used in the select clause
Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
next: you CANNOT use PRINT in functions....

let me work this out...
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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

Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
you can do the insert like the last one:

                        INSERT INTO @date_range VALUES (NULL, NULL, 'Error in Processing DATE')
Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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)
Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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

Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
>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.


Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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)
Avatar of Trudye
Trudye
Flag of United States of America image

ASKER

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