6 Answers. Sorted by: 112. On the one hand you could use sp_executesql: exec sp_executesql N'select @rowcount=count(*) from anytable', . N'@rowcount int output', @rowcount output; On the other hand you could use a temporary table: declare @result table ([rowcount] int); insert into @result ([rowcount]) exec (N'select count(*) from anytable');
A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. As with output parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program.
You can retrieve a return code value with two steps. First, you need to declare a local variable, such as @return_status in the following script, to which to transfer the return code value from a stored procedure. Second, you need to assign the return code value from within the stored procedure’s exec statement to the local variable.
@CustID = 10 SELECT 'Return Value' = @return_value. . GO. When executing the stored procedure, it gives the following output. The first output gives the result of the select statement inside the [GetCustomerEmail] stored procedure and the second set returns the stored procedure return value.
1 Answer. Sorted by: 0. Procedures including sp_executesql are forbidden in a UDF. https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine. I don't see why you need dynamic here. Try. Create FUNCTION [dbo].[Fn_GetTable] ( @ID as nvarchar(MAX) ) RETURNS . @Tbl TABLE (
The return status value can be included in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but it must be entered in the following form: EXECUTE @return_status = <procedure_name>.
Return Value in SQL Server Stored Procedure. In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
SQL. Copy. USE [AdventureWorks2022] GO -- Declare a variable to return the results of the function. DECLARE @ret nvarchar(15); -- Execute the function while passing a value to the @status parameter . EXEC @ret = dbo.ufnGetSalesOrderStatusText @Status = 5; -- View the returned value.
October 4, 2000. — The procedure. drop proc exec_with_return_code. go. create proc exec_with_return_code @tsql varchar (255) as. set nocount on. create table #return (code int null) insert into #return EXEC (‘DECLARE @rc int exec @rc=’ + @tsql + ‘. select code=@rc’) declare @rc int select @rc=isnull (code,0) from #return. return @rc. go.
3 Answers. Sorted by: 5. You need to use the OUTPUT parameter, as it is described in the MSDN article: Given this dummy stored procedure: CREATE PROCEDURE sp_test AS. RETURN 2. This code will give you the result: declare @ret int.