Click here to Skip to main content
Click here to Skip to main content

Figure Out the Default Values of Stored Procedure Parameters

, , 2 Feb 2006
Rate this:
Please Sign up or sign in to vote.
When working with Microsoft SQL JDBC driver, you cannot invoke a stored procedure without specifying all the parameters, including the optional parameters that have default values. Learn how to fix that problem here.

Introduction

When you are migrating a .NET application that uses ADO.NET to a Java-based one that uses JDBC, you will generally have to add code to add all the stored procedure parameters into the SqlCommand object, as the SQL JDBC doesn't allow you to 'ignore' parameters that have default values. In this article, you will see how you can derive all the parameters from a stored procedure using some T-SQL and C# code so that you can call your stored procedures and automatically have the default parameters assigned with their default values.

As SQL Server does not store the default values of stored procedure parameters in its system tables, and instead evaluates the text of the procedure at runtime, you lose some flexibility when writing applications that use T-SQL stored procedures. This problem can be worked around with a little bit of C# and some T-SQL. Get started using this T-SQL stored procedure called _GetAllProcedures:

CREATE PROCEDURE _GetAllProcedures 
AS 
SET NOCOUNT ON 
select sysobjects.name,syscolumns.name from sysobjects, syscolumns 
where 
sysobjects.xtype='P' and 
sysobjects.id = syscolumns.id 
RETURN 
GO

The C# code that will use this stored procedure to get all your stored procedures and evaluate them for their parameters is pretty straightforward. First, use two connections to the database – I will call these the 'main' connection, and the 'parameter' connection:

SqlConnection conSP = new SqlConnection(ConnectionString); 
SqlConnection conSPParamValue = 
    new SqlConnection(ConnectionString); 
conSP.Open(); 
conSPParamValue.Open();

Next, set up a SqlCommand on the main connection and initialize it with the stored procedure that you are interested in using:

SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP); 
cmdSP.CommandType = CommandType.StoredProcedure;

Next, the code will loop through the results of this stored procedure and ignore all of the system stored procedures that begin with the prefix 'dt_'. Trivia – 'dt' stands for 'DaVinci Tools' and the early code name for the technology used here to auto generate code when using the Enteprise Manager or design tools, so you could say that you've truly found the 'DaVinci Code'.

When the procedure isn't a 'dt_' one, you then create a new command on the 'params' connection to the '_GetParamDefault' stored procedure, which as its name suggests will get the default parameter for you:

string ParamDefaultValue = ""; 
SqlCommand cmdSPParamValue = 
    new SqlCommand("_GetParamDefault", conSPParamValue); 

cmdSPParamValue.CommandType = CommandType.StoredProcedure;

The code for this stored procedure is here:

CREATE proc _GetParamDefault
@Procname varchar(50),
@ProcParamName varchar(50),
@DefaultValue varchar(100) OUTPUT
as
/*
This procedure will return DEFAULT value for 
the parameter in thestored procedure.
Usage:
declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',
                                            @value OUTPUT
SELECT @VALUE

*****************************************************
Author: Eva Zadoyen
Edited: Rafael Mizrahi
*/

set nocount on
declare @sqlstr nvarchar(4000),
@obj_id int,
@version int,
@text varchar(8000),
@startPos int,
@endPos int,
@ParmDefinition NVARCHAR(500)
select @procName = rtrim(ltrim(@procname))
set @startPos= charindex(';',@Procname)

if @startPos<>0
begin
set @version = substring(@procname,@startPos +1,1)
set @procname = left(@procname,len(@procname)-2)
end
else
set @version = 1

SET @sqlstr =N'SELECT @text_OUT = (SELECT text FROM syscomments
WHERE id = object_id(@p_name) and colid=1 and number = @vers)'
SET @ParmDefinition = N'@p_name varchar(50),
@ParamName varchar (50),
@vers int,
@text_OUT varchar(4000) OUTPUT'

EXEC sp_executesql
@SQLStr,
@ParmDefinition,
@p_name = @procname,
@ParamName = @ProcParamName,
@vers = @version,
@text_OUT =@text OUTPUT

--select @TEXT
select @startPos = PATINDEX( '%' + @ProcParamName +'%',@text)
if @startPos<>0
begin
select @text = RIGHT ( @text, len(@text)-(@startPos +1))
select @endPos= CHARINDEX(char(10),@text) -- find the end of a line
select @text = LEFT(@text,@endPos-1)
-- check if there is a default assigned and 
-- parse the value to theoutput
select @startPos= PATINDEX('%=%',@text)
if @startPos <>0
begin
select @DefaultValue = 
     ltrim(rtrim(right(@text,len(@text)-(@startPos))))
select @endPos= CHARINDEX('--',@DefaultValue)
if @endPos <> 0
select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))

select @endPos= CHARINDEX(',',@DefaultValue)
if @endPos <> 0
select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))
end
ELSE
select @DefaultValue = 'NO DEFAULT SPECIFIED'
end
else
SET @DefaultValue = 'INVALID PARAM NAME'

set nocount off
return
GO

You then add parameters to this command, which are derived from the 'main' connection. These are the name of the procedure that you are currently inspecting and its parameter:

cmdSPParamValue.Parameters.Add("@Procname",
                             myReader.GetString(0));
cmdSPParamValue.Parameters.Add("@ProcParamName", 
                             myReader.GetString(1));

Finally, you specify the output type and execute the query:

SqlParameter param = cmdSPParamValue.Parameters.Add(
                    "@DefaultValue", SqlDbType.VarChar,100);
param.Direction = ParameterDirection.Output;
cmdSPParamValue.ExecuteNonQuery(); 
ParamDefaultValue = param.Value.ToString();

You now have the default value for the parameter, and you can use this in future calls to the stored procedure quite happily!

The full source code for this routine, in C# is shown below:

// open two connections 
string ConnectionString = "Data Source=localhost;Initial 
                           Catalog=Northwind;user id=sa"; 
SqlConnection conSP = new SqlConnection(ConnectionString); 
SqlConnection conSPParamValue = new SqlConnection(ConnectionString); 
conSP.Open(); 
conSPParamValue.Open(); 

// get all stored procedures and parameters 
SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP); 
cmdSP.CommandType = CommandType.StoredProcedure; 

// loop on all stored procecdures and parameters 
SqlDataReader myReader = cmdSP.ExecuteReader(); 
while (myReader.Read()) 
{ 
  if (myReader.GetString(0).ToLower().StartsWith("dt_") != true) 
  { 

    string ParamDefaultValue = ""; 
    SqlCommand cmdSPParamValue = new SqlCommand(
                         "_GetParamDefault", conSPParamValue); 
    cmdSPParamValue.CommandType = CommandType.StoredProcedure; 
    cmdSPParamValue.Parameters.Add("@Procname",
                               myReader.GetString(0)); 
    cmdSPParamValue.Parameters.Add(
             "@ProcParamName", myReader.GetString(1)); 

    SqlParameter param = cmdSPParamValue.Parameters.Add(
                         "@DefaultValue", SqlDbType.VarChar,100); 

    param.Direction = ParameterDirection.Output; 

    cmdSPParamValue.ExecuteNonQuery(); 

    ParamDefaultValue = param.Value.ToString(); 

    if (ParamDefaultValue != "NO DEFAULT SPECIFIED") 
    { 
      Console.WriteLine("{0}, {1}, {2}", myReader.GetString(0),    
                         myReader.GetString(1), ParamDefaultValue); 
    } 
  } 
} 
myReader.Close(); 
conSP.Close(); 
conSPParamValue.Close();

In this KB you have gained a new tool for your development toolbox: an automatic way of iterating through all the stored procedures in your database and extracting a list of their parameters that are declared with default values. With this list, you can then go through your code and find out where the stored procedures are used, and make sure that the SqlCommand is filled with all the parameters, including the optional ones.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Authors

Rafael Mizrahi
Chief Technology Officer Feng-GUI
United States United States
Rafael Mizrahi is a CTO at Feng-GUI
 
He is also a hacker contributor at the mono project, and a GarageGeeks member.
 
Rafael Mizrahi personal blog -
http://rafaelmizrahi.blogspot.com

Laurence Moroney is the Director of Product Evangelism for Mainsoft. He joined Mainsoft from Reuters, where he was a Senior Architect in their CTO Office, specializing in Enterprise Architecture and Web Services Interoperability. Laurence has also worked extensively in the financial services and security fields. He is the author of several computer books, including ‘Expert Web Services Security in the .NET Platform’ and ‘ASP.NET 1.1 with VB.NET’ as well as several dozen technology articles.
 
You can find his blog at: philotic.com/blog

Comments and Discussions

 
Bug2 Bugs PinmemberKalpesh Desai9-Apr-12 0:57 
GeneralMy vote of 5 PinmemberKalpesh Desai8-Apr-12 21:38 
AnswerI took a different approach, let sql server parse the parameters and return me out the results. Pinmembergokhan_varol25-Sep-11 12:55 
This is what I did to get it. grab the section of the stored procedure starting with the first parameter up to the AS statement. Created a temporary stored procedure with the declare statements and returning union all of all the parameter ids, names, column types, if they have default, and their value. And then executed the stored procedure with assumption of if there is a equal sign between parameters they have default, and if they don't have default I passed null to the parameter during execution, and either read the resultset or if exists the stored procedure populated a temporary table so that I can query it later. I checked if there is any equal signs between parameters and if yes initially I assumed they have defaults. If there is a comment etc with equal sign the procedure that means they did not have default and during execution I did not pass any parameter, the execution failed, I caught the error message, read the parameter name and executed the procedure this time I passed null to the parameter. In the procedure I used a CLR string concat function, for that reason it won't compile if you execute directly, but you can probably replace with XML path or so, or email me back I can guide you through the clr if you want to. Since I did union all the parameters I casted them as varchar(max)
<pre lang="SQL">
 

USE Util
GO
CREATE AGGREGATE [dbo].[StringConcat]
(@Value nvarchar(MAX), @Delimiter nvarchar(100))
RETURNS nvarchar(MAX)
EXTERNAL NAME [UtilClr].[UtilClr.Concat]
GO
CREATE FUNCTION dbo.GetColumnType (@TypeName SYSNAME,
                                  @MaxLength SMALLINT,
                                  @Precision TINYINT,
                                  @Scale TINYINT,
                                  @Collation SYSNAME,
                                  @DBCollation SYSNAME)
RETURNS TABLE
    AS
RETURN
    SELECT  CAST(CASE WHEN @TypeName IN ('char', 'varchar')
                      THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
                                                  ELSE CAST(@MaxLength AS VARCHAR)
                                             END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
                                                              ELSE ''
                                                         END
                      WHEN @TypeName IN ('nchar', 'nvarchar')
                      THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
                                                  ELSE CAST(@MaxLength / 2 AS VARCHAR)
                                             END + ')' + CASE WHEN @Collation <> @DBCollation THEN ' COLLATE ' + @Collation
                                                              ELSE ''
                                                         END
                      WHEN @TypeName IN ('binary', 'varbinary') THEN @TypeName + '(' + CASE WHEN @MaxLength = -1 THEN 'MAX'
                                                                                            ELSE CAST(@MaxLength AS VARCHAR)
                                                                                       END + ')'
                      WHEN @TypeName IN ('bigint', 'int', 'smallint', 'tinyint') THEN @TypeName
                      WHEN @TypeName IN ('datetime2', 'time', 'datetimeoffset') THEN @TypeName + '(' + CAST (@Scale AS VARCHAR) + ')'
                      WHEN @TypeName IN ('numeric', 'decimal') THEN @TypeName + '(' + CAST(@Precision AS VARCHAR) + ', ' + CAST(@Scale AS VARCHAR) + ')'
                      ELSE @TypeName
                 END AS VARCHAR(256)) AS ColumnType
GO
go
USE [master]
GO
IF OBJECT_ID('dbo.sp_ParamDefault') IS NULL 
    EXEC('CREATE PROCEDURE dbo.sp_ParamDefault AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_ParamDefault
    @ProcName SYSNAME = NULL OUTPUT
AS 
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
DECLARE @SQL VARCHAR(MAX),
    @ObjectId INT = OBJECT_ID(LTRIM(RTRIM(@ProcName))),
    @FirstParam VARCHAR(256),
    @LastParam VARCHAR(256),
    @SelValues VARCHAR(MAX),
    @ExecString VARCHAR(MAX),
    @WhiteSpace VARCHAR(10) = '[' + CHAR(10) + CHAR(13) + CHAR(9) + CHAR(32) + ']',
    @TableExists BIT = ABS(SIGN(ISNULL(OBJECT_ID('tempdb..#sp_ParamDefault'), 0))),
    @DeclareSQL VARCHAR(MAX),
    @ErrorId INT,
    @ErrorStr VARCHAR(MAX)
 
IF @ObjectId IS NULL 
    BEGIN
        SET @ProcName = NULL
        PRINT '/* -- SILENCE OPERATION --
IF OBJECT_ID(''tempdb..#sp_ParamDefault'') IS NOT NULL DROP TABLE #sp_ParamDefault
CREATE TABLE #sp_ParamDefault (Id INT, NAME VARCHAR(256), TYPE VARCHAR(256), HasDefault BIT, IsOutput BIT, VALUE VARCHAR(MAX))
*/
 
EXEC dbo.sp_ParamDefault
    @ProcName = NULL
'
RETURN
    END
 
SELECT  @SQL = definition,
        @ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectId)),
        @FirstParam = FirstParam,
        @LastParam = LastParam
FROM    sys.all_sql_modules m (NOLOCK)
CROSS APPLY (SELECT MAX(CASE WHEN p.parameter_id = 1 THEN p.name
                        END) AS FirstParam,
                    Util.dbo.StringConcat(p.name, '%') AS Params
             FROM   sys.parameters p (NOLOCK)
             WHERE  p.object_id = m.OBJECT_ID) p
CROSS APPLY (SELECT TOP 1
                    p.NAME AS LastParam
             FROM   sys.parameters p (NOLOCK)
             WHERE  p.object_id = m.OBJECT_ID
             ORDER BY parameter_id DESC) l
WHERE   m.object_id = @ObjectId
IF @FirstParam IS NULL 
    BEGIN
        IF @TableExists = 0 
            SELECT  CAST(NULL AS INT) AS Id,
                    CAST(NULL AS VARCHAR(256)) AS Name,
                    CAST(NULL AS VARCHAR(256)) AS Type,
                    CAST(NULL AS BIT) AS HasDefault,
                    CAST(NULL AS VARCHAR(MAX)) AS VALUE
            WHERE   1 = 2
        RETURN
    END
 
SELECT  @DeclareSQL = SUBSTRING(@SQL, 1, lst + AsFnd + 2) + '
'
FROM    (SELECT PATINDEX ('%' + @WhiteSpace + @LastParam + @WhiteSpace + '%', @SQL) AS Lst) l
CROSS APPLY (SELECT SUBSTRING (@SQL, lst, LEN (@SQL)) AS SQL2) s2
CROSS APPLY (SELECT PATINDEX ('%' + @WhiteSpace + 'AS' + @WhiteSpace + '%', SQL2)  AS AsFnd) af
 

DECLARE @ParamTable TABLE (Id INT NOT NULL,
                           NAME SYSNAME NULL,
                           TYPE VARCHAR(256) NULL,
                           HasDefault BIGINT NULL,
                           IsOutput BIT NOT NULL,
                           TypeName SYSNAME NOT NULL) ;
WITH    pr
          AS (SELECT    p.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS ParameterName,
                        p.Parameter_id,
                        t.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeName,
                        ct.ColumnType,
                        MAX(Parameter_id) OVER (PARTITION BY (SELECT 0)) AS MaxParam,
                        p.is_output
              FROM      sys.parameters p (NOLOCK)
              INNER JOIN sys.types t (NOLOCK) ON t.user_type_id = p.user_type_id
              INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
              CROSS APPLY Util.dbo.GetColumnType(t.name, p.max_length, p.precision, p.scale, db.collation_name, db.collation_name) ct
              WHERE     OBJECT_ID = @ObjectId)
    INSERT  @ParamTable
            (Id,
             NAME,
             TYPE,
             HasDefault,
             IsOutput,
             TypeName)
            SELECT  Parameter_id AS Id,
                    ParameterName AS NAME,
                    ColumnType AS TYPE,
                    HasDefault,
                    is_output AS IsOutput,
                    TypeName
            FROM    pr a
            CROSS APPLY (SELECT ISNULL('%' + (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b WHERE b.parameter_id < a.parameter_id), '') + '%'
                                + ParameterName + '%=' + '%' + CASE WHEN parameter_id = MaxParam THEN @WhiteSpace + 'AS' + @WhiteSpace + '%'
                                                                    ELSE (SELECT Util.dbo.StringConcat (ParameterName, '%') FROM pr b
                                                                                    WHERE b.parameter_id > a.parameter_id) + '%'
                                                               END AS ptt) b
            CROSS APPLY (SELECT SIGN (PATINDEX (ptt, @DeclareSQL)) AS HasDefault) hd
 
AGAIN:
SELECT  @SelValues = CASE WHEN @TableExists = 1 THEN 'INSERT #sp_ParamDefault(Id, Name, Type, HasDefault, IsOutput, Value)
'                         ELSE ''
                     END + 'SELECT * FROM (VALUES' + Util.dbo.StringConcat('(' + CAST(Id AS VARCHAR) + ', ''' + Name + ''', ''' + Type + ''', '
                                                                           + CAST(HasDefault AS VARCHAR) + ', ' + CAST(IsOutput AS VARCHAR) + ', '
                                                                           + CASE WHEN TypeName NOT LIKE '%char%' THEN 'CAST(' + name + ' AS VARCHAR(MAX))'
                                                                                  ELSE name
                                                                             END + ')', ',
') + '
) d(Id, Name, Type, HasDefault, IsOutput, Value)',
        @ExecString = 'EXEC #sp_ParamDefaultProc
' + ISNULL(Util.dbo.StringConcat(CASE WHEN HasDefault = 0 THEN Name + ' = NULL'
                                 END, ',
'), '')
FROM    @ParamTable
 
SET @SQL = 'CREATE PROCEDURE #sp_ParamDefaultProc
' + SUBSTRING(@DeclareSQL, CHARINDEX(@FirstParam, @DeclareSQL), LEN(@DeclareSQL)) + '
' + @SelValues
 
IF OBJECT_ID('TEMPDB..#sp_ParamDefaultProc') IS NOT NULL 
    DROP PROCEDURE #sp_ParamDefaultProc
EXEC(@SQL)
 
BEGIN TRY
    EXEC(@ExecString)
END TRY
BEGIN CATCH
    SELECT  @ErrorStr = ERROR_MESSAGE(),
            @ErrorId = ERROR_NUMBER()
-- there must have been a comment containing equal sign between parameters
    UPDATE  p
    SET     HasDefault = 0
    FROM    (SELECT PATINDEX ('%expects parameter ''@%', @ErrorStr) AS ii) i
    CROSS APPLY (SELECT CHARINDEX ('''', @ErrorStr, ii + 20) AS uu) u
    INNER JOIN @ParamTable p ON p.name = SUBSTRING(@ErrorStr, ii + 19, uu - ii - 19)
    WHERE   ii > 0
 
    IF @@ROWCOUNT > 0 
        GOTO AGAIN
 
    RAISERROR(@ErrorStr, 16, 1)
    RETURN 30
END CATCH
GO
EXEC sys.sp_MS_marksystemobject 
    sp_ParamDefault
GO

GeneralMore caveats Pinmembertimschultze2-Apr-07 2:34 
GeneralRe: More caveats - Params on Single Line work for me PinmemberThe Corner Analyst27-Jun-07 11:25 
GeneralPerhaps obvious but encryption messes this up Pinmembertimschultze30-Mar-07 8:17 
GeneralEva Zadoyen PinmemberJohnDeHope33-Feb-06 1:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 3 Feb 2006
Article Copyright 2006 by Rafael Mizrahi, Laurence Moroney
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid