Solved

Calling a Stored Proc from within a Stored Proc

Posted on 2008-06-10
19
412 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 142

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

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 142

Expert Comment

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

let me work this out...
0
 
LVL 142

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 142

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 142

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 142

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 142

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 142

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now