Need help in SP

Question

Post reply

Need help in SP

Zohaib Anwar

Ten Centuries

Points: 1120
More actions
December 18, 2013 at 6:17 am

#396007

Hi All I have following two stored Procedures and I want to create 3rd one which will take the values of both 1st and 2nd SPs and Subtract 1st from 2nd.... i.e. 31 -13 .. can any one plz help me its urgent...
create Proc first
AS
declare @date as datetime
DECLARE @date2 int
set @date=getdate()
set @date2 = datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))
select @date2
exec first
Create Proc Sec
AS
declare @date as datetime
declare @date2 as int
set @date=getdate()
set @date2 = datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))-Datepart(dd, Getdate())
select @date2
exec sec
====================== This is what i want ===============
Create Proc Third
AS
Declare @date1 int
, @date2 int
, @date3 int
set @date1 = exec first
set @date2 = exec Sec
Set @date3 = @date1 - @date2
print @date3
PLZ HELP ITS Really URGENT....

Viewing 14 posts - 1 through 13 (of 13 total)

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

Prasad.N Ten Centuries Points: 1083 More actions · Answer 1

use table variable tables for easy understanding.

-----------modify only third sp

Create Proc Third

AS

begin

Declare @date1 int

, @date2 int

, @date3 int

declare @VerTable2 table(SndSpV int)

declare @VerTable1 table(FstSpV int)

insert into @VerTable1 exec first

insert into @VerTable2 exec sec

select @date1 = FstSpV from @VerTable1

select @date2 = SndSpV from @VerTable2

Set @date3 = @date1 - @date2

print @date3

end

Prasad.N
Hyderabad-India.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

Zohaib Anwar (12/18/2013)
Hi All I have following two stored Procedures and I want to create 3rd one which will take the values of both 1st and 2nd SPs and Subtract 1st from 2nd.... i.e. 31 -13 .. can any one plz help me its urgent...

I think you may be overcomplicating things for something this simple. Unless you've just substituted code 🙂

You can use the return statement or output variables or you could consider using as User Defined Function.

create Proc first

AS

declare @date as datetime

DECLARE @date2 int

set @date=getdate()

set @date2 = datediff(day, dateadd(day, 1-day(@date), @date),

dateadd(month, 1, dateadd(day, 1-day(@date), @date)))

return @date2

GO

exec first

Create Proc Sec

AS

declare @date as datetime

declare @date2 as int

set @date=getdate()

set @date2 = datediff(day, dateadd(day, 1-day(@date), @date),

dateadd(month, 1, dateadd(day, 1-day(@date), @date)))-Datepart(dd, Getdate())

return @date2

GO

exec sec

Create Proc Third

AS

Declare @date1 int

, @date2 int

, @date3 int

exec @date1 = first

exec @date2 = Sec

Set @date3 = @date1 - @date2

print @date3

GO

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 3

Looks like you need an OUTPUT parameter

CREATE PROC USP_first @id INT OUTPUT

AS

DECLARE @date AS DATETIME

SET @date = GETDATE()

SET @id = DATEDIFF(day , DATEADD(day , 1 - DAY(@date) , @date) ,

DATEADD(month , 1 , DATEADD(day , 1 - DAY(@date) , @date)))

RETURN @id

GO

CREATE PROC usp_Sec @Id INT OUTPUT

AS

DECLARE @date AS DATETIME

SET @date = GETDATE()

SET @id = DATEDIFF(day , DATEADD(day , 1 - DAY(@date) , @date) ,

DATEADD(month , 1 , DATEADD(day , 1 - DAY(@date) , @date))) - DATEPART(dd , GETDATE())

RETURN @id

GO

--====================== This is what i want ===============

CREATE PROC usp_Third

AS

DECLARE @date1 INT

DECLARE

@d INT

, @e INT

EXEC USP_first

@d OUTPUT

EXEC USP_sec

@e OUTPUT

SELECT

@D - @e

GO

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

CKX SSCrazy Points: 2519 More actions · Answer 4

I would use the OUTPUT Parameter approach suggested.

It's more appropriate given the circumstances you describe.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

CK2 (12/18/2013)
I would use the OUTPUT Parameter approach suggested.
It's more appropriate given the circumstances you describe.

I wouldn't use that approach for a simple date calculation. There are no table references so these should be safe to use in a UDF.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

MysteryJimbo (12/18/2013)
CK2 (12/18/2013)
I would use the OUTPUT Parameter approach suggested.
It's more appropriate given the circumstances you describe.
I wouldn't use that approach for a simple date calculation. There are no table references so these should be safe to use in a UDF.

I agree that the original is way over complicated and that a function is appropriate here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

You don't need all the extra variables and such in these calculations either. Everything is based off getdate() so there is no need to store interim values. This can all be converted into 3 iTVF very easily.

create function GetFirst()

returns table with schemabinding as

return

select datediff(day, dateadd(day, 1-day(getdate()), getdate()), dateadd(month, 1, dateadd(day, 1-day(getdate()), getdate()))) as MyFirst

go

create function GetSecond()

returns table with schemabinding as

return

select datediff(day, dateadd(day, 1-day(getdate()), getdate()), dateadd(month, 1, dateadd(day, 1-day(getdate()), getdate())))-Datepart(dd, Getdate()) as MySecond

go

create function GetThird()

returns table with schemabinding as

return

select f.MyFirst - s.MySecond as MyThird

from dbo.GetFirst() f

cross apply dbo.GetSecond() s

go

select * from dbo.GetThird()

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Heh... Good Lordy!!! THIS type of thing is the perfect reason for why a simple comment or two is necessary in code.

I might be missing a nuance here but look carefully at what the first and second functions actually do and then understand why I think the following formula works just fine.

SELECT DAY(GETDATE());

I'll give you a hint. The first function can be dramatically simplified to this...

SELECT DAY(DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0)-1)

... and the second function can be simplified to this...

SELECT DATEDIFF(dd,GETDATE(),DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0)-1)

If you've given up, here it is:

1. The first function returns the number of days in the current month.

2. The second function calculates the number of days left to go in the current month.

3. If you subtract the number of days to go in the current month from the number of days in the current month, what do you get?

[font="Arial Black"]TODAY!!! [/font];-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

amy26 SSCrazy Eights Points: 8926 More actions · Answer 9

So, wherever you go...there you are? 😀 Hehe, sorry couldn't resist. 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

Sean Lange (12/18/2013)
You don't need all the extra variables and such in these calculations either. Everything is based off getdate() so there is no need to store interim values. This can all be converted into 3 iTVF very easily.
create function GetFirst()
returns table with schemabinding as
return
select datediff(day, dateadd(day, 1-day(getdate()), getdate()), dateadd(month, 1, dateadd(day, 1-day(getdate()), getdate()))) as MyFirst
go
create function GetSecond()
returns table with schemabinding as
return
select datediff(day, dateadd(day, 1-day(getdate()), getdate()), dateadd(month, 1, dateadd(day, 1-day(getdate()), getdate())))-Datepart(dd, Getdate()) as MySecond
go
create function GetThird()
returns table with schemabinding as
return
select f.MyFirst - s.MySecond as MyThird
from dbo.GetFirst() f
cross apply dbo.GetSecond() s
go
select * from dbo.GetThird()

+1000

Nice catch Jeff:-D

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

amy26 (12/18/2013)
So, wherever you go...there you are? 😀 Hehe, sorry couldn't resist. 🙂

DBCC TIMEWARP on sterioids. '-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Jeff Moden (12/18/2013)
amy26 (12/18/2013)
So, wherever you go...there you are? 😀 Hehe, sorry couldn't resist. 🙂
DBCC TIMEWARP on sterioids. '-)

That is the circular referenced verbose version. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Zohaib Anwar (12/18/2013)
Hi All I have following two stored Procedures and I want to create 3rd one which will take the values of both 1st and 2nd SPs and Subtract 1st from 2nd.... i.e. 31 -13 .. can any one plz help me its urgent...
create Proc first
AS
declare @date as datetime
DECLARE @date2 int
set @date=getdate()
set @date2 = datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))
select @date2
exec first
Create Proc Sec
AS
declare @date as datetime
declare @date2 as int
set @date=getdate()
set @date2 = datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))-Datepart(dd, Getdate())
select @date2
exec sec
====================== This is what i want ===============
Create Proc Third
AS
Declare @date1 int
, @date2 int
, @date3 int
set @date1 = exec first
set @date2 = exec Sec
Set @date3 = @date1 - @date2
print @date3
PLZ HELP ITS Really URGENT....

Just taking a snapshot of the original post. I wouldn't want this one to mysteriously go away because it's a perfect example of why documentation in code is so very important.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)