Capturing SQL Server Stored Procedure Arguments

Background

One of the few and fewer Software magazines that still come in paper form is Oracle Magazine.  I love reading it, as it is Thorough.

In the latest issue (Nov/Dec 2014) , Steven Feuerstein has a very important article on defensive programming in PL/SQL.

The article is available online @ http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64plsql-2349451.html.

 

Steven Feuerstein

Steven touched on a few ideas that applies to all programming languages.  His list of things to look for includes:

  • Assignment of value in the declare section
  • Use of global variables in modules
  • Argument Values validity checks
  • Instrumentation/Tracing

 

Preface

In our little post, we will see one of the ways we can capture the input parameters to a SQL Server Stored Procedure.

To do so, there are a couple of Transact SQL Language tool-sets we will use; and they are:

  • Table value functions; we will Table Value Parameters (TVP) as a collection object.  It serves similar roles to Collections (Array, Lists) in other programming languages.  And, it is useful as we can bag data, in our case input Stored Procedure arguments, and send them off to a function and have that function marshall our collection into a single XML Object
  • Exception Handling – Try/Catch

Code

 Table Value Parameter – tvpProgrammableParameterType

Create Table Value parameter

 


/*
    Create type
*/
if not exists
    (
        select schema_name(schema_id) as schemaName, *
        from   sys.table_types
        where  schema_id = schema_id('dbo')
        and    name = 'tvpProgrammableObjectParameterType'

    )
begin

    CREATE TYPE [dbo].[tvpProgrammableObjectParameterType] AS TABLE
    (
          [Name]     sysname
        , [Value]    nvarchar(4000)

        PRIMARY KEY
            (
                  [Name]
            )
    )

end

GO

 

 Scaler Function – [dbo].[udf_GetParameterXML]

The function below produces represented data for the Table Value Parameter passed in.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
    drop function [dbo].[udf_GetParameterXML]
*/
if object_id('[dbo].[udf_GetParameterXML]') is null
begin

	exec('create function [dbo].[udf_GetParameterXML]() returns  nvarchar(4000) as begin return null end  ')

end
go

ALTER FUNCTION [dbo].[udf_GetParameterXML]
(
      @arg [dbo].[tvpProgrammableObjectParameterType] READONLY
)
returns nvarchar(4000)
as
begin

	declare @xml nvarchar(4000)

	set @xml =
		(

			SELECT  TAG , Parent , [doc!1!dummy!hide] , [Parameter!2!Name!element], [Parameter!2!Value!element]
						FROM
						(
							SELECT
									1 AS TAG ,
									NULL AS Parent ,
									1 AS [doc!1!dummy!hide] ,
									NULL AS [Parameter!2!Name!element] ,
									NULL AS [Parameter!2!Value!element]
							UNION
							SELECT
									2 AS TAG ,
									1 AS Parent ,
									NULL AS [doc!1!dummy!hide] ,
									ISNULL([Name],'') AS [Parameter!2!Name!element] ,
									ISNULL(cast([Value] as nvarchar(4000)),'') AS [Parameter!2!Value!element]
							FROM @arg

						)	T

			  FOR XML EXPLICIT	

		) 

	return @xml
end

go

 

Test Cases

Test Case #1

Let us have a test case that I promise you will fail, but it is nonetheless useful as it exposes a stringent Transact SQL principle.

 

Stored Procedure – dbo.usp_CaptureInputArguments


if object_id('dbo.usp_CaptureInputArguments') is null
begin

    exec('create procedure dbo.usp_CaptureInputArguments as print ''shell'' ')

end
go

alter procedure dbo.usp_CaptureInputArguments
(
	  @arg1 int null
	, @arg2 nvarchar(400) null
	, @arg3 datetime null
)
as
begin

    set nocount on;

	declare @argList [dbo].[tvpProgrammableObjectParameterType]
	declare @argListASXML XML

	begin try

		insert into @argList
		(
			[name], [value]
		)
		values
		  ('arg1', @arg1)
		, ('arg2', @arg2)
		, ('arg3', @arg3)

		set @argListASXML = [dbo].[usp_GetParameterXML](@argList)

		--create exception/trap
		print 1/0

	end try
	begin catch

        print 'problem occurred!'

		SELECT
					ERROR_NUMBER() as 'Error Number'
					, ERROR_SEVERITY() as 'Error Severity'
					, ERROR_STATE() as 'Error State'
					, ERROR_PROCEDURE() as 'Error Procedure'
					, ERROR_LINE() as 'Error Line'
					, ERROR_MESSAGE() as 'Error Message'
					, @argListASXML as [Argument List]

	end catch

end

go

 

Test Case

 


	declare @arg1 int
	declare	@arg2 varchar(30)
	declare @arg3 datetime 

	select
	  @arg1 = 10
	, @arg2 = 'sam'
	, @arg3 = getdate()

	exec  dbo.usp_CaptureInputArguments

			  @arg1 = @arg1
			, @arg2 = @arg2
			, @arg3 = @arg3

 

The output is pasted below:

failWithConversionError

 

Our Stored Procedure failed, and the capture failure is:

Title Link
 Error Number  241
 Error Severity  16
 Error State  1
 Error Procedure  usp_CaptureInputArguments
 Error Line  19
 Error Message  Conversion failed when converting date and/or time from character string.

 

 

The reason for the error message “Conversion failed when converting date and/or time from character string” is that our arguments contains the various data types (varchar/int/datetime).
And, SQL Server needs help marshaling it.

 

Test Case #2

To correct, we have to resort to explicit conversion.

In this corrected module, we use Transact SQL cast function to convert the arguments that are not strings.


if object_id('dbo.usp_CaptureInputArgumentsCorrected') is null
begin

    exec('create procedure dbo.usp_CaptureInputArgumentsCorrected as print ''shell'' ')

end
go

alter procedure dbo.usp_CaptureInputArgumentsCorrected
(
	  @arg1 int null
	, @arg2 nvarchar(400) null
	, @arg3 datetime null
)
as
begin

    set nocount on;

	declare @argList [dbo].[tvpProgrammableObjectParameterType]
	declare @argListASXML XML

	begin try

		insert into @argList
		(
			[name], [value]
		)
		values
		  ('arg1', cast(@arg1 as varchar)) -- int casted to varchar(30)
		, ('arg2', @arg2) -- nvarchar left as is
		, ('arg3', cast(@arg3 as varchar)) -- datetime casted to varchar(30)

		set @argListASXML = [dbo].[udf_GetParameterXML](@argList)

		print 1/0 

	end try
	begin catch

        print 'problem occurred!'

		SELECT
					ERROR_NUMBER() as 'Error Number'
					, ERROR_SEVERITY() as 'Error Severity'
					, ERROR_STATE() as 'Error State'
					, ERROR_PROCEDURE() as 'Error Procedure'
					, ERROR_LINE() as 'Error Line'
					, ERROR_MESSAGE() as 'Error Message'
					, @argListASXML as [Argument List]

	end catch

end

go

 

Test Case

 


	declare @arg1 int
	declare	@arg2 varchar(30)
	declare @arg3 datetime 

	select
	  @arg1 = 10
	, @arg2 = 'sam'
	, @arg3 = getdate()

	exec  dbo.usp_CaptureInputArgumentsCorrected

			  @arg1 = @arg1
			, @arg2 = @arg2
			, @arg3 = @arg3

 

failWithDivideByZeroError

 

Title Link
 Error Number  8134
 Error Severity  16
 Error State  1
 Error Procedure  usp_CaptureInputArgumentsCorrected
 Error Line  32
 Error Message  Divide by zero error encountered.

 

 

 

Please per-use the reference section to get more information about datatype precedence.

 

Quotes

I have been listening to a forceful repudiation lately.  No one likes forceful arguments, but the guy has a sense of humor, as well.

And, he also drops names and knowledge.

Here he goes invoking David:

More to be desired are they than gold,
even much fine gold;
sweeter also than honey
and drippings of the honeycomb.

Moreover, by them is your servant warned;
in keeping them there is great reward.

 

 

Reference

Datatype Precedence

 

XML/For XML

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s