Diff b/w Srored Procedure & Functions

  • hi all

    i m new to sql server.

    i want to know the basic diff b/w stored proc. and functions.

    both take arguments and both can return values.

    plz tell me

  • 30,000 ft view:

    Functions are typically used to return values. For instance, I can create a scalar function and use it as part of a SELECT query. Functions cannot, however, modify data outside of the function (meaning I can't modify any tables).

    Stored procedure do return values, but they can't be used in a SELECT query in the same way a function can. Also, a stored procedure can modify data outside of the stored procedure (meaning I could update a table).

    K. Brian Kelley
    @kbriankelley

  • Likewise,

    1.A stored procedure can't return a table whereas a function can.

    2. We can't use 'Print' statement in functions whereas in S.P we can.

    these are 2 of the plenties..

  • Just wondering is there anything that you write as a function but cannot re-written in SP ???

  • On similar line I have one question.

    There is some stored procedure which has some SELECT statement so when we execute that (EXEC usp) it displays result but my problem is I want to Insert the returned rows in another table so how do I do that??

    As some body in earlier discussion said I cant use USP with Select on it.

    So please let me know how to do it??

    Please note that call for this USP is from some usp only..

  • Another difference.

    I think one cannot use SP_executesql frm within a function. The last time i tried to use it, i hit a brick wall.


    What I hear I forget, what I see I remember, what I do I understand

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

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