Menu
I have a stored procedure that takes in two parameters. I can execute it successfully in Server Management Studio. It shows me the results which are as I expect. However it also returns a Return Value.
It has added this line,
SELECT 'Return Value' = @return_value I would like the stored procedure to return the table it shows me in the results not the return value as I am calling this stored procedure from MATLAB and all it returns is true or false.
Do I need to specify in my stored procedure what it should return? If so how do I specify a table of 4 columns (varchar(10), float, float, float)?
671
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!
CREATE TABLE #StudentData_Log (ID INT, Name VARCHAR(100)) SELECT * FROM #StudentData_Log; Lets execute the stored procedure and insert output into above temp table. Lets check the temp table, and you can see the stored procedure output is inserted in table.
In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement.
You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.
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:
create procedure p_x as begin declare @t table(col1 varchar(10), col2 float, col3 float, col4 float) insert @t values('a', 1,1,1) insert @t values('b', 2,2,2) select * from @t end go declare @t table(col1 varchar(10), col2 float, col3 float, col4 float) insert @t exec p_x select * from @t 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
