calling stored procedure in an insert statement

[sandra64]

I am trying to call a procedure in an insert statement. The procedure takes in two dates and outputs how many working days are between those dates. I then want to use the output in my insert. Sounds simple enough but I'm going round in cirles with it.


here is my code

declare @numbofdays int
INSERT INTO sac_rfp(closing_date, rfp_sent_date, rfp_sent)
SELECT a.closing_date, a.rfp_sent_date,exec usp_GetWorkingDays closing_date, rfp_sent_date, @numbofdays OUTPUT
FROM advert a

It is complaining about the exec and the from which leads me to believe that the start of the exec needs to be separated from the rest of the statement. I've tried single quotes, double quotes but to no avail, hope someone can help.

cheers Sandra

 

[collierd]

Use a function rather than stored procedure
Very similar but these can be used in select statements

Damian.

 

[swampBoogie]

Use a function instead.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp

 

[sandra64]

Thanks for that but I've never written a funtion before so I went into the help and it says that SQL 7.0 doesn't support user-defined functions.

how do I create one?

 

[JamesLean]

Like you say SQL 7 doesn't have user functions. Only SQL 2000.

--James