Tags

, ,


Following procedure: 
 

CREATE PROCEDURE dbo.fakeProcedure 
    @foo INT = 5, 
    @bar DATETIME = GETDATE, 
    @dec DECIMAL(19,2), 
    @x TEXT, 
    @vc VARCHAR(255) OUTPUT 
AS 
BEGIN 
    DECLARE @ret INT 
    IF @foo > 10 
        SET @ret = 10 – @foo 
    ELSE 
        SET @ret = 10 + @foo 
    RETURN @ret 
END 
GO

 
Now, you’re planning on calling this stored procedure in your ASP code or in an application, and you forget the parameter names and/or their datatypes. 
 
Well, the first thing you could do is look at the second resultset of sp_help: 
 

EXEC sp_help ‘fakeProcedure’

 
However, this resultset does not differentiate between INPUT / OUTPUT parameters, nor does it show default values. 
 
There are a couple of built-in stored procedures that go into much greater detail about each parameter: 
 

EXEC sp_sproc_columns ‘fakeProcedure’ 
 
–or the undocumented/unsupported: 
 
EXEC sp_procedure_params_rowset ‘fakeProcedure’

 
These give far more information than necessary, and still don’t show default values for applicable params. 
 
You can also look at the syscolumns table: 
 

SELECT * FROM syscolumns 
    WHERE id = OBJECT_ID(‘fakeProcedure’)

 
Again, default values are not returned. 
 
Finally, you could create your own procedure using the PARAMETERS view under INFORMATION_SCHEMA, to give you a much more concise result: 
 

CREATE PROCEDURE dbo.listProcParams 
    @proc_name SYSNAME 
AS 
BEGIN 
    SELECT  
        [Param] = COALESCE(PARAMETER_NAME, ‘<no params>’), 
        [DataType] = COALESCE(UPPER(DATA_TYPE) + CASE 
            WHEN DATA_TYPE IN (‘NUMERIC’, ‘DECIMAL’) THEN  
                ‘(‘ + CAST(NUMERIC_PRECISION AS VARCHAR)  
                + ‘, ‘ + CAST(NUMERIC_SCALE AS VARCHAR) + ‘)’  
            WHEN RIGHT(DATA_TYPE, 4) = ‘CHAR’ THEN 
                ‘(‘ + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ‘)’ 
            ELSE ” END + CASE PARAMETER_MODE  
            WHEN ‘INOUT’ THEN ‘ OUTPUT’ ELSE ‘ ‘ END, ‘-‘) 
    FROM  
        INFORMATION_SCHEMA.PARAMETERS 
    WHERE 
        SPECIFIC_NAME = @proc_name 
    ORDER BY  
        ORDINAL_POSITION 
END 
GO 
 
EXEC dbo.listProcParams ‘fakeProcedure’

 
Result: 
 

Param  DataType 
—— ———————- 
@foo   INT  
@bar   DATETIME  
@dec   DECIMAL(19, 2)  
@x     TEXT  
@vc    VARCHAR(255) OUTPUT

 
Now, you’ll notice if you run the following… 
 

SELECT * 
    FROM INFORMATION_SCHEMA.PARAMETERS 
    WHERE SPECIFIC_NAME=’fakeProcedure’

 
…that you *still* can’t determine the default values. The sad truth is that these are simply not stored by SQL Server, they are merely evaluated by the text of the procedure at runtime. So, to determine default values, you’re going to have to visually inspect the actual SP code, using sp_helptext or the ROUTINES view under INFORMATION_SCHEMA: 
 

EXEC sp_helptext ‘fakeProcedure’ 
 
–or 
 
SELECT ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_NAME = ‘fakeProcedure’
Advertisements