How to get type of parameter passed in stored procedure?

Hi ,

I wrote a stored procedure with one uinqueidentifier parameter.Depending upon the value passed during execution type of parameter passed is changing.

Example:

CREATE PROCEDURE ABC_Proc

@.ABC uniqueidentifier = NULL

Execution:

EXEc ABC_Proc ' '

If I pass the uiqueidentifier from the same table then the program executes properly.If same uinqueidentifier from other table or NULL or '' is passed the following error is displayed

"conversion failed when converting from a character string to unique identifier."

Questions:IS there a way to get the datatype of parameter passed in the querry?so, that I can modify accordingly.

Thanks,

Kodela.

Hi Kodela,

The easiest way is to use the new sql_variant datatype as the parameter, and then within the procedure, you can test for the base datatype and execute the appropriate action:

declare @.v sql_variant

set @.v = 123

if SQL_VARIANT_PROPERTY ( @.v , 'BaseType' ) = 'int'
begin
print 'int type passed'
end
else if SQL_VARIANT_PROPERTY ( @.v ,'BaseType' ) = 'uniqueidentifier'
begin
print 'uid type passed'
end

Cheers,
Rob

|||

You could use sql_variant, but the best thing to do is to treat parameters as strongly typed and only pass proper values to that parameter. You can get the type of the parameter from the information_schema:

CREATE PROCEDURE ABC_Proc
@.ABC uniqueidentifier = NULL
as
select @.ABC
go

select parameter_name, data_type
from information_schema.parameters
where specific_name = 'ABC_Proc'
and specific_schema = 'dbo'

|||

Robert Varga wrote:

use the new sql_variant datatype

Robert,

There is a reason that the variant datatype has been removed from Visual Studio.Net -it opens the code to potential attack vectors. Similarly, it is not a particularly good thing to use in SQL Server. Often the use of variants demonstrates a poor design, or just plain sloppy coding. -NOT drawing aspersions to you or your techniques, just commenting...

Regards,

|||

Hi Arnie,

I have to say I disagree completely. The SQL_VARIANT datatype was introduced in SQL 2005 for good reason. Just as there are valid reasons for using cursors, there are valid reasons for using the sql_variant datatype, and blanket statements alluding to their use constituting "poor design" are flawed.

In reality, we've been using varchar's as "variant" datatypes for years - ( CONVERT(varchar(15), GETDATE(), 103) anyone? ) and the new datatype removes precision related errors.

Cheeers,
Rob

|||

I actually have to agree that the SQL_VARIANT datatype should generally be avoided (as should converting dates to varchar values too, since that is usually a formatting gimmick that we *should* let the presentation layer handle :) for the same reason that VB programmers shouldn't generally use Option Strict (I think that was (is?) the syntax).

Especially in this situation, picking the proper datatype is part of the contract between caller and callee. In your example, it would not be possible to tell a GUID from a character value, certainly not when being passed in as a parameter, since it would be passed as a string of characters (or as a binary value) which could not be distinguished in code.

It is not completely useless when used for sparse property tables where you define the metadata of the column at runtime is a pretty good use, but generally having a column of unknown type means more code, more work, and greater chance for errors later.

|||

Hi Louis,

Sure; just as liberal use of, say, cursors is not a good thing, neither is the use of generic datatypes. My point is that there are circumstances in which the use of sql_variant (or cursors) are valid and should not be discounted by zealots simply because some Wrox book said they were "bad".

Cheers,
Rob

|||

Louis,

You demonstrate my point precisely. There is no need to use a sql_variant in this situation. The metadata is available. Strongly typed variables/parameters reduce coding and coding errors.

Using an object datatype (the .NET replacement for 'variant' has it's place and needs, and slq_variant has it's places and usages. However, in my opinion, it should not be used in a situation where the exact need does not exist. This is not one of those situations.

And gosh, if one wanted to morph a varchar() parameter, the datatype could be just another input parameter. For me, strongly typing is the key. If the dataype is known when the call is made, it should be strongly typed.