Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Must declare the scalar variable "@name". SQL server 2005 Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2008 11:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2008 1:32 AM
Points: 4, Visits: 4
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

Post #496866
Posted Thursday, May 8, 2008 12:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
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')


- Deepak
Post #496872
Posted Thursday, May 8, 2008 12:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #496873
Posted Thursday, May 8, 2008 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2008 1:32 AM
Points: 4, Visits: 4
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
Post #496942
Posted Thursday, May 8, 2008 2:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
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
Post #496947
Posted Saturday, May 10, 2008 12:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2008 1:32 AM
Points: 4, Visits: 4
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
Post #498331
Posted Monday, May 12, 2008 6:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 16, 2011 11:36 PM
Points: 11, Visits: 133

Couldn't you just go to database properties > options and select the right collation?
Post #499234
Posted Monday, May 12, 2008 6:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
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.

- Deepak
Post #499236
Posted Tuesday, May 13, 2008 1:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2008 1:32 AM
Points: 4, Visits: 4
Yes Alter/ or modifying can be done ,
But not to System DataBases
Post #499365
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse