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_valueI 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)?
9 Answers
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 @tI do this frequently using Table Types to ensure more consistency and simplify code. You can't technically return "a table", but you can return a result set and using INSERT INTO .. EXEC ... syntax, you can clearly call a PROC and store the results into a table type. In the following example I'm actually passing a table into a PROC along with another param I need to add logic, then I'm effectively "returning a table" and can then work with that as a table variable.
/****** Check if my table type and/or proc exists and drop them ******/
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'returnTableTypeData')
DROP PROCEDURE returnTableTypeData
GO
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'myTableType')
DROP TYPE myTableType
GO
/****** Create the type that I'll pass into the proc and return from it ******/
CREATE TYPE [dbo].[myTableType] AS TABLE( [someInt] [int] NULL, [somenVarChar] [nvarchar](100) NULL
)
GO
CREATE PROC returnTableTypeData @someInputInt INT, @myInputTable myTableType READONLY --Must be readonly because
AS
BEGIN --Return the subset of data consistent with the type SELECT * FROM @myInputTable WHERE someInt < @someInputInt
END
GO
DECLARE @myInputTableOrig myTableType
DECLARE @myUpdatedTable myTableType
INSERT INTO @myInputTableOrig ( someInt,somenVarChar )
VALUES ( 0, N'Value 0' ), ( 1, N'Value 1' ), ( 2, N'Value 2' )
INSERT INTO @myUpdatedTable EXEC returnTableTypeData @someInputInt=1, @myInputTable=@myInputTableOrig
SELECT * FROM @myUpdatedTable
DROP PROCEDURE returnTableTypeData
GO
DROP TYPE myTableType
GOConsider creating a function which can return a table and be used in a query.
The main difference between a function and a procedure is that a function makes no changes to any table. It only returns a value.
In this example I'm creating a query to give me the counts of all the columns in a given table which aren't null or empty.
There are probably many ways to clean this up. But it illustrates a function well.
USE Northwind
CREATE FUNCTION usp_listFields(@schema VARCHAR(50), @table VARCHAR(50))
RETURNS @query TABLE ( FieldName VARCHAR(255) )
BEGIN INSERT @query SELECT 'SELECT ''' + @table+'~'+RTRIM(COLUMN_NAME)+'~''+CONVERT(VARCHAR, COUNT(*)) '+ 'FROM '+@schema+'.'+@table+' '+ ' WHERE isnull("'+RTRIM(COLUMN_NAME)+'",'''')<>'''' UNION' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table and TABLE_SCHEMA = @schema RETURN
ENDThen executing the function with
SELECT * FROM usp_listFields('Employees')produces a number of rows like:
SELECT 'Employees~EmployeeID~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees WHERE isnull("EmployeeID",'')<>'' UNION
SELECT 'Employees~LastName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees WHERE isnull("LastName",'')<>'' UNION
SELECT 'Employees~FirstName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees WHERE isnull("FirstName",'')<>'' UNIONYou can use an out parameter instead of the return value if you want both a result set and a return value
CREATE PROCEDURE proc_name
@param int out
AS
BEGIN SET @param = value
SELECT ... FROM [Table] WHERE Condition
END
GOI had a similar situation and solved by using a temp table inside the procedure, with the same fields being returned by the original Stored Procedure:
CREATE PROCEDURE mynewstoredprocedure
AS
BEGIN
INSERT INTO temptable (field1, field2)
EXEC mystoredprocedure @param1, @param2
select field1, field2 from temptable
-- (mystoredprocedure returns field1, field2)
ENDThe 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.
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.
HTH,
John
create procedure PSaleCForms
as
begin
declare
@b varchar(9),
@c nvarchar(500),
@q nvarchar(max)
declare @T table(FY nvarchar(9),Qtr int,title nvarchar (max),invoicenumber nvarchar(max),invoicedate datetime,sp decimal 18,2),grandtotal decimal(18,2))
declare @data cursor
set @data= Cursor
forward_only static
for
select x.DBTitle,y.CurrentFinancialYear from [Accounts Manager].dbo.DBManager x inner join [Accounts Manager].dbo.Accounts y on y.DBID=x.DBID where x.cfy=1
open @data
fetch next from @data
into @c,@b
while @@FETCH_STATUS=0
begin
set @q=N'Select '''+@b+''' [fy], case cast(month(i.invoicedate)/3.1 as int) when 0 then 4 else cast(month(i.invoicedate)/3.1 as int) end [Qtr], l.title,i.invoicenumber,i.invoicedate,i.sp,i.grandtotal from ['+@c+'].dbo.invoicemain i inner join ['+@c+'].dbo.ledgermain l on l.ledgerid=i.ledgerid where (sp=0 or stocktype=''x'') and invoicetype=''DS'''insert into @T exec [master].dbo.sp_executesql @q fetch next from @data into @c,@b end close @data deallocate @data select * from @T return end
Though this question is very old but as a new in Software Development I can't stop my self to share what I have learnt :D
Creation of Stored Procedure:
CREAET PROC usp_ValidateUSer
( @UserName nVARCHAR(50), @Password nVARCHAR(50)
)
AS
BEGIN IF EXISTS(SELECT '#' FROM Users WHERE Username=@UserName AND Password=@Password) BEGIN SELECT u.UserId, u.Username, r.UserRole FROM Users u INNER JOIN UserRoles r ON u.UserRoleId=r.UserRoleId END
ENDExecution of Stored Procedure:
(If you want to test the execution of Stored Procedure in SQL)
EXEC usp_ValidateUSer @UserName='admin', @Password='admin'Th Output:
Here's an example of a SP that both returns a table and a return value. I don't know if you need the return the "Return Value" and I have no idea about MATLAB and what it requires.
CREATE PROCEDURE test
AS
BEGIN SELECT * FROM sys.databases RETURN 27
END
--Use this to test
DECLARE @returnval int
EXEC @returnval = test
SELECT @returnval
