Calling a Stored procedure from another, HELP !

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 2000 Forums

SQL Server Development (2000)

Calling a Stored procedure from another, HELP !

Author

Topic

gatonegro
Starting Member

4 Posts

Posted - 2004-09-21 : 12:31:51

Hi, everybody.
Here is my problem, i have a SP to calculate de AGE of a person , por example SP_AGE, this one returns an integer result of a diff between this date and the date of birth, so, i want to use this SP from another to use it as a condition, por example SP_SELECT_BY_AGE,
and i want to do something like this, but y have tried everything !!! see if the idea is understanded ...
-------------------------------------
SELECT *
FROM peoples
WHERE SP_AGE(peoples.dateofbirth) > 24
-------------------------------------
the idea is selecting everyone witch age is > 24 , and y want to use that SP and not whrite here what is writen in the SP_AGE.
HOPE I WAS CLEAR ...
THANKS AND HELP !!!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 12:44:10

well you could use a function instead of sp_age if you're using sql 2000.

but you don't even need the sproc or function:

SELECT *
FROM peoples
WHERE datediff(y, dateofbirth, GetDate()) > 24

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 12:44:33

well first off, a calculation like an age calculation should probably be done in a function. Also, may I ask why you are using datediff?? It can give you the difference between dates for different types of periods.

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 12:47:15

I hate my phone!!!

again

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 12:51:30

why the phone?? don't tell you have dial-up at work

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 13:02:07

no no... users call to ask questions... always when I'm just about typing a response...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 13:15:14

heh... put them on ignore. if they can call now they can call later

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 13:18:05

hehe very true... I should have in that case. I really didn't want to talk to her anyway.

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-21 : 13:27:40

You should try the DELL approach....

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 13:30:59

don't know that one... what is "the DELL approach"?

Go with the flow & have fun! Else fight the flow

gatonegro
Starting Member

4 Posts

Posted - 2004-09-21 : 14:34:56

I knew that , thanks , but the real meaning of my cuestion was, how can i LINK a SP using it in a diferent one, and use it as a function ore something. !!!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 14:41:10

well you can't in the way you want to.
functions are for that. look them up in BOL under
Create function

maybe if you told us what you want to do we can help you better

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 14:50:01

You can call an SP from another SP and pass a variable around, but it is not a function so you can't treat it as one...
Create Procedure blah1
As

Declare @myVar varchar(100)

Exec blah2 @myVar OUTPUT

Select @myVar

Corey

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-21 : 15:19:57

Corey...
What you say makes sense but why does this generate an Error?

Declare @myVar varchar(100)
EXEC sp_ExecuteSql N'Select Name from sysobjects' @myVar OUTPUT
Select @myVar

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@myVar'.

Surf On Dude!

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-21 : 15:33:02

quote:
Originally posted by clarkbaker1964

Corey...
What you say makes sense but why does this generate an Error?

Declare @myVar varchar(100)
EXEC sp_ExecuteSql N'Select Name from sysobjects' @myVar OUTPUT
Select @myVar

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@myVar'.

Surf On Dude!

Because it doesn't work that way.....just execute this, and tell me what you get...


EXEC sp_ExecuteSql N'Select Name from sysobjects'

Brett

8-)

gatonegro
Starting Member

4 Posts

Posted - 2004-09-21 : 16:02:22

THANK YOU ALL !!!!!!!
I THINK MI QUESTION IS VERY VERY ANSWERED .....
I WILL CONTINUE LEARNING ....
SEE YA !!!!!!!!!!

PD: I FORGOT !!! I AM A LITTLE PROGRAMER FROM ARGENTINA, AND I'M PROGRAMING WITH SQLS2000 AND VISUAL BASIC 6, JUST FOR THE TIP ;-)

BYE !!!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 16:08:14

little as in 'stature' or as in 'time spent'

Corey

Kristen
Test

22859 Posts

Posted - 2004-09-22 : 12:46:12

sequelly challenged ... yup, I think I can apply that to myself!

I dare say many would wish that I have no sequel!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources