Posted by Simon Hayes on 10/01/27 11:19

<bozzzza@lycos.co.uk> wrote in message
news:1119438419.547258.218740@z14g2000cwz.googlegroups.com...
> Is it possible to tell sql server to cast to a datatype and set the
> field length to a variable.
>
> e.g. :-
>
> declare @flen int
> set @flen = 10
>
> select (cast somefield as char(@flen) newfield)
> into newtable
> from sometable
>
> I have also tried :-
> select (cast somefield as char(max(len(somefield))) newfield)
> into newtable
> from sometable
>
> When I try the above examples I get error in @flen; error in max
> respectivly.
>
> TIA
>
> Simon
>

I don't believe there's any easy way to do this, but in most cases, it's
probably not necessary - instead of declaring char(10), why not just declare
varchar(1000), or whatever value is suitable for you? If you can explain why
you need to do this, someone may have a better solution. Depending on what
you need to achieve, you might be able to use dynamic SQL, but that has a
number of issues:

http://www.sommarskog.se/dynamic_sql.html

Simon

[Back to original message]