locked
Functions and Procedures RRS feed

  • Question

  • Can we use all DDL, DCL & DML commands in Stored Procedure or Function ?
    • Moved by Tom Phillips Wednesday, July 20, 2011 8:07 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, July 20, 2011 6:53 PM

Answers

  • You can not use DDL, DCL or DML commands in a function in regards to regular or temp tables. You can only do this with table variables if we're talking about functions (T-SQL UDF).

    For Stored procedures you can use them, but certain operations will require dynamic SQL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Surendra Nath GM Wednesday, July 20, 2011 8:30 PM
    • Marked as answer by KJian_ Thursday, July 28, 2011 9:22 AM
    Wednesday, July 20, 2011 8:25 PM

All replies

  • You can use DML statements. However using DDL and DCL would require the usage on dynamic sql i.e. sp_executesql
    With kind regards
    Krystian Zieja
    http://www.projectenvision.com
    Follow me on twitter
    My Blog
    Need help with your systems?
    Wednesday, July 20, 2011 6:56 PM
  • You can not use DDL, DCL or DML commands in a function in regards to regular or temp tables. You can only do this with table variables if we're talking about functions (T-SQL UDF).

    For Stored procedures you can use them, but certain operations will require dynamic SQL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Surendra Nath GM Wednesday, July 20, 2011 8:30 PM
    • Marked as answer by KJian_ Thursday, July 28, 2011 9:22 AM
    Wednesday, July 20, 2011 8:25 PM
  • Did you try it before you posted this? Might want to do that or read BOL first.
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Proposed as answer by Naomi N Thursday, July 21, 2011 3:09 AM
    Thursday, July 21, 2011 12:05 AM
  • Can we use all DDL, DCL & DML commands in Stored Procedure or Function ?


    In a stored procedure, practically speaking, sky is the limit!

    In a function, you cannot change any database state in any shape or form.  That means SELECT queries and T-SQL operations like string manipulation.

    Example scripts: http://www.sqlusa.com/bestpractices2008/table-valued-parameter/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Wednesday, July 27, 2011 9:04 AM