Good Day Bravi
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
This is because of the Following Reasons.
If you have an Ouput StoredProcedure , you need to declare the Parameters that you want to pass to the Stored Procedure and execute them , but in UDF the parameters does not need to be explicitly declared. This means i can do this
Declare @Value int
set @Value = 3
select * from mytable
where ID = (dbo.My_UDF(@Value));
but in a SP
Create Proc Check_Value
@Val INT OUTPUT
SET @Val = (SELECT ID FROM MYTABLE
WHERE ID = 3)
IF @Val = null
return = 0
Now to call this Sp you will have to do it like this
declare @Val int
execute Check_Value @Val output
now as you can see in the manner we execute the SP , you need to give the name as declared inside the StoredProcedure else you will get an Exception that the Parameter was not Supplied. but when i used a UDF i did not need to declare the Variable explicitly.
I hope you understand
Thank you for Posting at dotnetfunda
Bravi, if this helps please login to Mark As Answer. | Alert Moderator