SQL server stored procedure return a table

Question

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

User · Answer

Consider creating a function which can return a table and be used in a query.

https://msdn.microsoft.com/en-us/library/ms186755.aspx

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
END

Then 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",'')<>'' UNION

User · Answer

I 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   END

User · Answer

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 END   Execution of Stored Procedure    If you want to test the execution of Stored Procedure in SQL   EXEC usp ValidateUSer  UserName  admin    Password  admin    Th Output

User · Answer

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    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

User · Answer

You 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 GO

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I 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  lt   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 GO

[sql] SQL server stored procedure return a table

Examples related to sql

Examples related to sql-server

Examples related to matlab

Examples related to stored-procedures