Prev: Stored Procedure - Writeline Where
Next: Many to many table design question
From: m on 11 Mar 2010 09:05
Hello,

Does anybody know, MSSQL 2005 T SQL language, are variables or keywords case
sensitive or not?

Regards


From: Plamen Ratchev on 11 Mar 2010 10:37
No, keywords and variables are no case sensitive (but CLR method names are).

--
Plamen Ratchev
http://www.SQLStudio.com
From: m on 11 Mar 2010 10:59
What is wrong with this code
Let's look at @sec variable
You can see upper case S in the @Sec variable in the body of function
And lower case s at @sec input variable
When I tried to compile this I got error

Msg 137, Level 15, State 1, Procedure test, Line 8

Must declare the scalar variable "@Sec".

Msg 137, Level 15, State 2, Procedure test, Line 10

Must declare the scalar variable "@Sec".



After changing @sec into @Sec everything works fine

I don't get it?!
Can you explain this

drop function [dbo].[test]

Go

create function [dbo].[test](@sec integer)

returns varchar(20)

as

begin

declare @str varchar(16)

Set @Sec = 100

Set @str = Convert(varchar(16), @Sec)

return @str

end





"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:jI-dnXlr9sTZkwTWnZ2dnUVZ_uudnZ2d(a)speakeasy.net...
> No, keywords and variables are no case sensitive (but CLR method names
> are).
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 11 Mar 2010 11:07
My statement was incorrect about variables. Since they follow the rules for identifiers, they can be case sensitive
depending on the collation. Here is from BOL:

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such
as logins and database names, are assigned the default collation of the instance. Identifiers of objects in a database,
such as tables, views, and column names, are assigned the default collation of the database. For example, two tables
with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be
created in a database that has case-insensitive collation.

--
Plamen Ratchev
http://www.SQLStudio.com
From: m on 11 Mar 2010 14:38
Thank you very much.

Now I have problem.

I have database A and database B

I made full backup of db A (example function "test" works properly >
Insensitive collation)
And restore that backup in db B.

In database B example "test" function doesn't work properly!

I thought if I make full backup of db A and restore it in db B, db B must
have exact collation like db A?!

I suppose there isn't easy way to covert database collation?

Thank you in advance.

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:jI-dnXpr9sTciATWnZ2dnUVZ_usAAAAA(a)speakeasy.net...
> My statement was incorrect about variables. Since they follow the rules
> for identifiers, they can be case sensitive depending on the collation.
> Here is from BOL:
>
> The collation of an identifier depends on the level at which it is
> defined. Identifiers of instance-level objects, such as logins and
> database names, are assigned the default collation of the instance.
> Identifiers of objects in a database, such as tables, views, and column
> names, are assigned the default collation of the database. For example,
> two tables with names that differ only in case can be created in a
> database that has case-sensitive collation, but cannot be created in a
> database that has case-insensitive collation.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 |  Next  |  Last
Pages: 1 2
Prev: Stored Procedure - Writeline Where
Next: Many to many table design question