CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-function
Mridul Thakur
Mridul Thakur

Reputation: 1

Why were functions introduced when stored procedures were already available in SQL Server?

What factors led to the introduction of functions alongside stored procedures in SQL, and how do functions specifically enhance the flexibility and efficiency of database operations compared to stored procedures?

I didn't get the exact explaination.

Upvotes: 0

Views: 128

Answers (1)

Martin Smith
Martin Smith

Reputation: 452907

A function can take parameters and return either a scalar value or a single resultset of zero or more rows and one or more columns.

This is composable. You can integrate it into your SQL statements in order to add new calculated columns or apply it for table valued functions.

Stored procedures are less restricted in the syntax allowed but are not composable.

e.g. You can use the built in function OBJECT_DEFINITION as below

SELECT *, 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures

But there is no way of calling the sp_helptext stored procedure inline in a similar manner

SELECT *, 
       EXEC sp_helptext name /*Not valid syntax*/
FROM sys.procedures

Upvotes: 4

Related Questions