September 29, 2010 at 11:59 pm
Hi All,
I have a Stored Procedure that excepts 2 values. Below is an example:
Exec CleanUpSP 123345696, 0x4C8A2BCB
I've got a number of combinations that need to run against the Stored Procedure so I created a second table with 2 columns and imported all the combinations into it. The first field is Account and of type int. The second file is Archive and I set the type as nvarchar (12)
I then create a loop to fetch each combination and run it in the Stored Procedure. This however failed with the following error : Implicit conversion from data type nvarchar to int is not allowed.
I know why...the Stored Procedure is created with both those values as int data type.
My question is...for my table that stores these combinations...what should the data type be for the Archive field or how do I convert that value into an int that the Stored Procedure can except.
Thanks
Denesh
September 30, 2010 at 12:02 am
Apologies...the error is :Procedure CleanUpSP , Line 0 Error converting data type nvarchar to int and not Implicit conversion from data type nvarchar to int is not allowed.
September 30, 2010 at 5:40 am
It depends on the data. Based on the stuff you provided, '0x4C8A2BCB' is absolutely not an integer, a whole number without decimal points, which is what the int data type is. So, yeah, it would always have to be referred to as a string data type, varchar, nvarchar, char, nchar, text, ntext (the 'n' denotes unicode).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 5:56 am
Grant Fritchey (9/30/2010)
It depends on the data. Based on the stuff you provided, '0x4C8A2BCB' is absolutely not an integer, a whole number without decimal points, which is what the int data type is. So, yeah, it would always have to be referred to as a string data type, varchar, nvarchar, char, nchar, text, ntext (the 'n' denotes unicode).
Not entirely accurate. Its a hexadecimal representation of an integer and as long as you dont convert it to a string it shouldnt be a problem. (And the exampelcode at the start doesnt have any ')
create procedure S (@a int, @b-2 int) as
go
exec s 1, 0x4C8A2BCB
go
drop procedure s
Gives
1
1284123595
1284123596.
So since the second parameter in his procedure is a nvarchar then changing it to an int should make it better. Shouldnt need to have it as an varchar.
/T
September 30, 2010 at 6:18 am
tommyh (9/30/2010)
Grant Fritchey (9/30/2010)
It depends on the data. Based on the stuff you provided, '0x4C8A2BCB' is absolutely not an integer, a whole number without decimal points, which is what the int data type is. So, yeah, it would always have to be referred to as a string data type, varchar, nvarchar, char, nchar, text, ntext (the 'n' denotes unicode).Not entirely accurate. Its a hexadecimal representation of an integer and as long as you dont convert it to a string it shouldnt be a problem. (And the exampelcode at the start doesnt have any ')
create procedure S (@a int, @b-2 int) as
go
exec s 1, 0x4C8A2BCB
go
drop procedure s
Gives
1
1284123595
1284123596.
So since the second parameter in his procedure is a nvarchar then changing it to an int should make it better. Shouldnt need to have it as an varchar.
/T
Must not have enough coffee. I just saw characters. Good catch.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 6:20 am
Grant Fritchey (9/30/2010)
Must not have enough coffee. I just saw characters. Good catch.
Mmmmm coffee 😀
October 4, 2010 at 2:16 am
Thanks all for the replies.
Would I be right in saying when I created the table to store the different combinations...I should have created that field as a varbinary
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply