Contents
Can a SP return a table?
Every stored procedure can return an integer value known as the execution status value or return code. If you still want a table returned from the SP, you’ll either have to work the record set returned from a SELECT within the SP or tie into an OUTPUT variable that passes an XML datatype.
Can you run a stored procedure in a transaction?
Yes, everything that you do between the Begin Transaction and Commit (or Rollback) is part of the transaction.
What is commit in stored procedure?
The behavior of a stored procedure depends on whether it is called as a singleton SQL statement or within a SQL transaction block. When a stored procedure is called as a singleton SQL statement, the changes made by the stored procedure are committed when the procedure exits or rolled back if the procedure aborts.
Can a stored procedure be run inside a transaction?
Yes, a stored procedure can be run inside a transaction. Please find below a sample query. From SQL Server (not sure about other RDBMS), You can call multiple stored procedures inside a transaction. EDIT: To check a return code you can do something like the following. This will run the first stored proc.
Can a procedure return a table in SQL?
A procedure can’t return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this: I recommend seeing this solution as well.
Can a stored procedure return an int value?
The Status Value being returned by a Stored Procedure can only be an INT datatype. You cannot return other datatypes in the RETURN statement. From Lesson 2: Designing Stored Procedures: Every stored procedure can return an integer value known as the execution status value or return code.
When is the Transact-SQL statement compiled in SP _ ExecuteSQL?
The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.