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.

How to get SQL Server Management Studio to automatically connect upon startup?

Hi all,
2000's Enterprise Manager was smart enough to automatically connect to my
last database when it was launched.
Somehow I can't find out how to turn on this extremely useful feature in
2005's Management Studio. Can anyone tell me the secret?
Thanks,
C17
> 2000's Enterprise Manager was smart enough to automatically connect to my
> last database when it was launched.
> Somehow I can't find out how to turn on this extremely useful feature in
> 2005's Management Studio. Can anyone tell me the secret?
None of these things are "secrets." You can add command line parameters and
create shortcuts for different servers. Here are the possible startup
parameters for Management Studio (sqlwb.exe):
Usage:
sqlwb.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P
password] [-E] [file_name[, file_name]] [/?]
[-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information
However, this won't automatically open Object Explorer. (Same was true in
Query Analyzer, IIRC.)
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Thank you, that works great!!
It's still annoying that one can't set a default via the UI, though. From
reading the Feedback pages, and the MSFT responses, it seems as though
everyone at MSFT thinks that we're all using Management Studio from inside
of VS, and so the response to a lot of these usability requests is
basically,
"Well, you can do that from inside of VS." I think they forget that a lot
of us use it for management, not development!
Thanks again,
C17
|||You maybe are misunderstanding the posts from Microsoft. You
don't use Management Studio from inside Visual Studio. You
use Management Studio OR Visual Studio.
What posts are you referring too? You are always free to
offer product suggestions.
-Sue
On Thu, 14 Jun 2007 14:41:07 -0400, "C17"
<c17@.community.nospam> wrote:

>Thank you, that works great!!
>It's still annoying that one can't set a default via the UI, though. From
>reading the Feedback pages, and the MSFT responses, it seems as though
>everyone at MSFT thinks that we're all using Management Studio from inside
>of VS, and so the response to a lot of these usability requests is
>basically,
>"Well, you can do that from inside of VS." I think they forget that a lot
>of us use it for management, not development!
>Thanks again,
>C17
>

How to get ID into field upon insert (SCOPE_IDENTITY() ?)

I have two fields:

ID - primary key and identity field (seed 1 increment 1)
GroupID - int

Upon INSERT I want groupID to be the value of ID

The only way I can figure out how to do this (being inexperienced with sql) is to INSERT with GroupID = 0 and then do a SELECT @.iGroupID=SCOPE_IDENTITY() and then UPDATE the record. But even inside a transaction this seems error prone and not the right way. Can anyone tell me the correct way to do this. Thank you in advance.You can try using a trigger.

CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[GroupID] [int] NOT NULL ,
[AnotherColumn] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TRIGGER [InsertTrigger] ON [dbo].[Table1]
AFTER INSERT
AS
declare theCursor cursor for SELECT ID FROM inserted
open theCursor
declare @.ThisID int
select @.ThisID = -1
while 1 = 1
begin
fetch next from theCursor into @.ThisID
if @.@.fetch_status <> 0
 break
Update Table1
Set GroupID = @.ThisID
WHERE ID=@.ThisID
end
close theCursor
deallocate theCursor

|||I hope you are using SQL Server 2000 ... If that being the case we can use UDFs to accomblish this task ... A typical code snippet for the same looks like below:

CREATE FUNCTION dbo.GetIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('vin_test'))
END

CREATE TABLE vin_test
(
colAINT IDENTITY(1,1) NOT NULL,
colBINT DEFAULT dbo.GetIdentity(),
colCVARCHAR(10)
)

INSERT INTO vin_test (colC) VALUES ('Test')
SELECT * FROM vin_test|||Yes the ISP is server 2000 and my MSDE seems to support functions but prompts me to select from Inline, Table-values, Scalar-valued, can you please tell me which to select. Also I have about 20 tables I need to do this with. Do I need to write a function for each table?

THANKS!|||Yes, we need to create one for each table. I am sure it is better than creating 10 triggers ... :)

Select the scalar-valued function ...|||My final question:

Is the UDF better/safer/more stable/reliable then what I am doing in the stored procedure where inside a transaction I insert, get the scope_identity and then update the just inserted record. I understand my (hack/kludge?) method may take more server processing but I besides that I am wondering if it is any less reliable then the UDF method. (last question :) again, thanks.|||I can say it with certain that UDFs are quite stable ... There is no second thoughts on that statement. We have used this in our production code and have tested the same with concurrent users ... Works like a gem ... :) ...

Hmm ... the second part of the question has a couple of interesting points to backup:
1. Keep the transaction short. Which means if you have a big routine to do the same it is quite not advisible.
2. CPU cycles are not cheaper and so is memory. If you were to use SPs in such a fashion then we are using both ... :) ... And I dont recommend the same strongly ...

I know this is a delicate topic to answer and can raise a couple of eye-brows who are reading this ... But these are my views on the same. I am open to hear from others if they think otherwise ...|||stored procedures return only integers...UDF can return other datatypes like float...etc
other than that i guess both are similar...in terms of security/performance...etc|||Hmm ... Stored Procedures can also return more than one value using the OUTPUT parameter types ... And I feel that we can do things in UDF that are quite not possible with Stored Procedures ... Read my article on UDF at :http://www.extremeexperts.com/sql/articles/UDFFunctions.aspx ...

And for the present problem description ... I feel UDF is a cleaner and neater way rather than the conventional UDF approach ...