locked
find datatypes from stored procedure RRS feed

  • Question

  • I would like to know the datatypes of the resultset from a stored procedure. How can i do that? I am trying to dump the resultset into a temp table and i need to know the datatypes of the resultset from the stored procedure

    Eagerly waiting for your replies


    Thanks
    Tuesday, May 17, 2011 2:25 PM

All replies

  • Hello,

    A stored procedure can return different results depending on parameter; e.g. like  sp_help. Even SSMS don't show the result rowset and the datatypes. 


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Naomi N Tuesday, May 17, 2011 11:47 PM
    • Unproposed as answer by Kalman Toth Wednesday, May 18, 2011 1:46 PM
    Tuesday, May 17, 2011 2:36 PM
  • when we can find the datatypes from a table
    why can't we find the datatypes from a stored proc
    there must be a way to do that?

     


    Thanks
    Tuesday, May 17, 2011 2:43 PM
  • If you only need to know the datatypes so that it matches your temp table datatypes for insertion, you can use the following select syntax:

    SELECT * INTO #TempTables FROM Table

    This will create a temp table with the data types from the columns data types

    Tuesday, May 17, 2011 2:57 PM
  • The data types of the columns in a table are well defined. In contrast, in a stored procedure, I can write any query I like. I can do something like this:

     

    If DATEPART(second, CURRENT_TIMESTAMP) < 30
     SELECT CAST(1 AS int) AS my_number
    ELSE
     SELECT CURRENT_TIMESTAMP AS my_date, 'Hello' AS my_comment
    
    

    This is to illustrate that there is not "one" data type definition of the resultset. Also, there is no way to automatically determine the data types even if you only have one query in the SP.

     


    Gert-Jan
    Tuesday, May 17, 2011 8:49 PM
  • You can get the metadata for stored procedure SELECT return the following way:
     
    -- Create table with SELECT INTO and OPENQUERY
    SELECT * INTO SPWHO FROM OPENQUERY(HPESTAR,'EXEC sp_who')
    -- (33 row(s) affected)
    
    -- Get table meta data
    SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, 
      CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='SPWHO'
    /* spid	1	smallint	NULL
    ecid	2	smallint	NULL
    status	3	nchar	30
    loginame	4	nvarchar	128
    hostname	5	nchar	128
    blk	6	char	5
    dbname	7	nvarchar	128
    cmd	8	nchar	16
    request_id	9	int	NULL
    */
    

    As noted above, the assumption is a single return set and no duplicate column names like in sp_who2. Related articles:

    http://www.sqlusa.com/bestpractices2005/selectfromsproc/

    http://www.sqlusa.com/bestpractices2008/information-schema-views/

     


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Wednesday, May 18, 2011 1:59 PM
  • As noted above, the assumption is a single return set and no duplicate column names like in sp_who2. Related articles:


    - If it returns a single resultset
    - If it returns a resultset at all
    - If column names are unique
    - If the resultset is deterministic
    - If ....

    A lot of if's, presumptions and exceptions when it possibly could work. What e.g. does this SP returns for a type; something between char(1) and char(998), depending on execution time (and I known, this SP makes no sense):

    USE [TempDB];

    GO

     

    CREATE PROCEDURE spVarCol

    AS

    BEGIN

        DECLARE @sql nvarchar(100);

        SET @sql = N'SELECT CONVERT(char(' + CONVERT(varchar(4)

                                                    ,(DATEPART(ms, GetDate()) + 1))

                                           + N'), 1) AS VarCol'

        EXEC sp_executesql @sql;

    END

    GO

     

    EXEC spVarCol;

    GO

     

    DROP PROCEDURE spVarCol;

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Thursday, May 19, 2011 6:09 AM
  • Hallo hpjack,

    all gave perfect answers so one more from me ;-)

    SELECT	o.name,
    	p.name,
    	t.name		AS [datatype],
    	p.precision,
    	p.max_length,
    	p.is_output
    FROM	sys.all_objects AS o INNER JOIN sys.all_parameters p
    	ON	(o.object_id = p.object_id) INNER JOIN sys.types t
    	ON	(p.system_type_id = t.system_type_id)
    WHERE	o.object_id = OBJECT_ID('schema.proc')
    ORDER BY
    	p.parameter_id
    

    This solution will have ONE definitive problem concerning the datatype.
    (sysname and nvarchar have both the same system_type_id!)

    Maybe someone here will find a workaround for this ;-)


    Uwe Ricken

    MCIT Database Administrator 2005
    MCIT Database Administrator 2008
    MCTS SQL Server 2005
    MCTS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Thursday, May 19, 2011 6:26 AM
  • Uwe, I think the question is to get the datatype of columns in resultset.

    Your query gives information about the parameters, which can also retrived by INFORMATION_SCHEMA.PARAMETERS view.


    ~Manoj (manub22@gmail.com)
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Thursday, May 19, 2011 6:42 AM
  • Uwe, I think the question is to get the datatype of columns in resultset.~Manoj (manub22@gmail.com)


    Manu,

    yes - you are right. I did missunderstand the question.
    Thank yo for the hint.


    Uwe Ricken

    MCIT Database Administrator 2005
    MCIT Database Administrator 2008
    MCTS SQL Server 2005
    MCTS SQL Server 2008, Implementation and Maintenance
    db Berater GmbH
    http://www-db-berater.de
    Thursday, May 19, 2011 6:46 AM
  • An alternate to OPENQUERY:

    1. Run the SSIS Import/Export Wizard: http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

    2. For Data Source Query specify the sproc execution like: EXEC sp_who

    3. You can run the package and examine the meta data of the output table or examine the metadata - Column Mappings - right in the Wizard:


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    Thursday, May 19, 2011 8:27 AM
  • A few years back i wrote a C# .NET utiliy to achieve exactly this. Following are the steps you need to do inorder to acomplish this.

    1) Write a stored procedure usp_ReturnSpParameters. This sp should take in the SP name (@spName) and return a list of parameters.

    SELECT *

    FROM sys.all_parameters p

    where object_id = object_id(@spName)

    2) Create a C# .NET application

     2.1) execute the above stored proceudre using .NET code.

     2.2) using the list of parameters returned from step 2.1 construct a sql string  like

            usp_StoreProcedureName @parameter1=null, @parameter2=null, @parameter3=null

     2.3) execute the above query and fill a DataTable. 

     2.4) Now inspect the column names and datatype of the dynamically created DataTable this should give you the return type columnnames as well as their data types.

    Cheers! & Good Luck

    • Proposed as answer by Naomi N Friday, May 20, 2011 3:51 AM
    Thursday, May 19, 2011 10:18 AM