Userdefind functions and StredProcedure

Posted by Bravi under Sql Server on 9/26/2009 | Views : 1040 | Status : [Member] | Replies : 3
We can use userdefind functions in query like select *from dbo.functionname

but we can't use sps like select * from spName ..

why ..if we can ..how or we can't why ....

Thanks&Regards,

B Ravi.




Responses

Posted by: Vuyiswamb on: 9/28/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Bravi

These are Two Different Things look at this

http://www.go4expert.com/forums/showthread.php?t=329


Thank you for Posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Bravi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bravi on: 9/28/2009 [Member] Starter

Up
0
Down
I did not get the following statment ....


UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

Could you tell me why please more focus on it

Sps also return values using out parameter right.. that is my question..

Thanks&Regards,
B Ravi


Bravi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/28/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down

Good Day Bravi

Bravi Wrote:
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
)
as
SET @Val = (SELECT ID FROM MYTABLE
WHERE ID = 3)
IF @Val = null
return = 0
else
select @Val


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

Vuyiswa Maseko


Thank you for posting at Dotnetfunda
[Administrator]

Bravi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response