Difference between a stored procedure and function

Question

Post reply

Difference between a stored procedure and function

anthonyjoe

Grasshopper

Points: 14
More actions
October 31, 2003 at 11:29 pm

#82529

whats the difference between a stored procedure and function ?

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

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

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

a stored procedure is executed

exec s_mysp

whereas a function can be used in a select statement - depending on it's type.

A function has to be deterministic.

See bol for a description.

Cursors never.

DTS - only when needed and never to control.

Cursors never.
DTS - only when needed and never to control.

anthonyjoe Grasshopper Points: 14 More actions · Answer 2

what does one mean by saying a function has to be deterministic?

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

Given the same parameters it must return the same result.

You cannot use things like getdate() within the function.

There are a lot of restrictions on functions - see bol.

Cursors never.

DTS - only when needed and never to control.

Cursors never.
DTS - only when needed and never to control.

taner Valued Member Points: 53 More actions · Answer 4

UDFs can not have side affects either. Where a side affect is defined as "Anything that reaches outside the scope of the function – such as changing tables, sending e-mails, or system or database parameter changes."

vidya_pande SSCertifiable Points: 5275 More actions · Answer 5

Following are the key differnces between Stored procedure and UDF.

1.User defined Function returns only one value; It may be the one value of table data type if ur using table valued function.But stored procedure can return more than one values if you use OUTPUT paramers with procedure.

2.You can use UDF(scalar/table valued) in the select statement as a record source. You can not use stored procedure result set as a record source in select statement directly.

3.UDF can return only one result set at a time.While stored procedure can return more than one result sets.

Vidyadhar