calling a stored procedure

Question

calling a stored procedure

~masoodmjan

SSC-Addicted

Points: 499
More actions
March 10, 2004 at 8:43 am

#107688

Hi is it possible to call a stored procedure from t-SQL.
like
select raised_date from table where id = [stored porcedure]
the stored procedure will retrive some value.
if this is not possible then please suggest other ways.
Also i can i create user functions in SQL 7.0.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Maybe you should post what you have so far, along with some sample data and what you expect to get. Sounds like there is an easier workaround.

For your last quesion:

No, UDFs are not available in SQL7.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

~masoodmjan SSC-Addicted Points: 499 More actions · Answer 2

thanks for quick reply,

well the situation is that i have a huge sql built up at runtime, which include some date checks like records from to periods .

select * from table where datecolumn between date1 and date2

now if date1 is not specified then i have to get the min(datecolumn) from the table . well this sounds quite easy till here.

But i have many date checks in a single query so if date1 isnt specified i have to get the min value of the date column , so i wrote a store procedure which retirives min date or days betwee the date and today. coz some part of the sql check for last n days and when n days is not specified i have to get the day difference between today and the min date.

To make it more complex this is not only for date and days, it might contain for last n weeks, months, years ...

so i wrote a sp which will return the argument value

like

select * from table where datediff(d, column , getDate() ) > [sp to get the days diff from 2 dates]

I have done my best to explain my requirement.

thanks

Osoba SSCommitted Points: 1964 More actions · Answer 3

DO you want something like this:

CREATE PROCEDURE Return_Example @Value INT OUTPUT

AS

SELECT @Value = MAX(date) FROM

--this can be any logic you want including complex IF logic

go

declare @Value numeric

exec return_example @value=@value output

select * from table where datediff(d, column , getDate() ) > @value

hope this helps

john-132685 SSCertifiable Points: 6084 More actions · Answer 4

Do it another way using case statements.

Join your tables to select statements which retrieve the other info you want to use for comparison/manipulation. Then build case statement to translate the result columns. this can be done in an update statement as well. (and group by and other things can be done in the embedded select).

kinda like this Q&D example

select ...

case when datecol < w.maxdate then ... else ... end as dateresultcol

from tablea a

,( select max( datecol ) as maxdate

, min( datecol ) as mindate

from tablea ) w