Menu
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.
Exec is not allowed in functions, but it is allowed in stored procedures, so you can just rewrite the function as a stored procedure which retuns a resultset.
The RETURN exits the stored procedure, and nothing that follows it will be executed, including the SELECT statement on the following line. Otherwise, if you want the data for the entire table, as your question shows, add a SELECT after the INSERT . But don't put RETURN in front of it!
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');
declare @rowcount int = (select top (1) [rowcount] from @result);
declare @nReturn int = 0 EXEC @nReturn = Stored Procedures
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With