using result of a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using result of a stored procedure

Author

Topic

frankDK
Starting Member

14 Posts

Posted - 2008-10-29 : 07:04:59

Hi

i have problems with some syntax in a stored sql-statement, the statement looks like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[getAllChildrenInCounty]

@countyId int
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ReturnValue INT
EXEC @ReturnValue = getMonths_ChildId c.childid

SELECT i.institutionName, c.childid, c.navn, c.screen2, ReturnValue=@ReturnValue
FROM children c inner join institutions i
ON c.institution = i.institutionId
where (c.countyid = @countyId)
Group by c.institution, i.institutionName, c.childid, c.navn, c.screen2
Order by i.institutionName

END

what i want is a result looking like this:
institutionName, childid, navn, screen2, age_in_month

Age in month comes from a stored procedure, that takes childid as parameter.

can anyone help me?

Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:12:40

how is procedure getMonths_ChildId defined? does it have an OUTPUT parameter?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 07:13:04

I think you should rewrite the getMonths_ChildId stored procedure and turn it into a function.

You could then just use that function in the SELECT clause of your procedure.

Something like.... (asuming that there is a function called fn_getMonths_ChildId



SELECT
	i.institutionName
	, c.childid
	, c.navn
	, c.screen2
	, fn_getMonths_ChildId(c.[childId])
FROM
	children c
	inner join institutions i ON c.institution = i.institutionId
where
	(c.countyid = @countyId)
Group by
	c.institution
	, i.institutionName
	, c.childid
	, c.navn
	, c.screen2
	, fn_getMonths_ChildId(c.[childId])
Order by
	i.institutionName

-- NB I'm not sure about your GROUP BY clause here...

However, it might be a good idea for you to post

1) sample data
2) required output
3) Code of getMonths_ChildId

Regards,

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 07:17:17

Another way to do this would be to rewrite getMonths_ChildId as a table value function (one that returns a table of values) Ideally returning a childId and Months columns. Then just JOIN that function in your select statement and reference the [months] column.

-------------
Charlie

frankDK
Starting Member

14 Posts

Posted - 2008-10-29 : 07:34:07

Hi again

this is getMonth_childId:
ALTER PROCEDURE [dbo].[getMonths_ChildId]
-- Add the parameters for the stored procedure here
@childId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select datediff(month,
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)),
getdate())
from cprtabel where (id = @childId)
END

frankDK
Starting Member

14 Posts

Posted - 2008-10-29 : 07:41:24

I just tried to create a function:
CREATE FUNCTION fnGetMonth_ChildId
(
-- Add the parameters for the function here
@childId bigint
)
RETURNS TABLE
AS
RETURN
(
select datediff(month,
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)),
getdate())
from cprtabel where (id = @childId)
)
GO

but it tells me that:
Msg 443, Level 16, State 1, Procedure fnGetMonth_ChildId, Line 17
Invalid use of 'getdate' within a function.

frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:42:28

do like this

ALTER PROCEDURE [dbo].[getMonths_ChildId] 
-- Add the parameters for the stored procedure here
@childId bigint,
@RetVal int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @RetVal=datediff(month, 
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)), 
getdate())
from cprtabel where (id = @childId)
END

then in your procedure do like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[getAllChildrenInCounty] 

@countyId int
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ReturnValue INT
EXEC getMonths_ChildId @childid,@ReturnValue OUTPUT


SELECT i.institutionName, c.childid, c.navn, c.screen2, ReturnValue=@ReturnValue
FROM children c inner join institutions i
ON c.institution = i.institutionId
where (c.countyid = @countyId)
Group by c.institution, i.institutionName, c.childid, c.navn, c.screen2
Order by i.institutionName

END

or consider making it a function as Charlie suggested. it will be something like

ALTER FUNCTION [dbo].[getMonths_ChildId] 
(
-- Add the parameters for the stored procedure here
@childId bigint
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @RetVal int
select @RetVal=datediff(month, 
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)), 
getdate())
from cprtabel where (id = @childId)
RETURN @RetVal
END

and then use like

ALTER PROCEDURE [dbo].[getAllChildrenInCounty] 

@countyId int
AS
BEGIN

SET NOCOUNT ON;




SELECT i.institutionName, c.childid, c.navn, c.screen2, [dbo].[getMonths_ChildId] (c.childid)
FROM children c inner join institutions i
ON c.institution = i.institutionId
where (c.countyid = @countyId)
Group by c.institution, i.institutionName, c.childid, c.navn, c.screen2
Order by i.institutionName

END

frankDK
Starting Member

14 Posts

Posted - 2008-10-29 : 07:49:17

hi again

sorry i did'nt see this:
1) sample data

cprtabel:
id, cprnr
1, 3009861565
2, 0506063020

institutions tabel:
id, institutionname
1, inst1
2, inst2
3, inst3

children tabel:
childid,navn, screen2
1,name of child,29-09-2008 10:39:43
2, new name, 29-09-2008 10:39:43

2) required output
institutionName, childid, navn, screen2, age_in_month
inst1, 1, name of child, 29-09-2008 10:39:43, 260
inst1, 2, name of child2, 01-02-2008 10:39:43, 200

3) Code of getMonths_ChildId
this is getMonth_childId:
ALTER PROCEDURE [dbo].[getMonths_ChildId]
-- Add the parameters for the stored procedure here
@childId bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select datediff(month,
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)),
getdate())
from cprtabel where (id = @childId)
END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 07:50:30

Hi FrankDK

Functions should be deterministic. That is they should always produce the same output with the same inputs (assuming the data is the same).

As such they have a few limitations (one being that you can't call certain other procs and functions from them (and that they can't modify data in any way).

You will have to change the function so it also has an input parameter of @currentDate. Then pass that the current date from GETDATE. Also If you only want the months for 1 child then you don't want a table valued function. you want a *scaler* function. Therefore you need to return an INT (number of months)

SOMETHING LIKE


CREATE FUNCTION fnGetMonth_ChildId
(	
-- Add the parameters for the function here
@childId bigint
, @curDate DATETIME
)
RETURNS INT
AS
RETURN (
select	datediff(month, 
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)), 
@curDate)
from cprtabel where (id = @childId)
)
GO

And then call it with


DECLARE @curDate DATETIME
SET @curDate = GETDATE()
SELECT fnGetMonth_ChildId(childId,@curDate)

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 07:52:00

And the above assumes there is only 1 row returned by your select statement!

-------------
Charlie

frankDK
Starting Member

14 Posts

Posted - 2008-10-29 : 07:53:52

hi i used the first part, but got an error:
Msg 137, Level 15, State 2, Procedure getAllChildrenInCounty2, Line 11
Must declare the variable '@childid'.

in this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[getAllChildrenInCounty]

@countyId int
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ReturnValue INT
EXEC getMonths_ChildId @childid,@ReturnValue OUTPUT

SELECT i.institutionName, c.childid, c.navn, c.screen2, ReturnValue=@ReturnValue
FROM children c inner join institutions i
ON c.institution = i.institutionId
where (c.countyid = @countyId)
Group by c.institution, i.institutionName, c.childid, c.navn, c.screen2
Order by i.institutionName

END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 07:55:07

quote:

Originally posted by visakh16

do like this

ALTER PROCEDURE [dbo].[getMonths_ChildId] 
-- Add the parameters for the stored procedure here
@childId bigint,
@RetVal int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @RetVal=datediff(month, 
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)), 
getdate())
from cprtabel where (id = @childId)
END

then in your procedure do like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[getAllChildrenInCounty] 

@countyId int
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ReturnValue INT
EXEC getMonths_ChildId @childid,@ReturnValue OUTPUT


SELECT i.institutionName, c.childid, c.navn, c.screen2, ReturnValue=@ReturnValue
FROM children c inner join institutions i
ON c.institution = i.institutionId
where (c.countyid = @countyId)
Group by c.institution, i.institutionName, c.childid, c.navn, c.screen2
Order by i.institutionName

END

.......

Hi Viaskh,

This won't work as the op wants the number of months for each child. You'd need to either make a scaler function and call it in the select statement, or CROSS APPLY which I know you are fond of, make a table valued function and JOIN it in the SELECT statement, or Make a temp table to store all the months and then populate that with an INSERT EXEC or similar. They way you've posted won't work as when the stored proc is called there is no value for childId

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:56:01

quote:
Originally posted by frankDK

hi i used the first part, but got an error:
Msg 137, Level 15, State 2, Procedure getAllChildrenInCounty2, Line 11
Must declare the variable '@childid'.

in this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[getAllChildrenInCounty]

@countyId int
AS
BEGIN

SET NOCOUNT ON;

DECLARE @ReturnValue INT,@childid
set @childid=...
EXEC getMonths_ChildId @childid,@ReturnValue OUTPUT

SELECT i.institutionName, c.childid, c.navn, c.screen2, ReturnValue=@ReturnValue
FROM children c inner join institutions i
ON c.institution = i.institutionId
where (c.countyid = @countyId)
Group by c.institution, i.institutionName, c.childid, c.navn, c.screen2
Order by i.institutionName

END

you need to declare it and assign it a value like above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:58:49

then only way is my second suggestion frank as you want it for each childid value

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 07:59:09

Yes that will compile but it will not give you all the proper values for each child in the second SELECT statement. It will only show the values for the particular childId you passed it you will then up with data like this....


ChildId  | Months
a          10
b          10
c          10
d          10
e          10

Assuming that childId a is passed to the first sp and the value returned is 10. You will *not* get the proper months for each child this way.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 08:00:47

I think this should be your function....

Posted - 10/29/2008 : 07:50:30
Hi FrankDK

Functions should be deterministic. That is they should always produce the same output with the same inputs (assuming the data is the same).

As such they have a few limitations (one being that you can't call certain other procs and functions from them (and that they can't modify data in any way).

You will have to change the function so it also has an input parameter of @currentDate. Then pass that the current date from GETDATE. Also If you only want the months for 1 child then you don't want a table valued function. you want a *scaler* function. Therefore you need to return an INT (number of months)

SOMETHING LIKE
CREATE FUNCTION fnGetMonth_ChildId
(
-- Add the parameters for the function here
@childId bigint
, @curDate DATETIME
)
RETURNS INT
AS
RETURN (
select datediff(month,
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)),
@curDate)
from cprtabel where (id = @childId)
)
GO

And then call it with
DECLARE @curDate DATETIME
SET @curDate = GETDATE()
SELECT fnGetMonth_ChildId(childId,@curDate)

-------------
Charlie

-------------
Charlie

frankDK
Starting Member

14 Posts

Posted - 2008-10-29 : 08:04:41

charlie i just tried to paste your function, but i gives me:
Msg 170, Level 15, State 31, Procedure fnGetMonth_ChildId, Line 9
Line 9: Incorrect syntax near 'RETURN'.

frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:05:57

quote:
Originally posted by Transact Charlie

Yes that will compile but it will not give you all the proper values for each child in the second SELECT statement. It will only show the values for the particular childId you passed it you will then up with data like this....

ChildId | Months
a 10
b 10
c 10
d 10
e 10

Assuming that childId a is passed to the first sp and the value returned is 10. You will *not* get the proper months for each child this way.

-------------
Charlie

sorry didnt get that. you will get value corresponding to each child if you're using it as a UDF.can you explain why you think it wont work?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 08:08:11

Sry, try this instead.


CREATE FUNCTION fnGetMonth_ChildId
(	
-- Add the parameters for the function here
@childId bigint
, @curDate DATETIME
)
RETURNS INT

AS BEGIN

	DECLARE @months INT

	SELECT @months = DATEDIFF (
		MONTH
		, CONVERT(DATETIME, '20' + SUBSTRING(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2))
		, @curDate
		)
	FROM
		cprTabel
	WHERE
		Id = @childID

	RETURN @months
END

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:09:25

quote:
Originally posted by frankDK

charlie i just tried to paste your function, but i gives me:
Msg 170, Level 15, State 31, Procedure fnGetMonth_ChildId, Line 9
Line 9: Incorrect syntax near 'RETURN'.

frank

what about this small modification?

CREATE FUNCTION fnGetMonth_ChildId
( 
-- Add the parameters for the function here
@childId bigint
, @curDate DATETIME
)
RETURNS INT
AS
DECLARE  int
select @RetVal=datediff(month, 
convert(datetime, '20' + substring(cprnr, 5, 2) + substring(cprnr, 3, 2) + left(cprnr, 2)), 
@curDate)
from cprtabel where (id = @childId)
RETURN @RetVal
GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 08:09:49

quote:
Originally posted by visakh16

quote:
Originally posted by Transact Charlie

Yes that will compile but it will not give you all the proper values for each child in the second SELECT statement. It will only show the values for the particular childId you passed it you will then up with data like this....

ChildId | Months
a 10
b 10
c 10
d 10
e 10

Assuming that childId a is passed to the first sp and the value returned is 10. You will *not* get the proper months for each child this way.

-------------
Charlie

sorry didnt get that. you will get value corresponding to each child if you're using it as a UDF.can you explain why you think it wont work?

Sorry Visakh -- looks like all our replies are out of synch. I was referring to the stored proc method that returned 1 value. The scaler function will work. I suggested it myself.

Charlie.

-------------
Charlie

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources