December 17, 2009 at 12:19 pm
I have a table with a column set to NVARCHAR(100). In a simple explanation I am looking for a match on this column with data supplied to a Stored Proc.
Create Proc s_Test
@Parent varchar(50),
@Name nvarchar(100)
as
Declare @NameAudit nvarchar(100)
select @NameAudit = Name from Table Where Parent = @Parent
IF @NameAudit = @Name
BEGIN PRINT 'MATCH' END
-------------------------------------------------
I will get a match if I execute the proc with the N' prefix:
exec dbo.s_Test @Parent = 'Iraq',@Name = N'AL ANBĀR'
I will not get a match if I don't use the N' Prefix:
exec dbo.s_Test @Parent = 'Iraq',@Name = 'AL ANBĀR'
Can I convert that @Name within the proc so the N' prefix is implied?
December 17, 2009 at 1:54 pm
It should match in either case because the parameter itself is NVARCHAR so it should do an implicit conversion.
Here's a test I have done (I'm using 2005 so could be a difference):
CREATE TABLE nvarchar_testa (colA NVARCHAR(10));
GO
CREATE PROCEDURE nvarchar_test
(
@param NVARCHAR(10)
)
AS
SELECT
@param;
SELECT
*
FROM
nvarchar_testa AS NT
WHERE
colA = @param;
RETURN ;
GO
INSERT INTO nvarchar_testa (
colA
) VALUES (
N'Jêêp' );
EXEC nvarchar_test @param = N'Jêêp';
EXEC nvarchar_test @param = 'Jêêp';
DROP TABLE nvarchar_testa;
DROP PROCEDURE nvarchar_test;
And in both cases the row is returned. Can you provide table structures and test data like I have so we can see if there are any differences?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 17, 2009 at 2:08 pm
Jack,
Thanks for the reply. I thought it should convert it automatically too because I set the param @Name as an NVARCHAR but it doesn't work that way for me. Not sure why.
The column [State] is an NVARCHAR(360)
If do a simple select on the table without the N' Prefix no results are returned. Which is expected.
select * from tTable Where State = 'AL ANBAR'
(NO RESULTS)
select * from tTable Where State = N'AL ANBAR'
(RESULTS)
However my question is, Can it be converted if it is not intially passed as that datatype?
December 17, 2009 at 2:20 pm
I thought I might be able to set the value of the param to a new value with the N' prefix but I think that might require some dynamic SQL. I could do this at the begining of the procedure.
It's definitley odd... I assumed it would set it to a NVARCHAR type but the N' prefix must do something else. I'm not sure what exactly takes place when you do or don't provide that.
December 17, 2009 at 2:21 pm
I can't duplicate the issue on a 2005 box. Perhaps there is an issue with 2000 that was fixed in 2005. The only thing I can think of is that your ANSI_PADDING setting might be causing an issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 20, 2009 at 5:31 pm
By using or not using "N" you define the datatype of the string you supply to the parameter of the procedure.
If "N" is missing then you supply non-unicode string and whatever you pass is being converted to varchar with default database collation.
To see it for yourself run this simple test:
select 'AL ANBAR', N'AL ANBAR'
See the difference?
Because the conversion happens even before the value is assigned to the parameter you cannot do anything about it from inside of the procedure.
You need to fix the way you call the procedure(s) from UI.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply