Click here to Skip to main content
Click here to Skip to main content
Search within:



 
Comments & Discussions (7)

Figure Out the Default Values of Stored Procedure Parameters

By , , 2 Feb 2006
 

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

About the Authors

 Rafael Mizrahi
Chief Technology Officer Feng-GUI
United States United States
Member
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
Belgium Belgium
Member
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

Article Top
 
Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board.
Search this forum  
    Spacing  Noise  Layout  Per page   
First Prev Next
Bug2 BugsmemberKalpesh Desai9 Apr '12 - 0:57 
Article is very good. Objective of this note is just to make solution more accurate, So please take it positively.
 
It's having couple of issues identified as follows.
 
#1) e.g. Consider SP parameters sequence is as follows and try to find default value of '@P' parameter.
.....
@P11 varchar(100) = 'p3',
@P1 varchar(100) = 'p2',
@P varchar(100) = 'p1',....
 
Here it returns the result = 'p3' which is incorrect.
 
#2) e.g. Consider SP parameter statement written in worst forms as explained below.
...
@P
varchar(100) = 'p1'
...
 
OR
 
...
@P varchar(100)
=
'p1'
...
 
----------------------------------------------------
I have corrected both issues and redesigned it with our coding conventions and readable remarks.
 
----------------------------------------------------
 
CREATE PROCEDURE DU_CG_spGetSPParameterDefault_Private
(
	@SPName			varchar(100),
	@SPParameterName	varchar(100),
 
	@ProcessingStatus	int Out, -->  1 = Default value found. 
					 -->  0 = SP doesn't exist.
					 --> -1 = 'INVALID PARAMETER NAME'
					 --> -2 = 'NO DEFAULT IS SPECIFIED'
	@DefaultValue 		varchar(100) Out
)
As
Begin
	/*
	This procedure will return DEFAULT value for the parameter in the stored procedure.
	Author: Eva Zadoyen
	Edited: Rafael Mizrahi
	*/
 
	---------------------------------------
	--> Extract SPName and Version if available as ';' separated.
	Set @SPName = LTrim(RTrim(@SPName))
	Declare @Version int
 
	Declare @StartPosition int
	Set @StartPosition = CharIndex(';', @SPName)
	If @StartPosition > 0 --> ';' exists inside.
	Begin
		Set @Version = Substring(@SPName, @StartPosition + 1, 1)
		Set @SPName = Left(@SPName, Len(@SPName) - 2)
	End
	Else
		Set @Version = 1
 
	---------------------------------------
	--> Test weather SP exists or not.
	Declare @ObjectID int
	Set @ObjectID = Object_ID(@SPName)
	If @ObjectID Is Null
	Begin
		Set @ProcessingStatus = 0 --> SP doesn't exist.
		Print 'Requested SP ''' + @SPName + ''' doesn''t exist.'
		Return
	End
 
	---------------------------------------

	--> Extract SP Definition + Body
	Set @DefaultValue = Null
 
--	Set NoCount On
	Declare @SQL nvarchar(4000)
	Declare @Text varchar(8000)
	Declare @ParameterDefinition nvarchar(500)
	
	Set @SQL = N'Select @Text_OUT = Text ' +
		    'From   SysComments ' +
		    'Where  ID = @Object_ID And ColID = 1 And Number = @Vers'
	
	Set @ParameterDefinition = N'@Object_ID int,
					@Vers int,
					@Text_OUT varchar(4000) Out'
	
	Exec sp_executesql @SQL, @ParameterDefinition,
				@Object_ID = @ObjectID,
				@Vers = @Version,
				@Text_OUT = @Text Out
	
	--Select @Text
	---------------------------------------

	--> Parse Parameter and it's definition.
	--> Need to find the nearest matched pattern with any combination of Space/Tab/CRLF characcters.
	--> Searching without combination character becomes bug (Case explained below) and returns inaccurate result.
	--> e.g. Consider SP parameters sequence is as follows and try to find default value of '@P' parameter.
	--> @P11	varchar(100) = 'p3',
	--> @P1		varchar(100) = 'p2',
	--> @P		varchar(100) = 'p1',

	Declare @IndexOfParamWithSpace int
	Set @IndexOfParamWithSpace = PatIndex('%' + @SPParameterName + Char(32) + '%', @Text) --> 32 = Space

	Declare @IndexOfParamWithTab int
	Set @IndexOfParamWithTab = PatIndex('%' + @SPParameterName + Char(9) + '%', @Text) --> 9 = Tab
	
	Declare @IndexOfParamWithCRLF int
	Set @IndexOfParamWithCRLF = PatIndex('%' + @SPParameterName + Char(13) + Char(10) + '%', @Text) --> 13 = CR, 10 = LF

	--> Find Min Value among above three values.
	Declare @NearestPositionIndex int
	Select @NearestPositionIndex = Min(PositionIndex)
	From
	(
		Select 	@IndexOfParamWithSpace As PositionIndex
			Union
		Select 	@IndexOfParamWithTab As PositionIndex
			Union
		Select 	@IndexOfParamWithCRLF As PositionIndex
	) As tblUnion
		Where	IsNull(tblUnion.PositionIndex, 0) <> 0
 
	Set @NearestPositionIndex = IsNull(@NearestPositionIndex, 0)	
 
	---------------------------------------
	---> Set @StartPosition = PatIndex('%' + @SPParameterName + '%', @Text)
	Set @StartPosition = @NearestPositionIndex
	If @StartPosition <= 0
	Begin
		Set @ProcessingStatus = -1 --> INVALID PARAM NAME
		Set @DefaultValue = Null
		Return
	End
 
	--> Parameter exists.
	Set @Text = Right(@Text, Len(@Text) - (@StartPosition + 1)) --> KRD-OK, Remove left part.
--	Set @EndPosition = CharIndex(Char(10), @Text) -- Find the end of a line --> KRD-NOT_OK
--	Set @Text = Left(@Text, @EndPosition - 1) --> KRD-NOT_OK

	-- Check If there is a default assigned and parse the value to theoutput
	Set @StartPosition = PatIndex('%=%', @Text) --> KRD-OK
	If @StartPosition <= 0 --> No '=' operator is found.
	Begin
		Set @ProcessingStatus = -2 --> NO DEFAULT IS SPECIFIED
		Set @DefaultValue = Null
		Return
	End
 
	--> '=' operator is found. So default value is there, Need to parse it now.
	Set @ProcessingStatus = 1 --> Default value found.
	Set @DefaultValue = dbo.DU_CG_fnLTrimNewLineTabs(LTrim(RTrim(Right(@Text, Len(@Text) - (@StartPosition))))) --> Extract value which is right side of '=' operator.

	--> Check weather any comment/remark exists or not.
	Declare @EndPosition int
	Set @EndPosition = CharIndex('--', @DefaultValue)
	If @EndPosition > 0 --> Comment/Remark is found. Need to remove that too.
		Set @DefaultValue = RTrim(Left(@DefaultValue, @EndPosition - 1))
 
	--> Check second parameter is started immediately on same line.
	Set @EndPosition= CharIndex(',', @DefaultValue) --> It's started.
	If @EndPosition > 0 --> Need to ignore that part.
		Set @DefaultValue = RTrim(Left(@DefaultValue, @EndPosition - 1))
 
	--> SEQ#Last --> Remove Tabs and New Line Characters from end if exists.
	Set @DefaultValue = dbo.DU_CG_fnRTrimNewLineTabs(@DefaultValue)
	
--	Set NoCount off
End
GO
 

----------------------------------------------------
Sign In·View Thread·Permalink
GeneralMy vote of 5memberKalpesh Desai8 Apr '12 - 21:38 
This article saved our valuable time.
 
Thanks.
Sign In·View Thread·Permalink
AnswerI took a different approach, let sql server parse the parameters and return me out the results.membergokhan_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

Sign In·View Thread·Permalink
GeneralMore caveatsmembertimschultze2 Apr '07 - 2:34 
The stored procedure for parsing the parameters assumes (I think) a certain standard format of the stored procedure "declaration." Each parameter needs to be listed on a separate line, as is done for the procedure itself in the article.
 
I happen to think that this standardized approach to declaring a stored procedure is good and, in fact, is slowly being adopted at one of my clients. There are still those old style formats out there, though, and this trips up the parsing logic, I believe.
 
There are perhaps other issues associated with parsing. For example, if I had a two parameters, one named SubDirectory followed by one named Directory, I'm not so sure the second one would ever be parsed. Granted, it seems a little unlikely that you would use one parameter whose name is a substring of the other, but it can happen in situations where you least expect it.
 
Perhaps an improvement would be to move the parsing logic into C# where there seems to be a larger text processing toolkit available to you.
Sign In·View Thread·Permalink
GeneralRe: More caveats - Params on Single Line work for mememberThe Corner Analyst27 Jun '07 - 11:25 

Quoted from above:
"Each parameter needs to be listed on a separate line, "
 
The parser works for me even when multiple parameters are on the same line.
 
Thanks Rafael Mizrahi and Laurence Moroney ... great little gadget you wrote!!!
 

The Corner Analyst
Sign In·View Thread·Permalink
GeneralPerhaps obvious but encryption messes this upmembertimschultze30 Mar '07 - 8:17 
This is just a warning. We load all of our stored procedures in encrypted mode (yes, ABAPerl would help us manage this so that development environments had unencrypted while production had encrypted versions), so the text of the stored procedure comes back as NULL and this doesn't work. It's a pity SQL Server doesn't store its default parameter values so they can be returned in COLUMN_DEF.
Sign In·View Thread·Permalink
GeneralEva ZadoyenmemberJohnDeHope33 Feb '06 - 1:50 
Eva Zadoyen is my new heroine. I have wanted to be able to do that for so long. And now I can! Thanks Rafael and Laurence for introducing me to this great sproc!
Sign In·View Thread·Permalink
Last Visit: 31 Dec '99 - 18:00     Last Update: 25 May '13 - 6:46Refresh1

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

About Article
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.
Type Article
Licence 
First Posted 2 Feb 2006
Views 45,479
Bookmarked 22 times

, +
Top News

Old Japanese Man Creates Amazing Art Using Excel (Wait, Excel?)

Get the Insider News free each morning.
Related Videos
Entity Framework: Stored Procedures Part 3
Entity Framework: Stored Procedures Part 2
Code First Stored Procedures
Overview of SQL Server Stored Procedure
Speeding up database access - Part 6: Fixing execution plan reuse
Pager Control for ASP.NET
Table Value Parameter in SQL Server 2008
Data Access Application Block .NET 2.0: Get Return Values, Output Values
How to Use Stored Procedures in VB6
How to invoke a Web Service from a Stored Procedure
WrapSP - A Wrapper Class Generator for SQL Stored Procedures
Lite ORM Library (v2)
How to execute multiple long running SQL Statements Asynchronously in small chunks
Compilers Demystified: Function Pointers in Visual Basic 6.0
Create CLR Stored Procedure with Visual Studio Express Edition
Abstract Simplified Stored Procedure Execution using .NET Interfaces
Stored Procedure Data Gateway
Passing an array or DataTable into a stored procedure
Easy Data Access
Stored Procedure Invocation Code Generator for VB, C# and JScript.NET
Class Generator for SQL Server Stored Procedures
A set of ADO classes - version 2.20
Permalink | Advertise | Privacy | Mobile
Web01 | 2.6.130523.1 | Last Updated 3 Feb 2006
Article Copyright 2006 by Rafael Mizrahi, Laurence Moroney
Everything else Copyright © CodeProject, 1999-2013
Terms of Use
Layout: fixed | fluid