Must declare the scalar variable "@name". SQL server 2005

Question

Must declare the scalar variable "@name". SQL server 2005

pinnamanenihari

Old Hand

Points: 350
More actions
May 7, 2008 at 11:43 pm

#129058

THis is hw my Stored proc goes
Original
ALTER procedure [stpGetlist]
(
@Name varchar(255),
)
As
Declare @sName varchar(1000)
Declare @count bigint
select @sName = replace(replace(replace(@name,'.', '%'), '-', '%'), ',', '%')
In above decalration i had made like @Name
but when i am using in below i used like @name
and it gave the Error like+
Must declare the scalar variable "@name".
If i change the above into
select @sName = replace(replace(replace(@Name,'.', '%'), '-', '%'), ',', '%')
the stored proc is executing fine
can any one please suggest on this

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 1

I think Sql Server is having a case-sensitive collation and hence the above error message. You can confirm this by runnning,

sp_helpsort (or) SELECT SERVERPROPERTY ('Collation')

[font="Verdana"]- Deepak[/font]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

The only error i found was in your parameter declaration.

"@Name varchar(255)," . No need of a comma if its the only parameter there.

Check your server,db collation.

"Keep Trying"

pinnamanenihari Old Hand Points: 350 More actions · Answer 3

hI AFTER RUNNING

select serverproperty ('Collation') THIS I GOT THE RESULT AS

SQL_Latin1_General_CP437_BIN

IS THERE ANY WAY WE CAN ALTER THIS

rajankjohn SSCertifiable Points: 5061 More actions · Answer 4

for changing on a single database, you can use the ALTER DATABASE command. For changing the entire instance, you may need to rebuild the master database.

http://rajanjohn.blogspot.com

pinnamanenihari Old Hand Points: 350 More actions · Answer 5

Finnaly the Problem was solved by rebuilding.

while installing the Sql- Server it will asl of Collation "Case- Insensitive", this was to be set corectly. To avoid the Issues mentioned above.

Thanks guys

du.pereira Mr or Mrs. 500 Points: 591 More actions · Answer 6

Couldn't you just go to database properties > options and select the right collation?

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 7

In order to change the server level collation you need to rebuild the system databases. For changing database collation you can make use of alter database statement.

[font="Verdana"]- Deepak[/font]

pinnamanenihari Old Hand Points: 350 More actions · Answer 8

Yes Alter/ or modifying can be done ,

But not to System DataBases