September 27, 2011 at 5:01 am
Hi I have a requirement where i need to take the database default collation for creating the view.
There is an option called database_default but this query is not working as expected
No.1)
select col1 collate database_default from table1
union
select col1 from table2
Here col1 collation is dynamic based on the schema installation
Above query works fine onlyi f i run it as it is.Suppose if i run the same query in another way by creating the view
create view v1 as select col1 collate database_default from table1
then do a union operation
No.2 )
select col1 from v1
union
select col1 from table2
then this is not working. How to make this working.
Is there any otherway where can i pass the collation value as variable
something like declare @col
set @col=select collation_name from sys.columns where table_name='table1'
and column_name='col1'
and then
create view v1 as select col1 collate @col from table1
such that my union query should work (no.2)
please help me out
September 27, 2011 at 6:51 am
I can't think of a way to do it without using dynamic SQL:
declare @sql varchar(4000)
declare @coll varchar(128)
-- change the select to get your required collation names
set @coll = (select top(1) collation_name from sys.columns where collation_name is not null)
set @sql = 'create view v1 (orderDesc) as select orderDesc collate ' + @coll + ' from dbo.Orders'
exec(@sql)
September 27, 2011 at 6:56 am
This was removed by the editor as SPAM
September 28, 2011 at 5:37 am
Hi ,
Thanks both of you for the immediate response.
May i know, what are the risks with the dynamic sql
September 28, 2011 at 6:16 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply