Why we use scalar valued function in SQL [closed]

It's difficult to tell what开发者_如何学Go is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

If performance of Scalar valued function or any user defined function is slower than SP's Why we need to go through User defined function rather to go with simple SP's?

---

Because you can call a scalar valued function from inside a select query such as.

select f1(name), f2(id) from users

which is not possible in case of SPs

---

What you use depends on the requirements.

Functions (including scalar ones) can be used within queries and return a set of different types while stored procedures can only return an integer value and cannot be used within other queries.

create function dbo.scalarfunc () returns varchar(20) as
begin
    declare @somevalue varchar(20)
    set @somevalue = (select top 1 name from master.sys.tables)
    return @somevalue

end
go

select * from sys.tables
where name = dbo.scalarfunc()

with an SP

create procedure dbo.someproc as 
begin
    declare @somevalue varchar(20)
    set @somevalue = (select top 1 name from master.sys.tables)
    return @somevalue
end
go

declare @somespret varchar(20)
exec @somespret = dbo.someproc

this causes the return to be NULL and the following message

'The 'someproc' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.'

And a proc returning an integer

create procedure dbo.someproc as 
begin
    declare @somevalue int
    set @somevalue = (select top 1 object_id from master.sys.tables)
    return @somevalue
end
go

declare @somespret int
exec @somespret = dbo.someproc
select @somespret

This works but using it in a query

select * from sys.tables where name = dbo.someproc()

results in the message

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.someproc", or the name is ambiguous

---

UDF could introduce serious performance problems when it used in JOIN or WHERE clause and query returns big amount of data

SELECT A.*, B.*
FROM A
INNER JOIN B ON B.id = A.id AND B.ExtraCode = calculateExtraCode(A.Name)

so for each selected row UDF will be called and problem is in invocation latency introduced by UDF call. it simply can be tested by extracting UDF body inline of SP or query, difference will be easily observed.

But if query returns small amount of data it makes sense to separate logic into UDF so code of the main query/SP will be more clear.