handling collation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

handling collation

Author

Topic

alok_sharma14
Starting Member

1 Post

Posted - 2008-11-19 : 05:46:34

actually i have a prob..

i m using collate clause for sorting data according to the culture.
for this i m passing window collation name from the ui by seeking the culture...

i m using dynamic sql for this...however i m getting the desired result...but i want the same result without using dynamic sql

here is the sp

ALTER proc [dbo].[p_SortEmployee]
(
@firstname varchar(50),
@Lastname varchar(50),
@CollateName varchar(50)
)
as
begin
declare @sql nvarchar(max)
--set @CollateName = 'Traditional_Spanish_ci_ai ASC'

if (@firstname='FirstName')
begin
set @sql='select FirstName,LastName,JoiningDate,BirthDate from employee order by firstname COLLATE '
set @sql=@sql+ @CollateName
print @sql
exec sp_executesql @sql

end

if (@Lastname='LastName')
begin
set @sql='select FirstName,LastName,JoiningDate,BirthDate from employee order by Lastname COLLATE '
set @sql=@sql+ @CollateName
print @sql
exec sp_executesql @sql
end

end

can ne body tell me how i cud carry out the same without using dynamic sql as i m using above

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 06:02:22

Not possible since collation name is passed as a parameter. You will need to have dynamic SQL.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources