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

<bozzzza@lycos.co.uk> wrote in message
news:1119438419.547258.218740@z14g2000cwz.googlegr oups.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

(bozzzza@lycos.co.uk) writes:
> 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.

No, you would have to use dynamic SQL for that. Seems easier to use
varchar.

What do you want to achieve, really?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog wrote:
> (bozzzza@lycos.co.uk) writes:
> > 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.
>
> No, you would have to use dynamic SQL for that. Seems easier to use
> varchar.
>
> What do you want to achieve, really?
>
>

Yhe problem is we have had some data supplied and the all the fields
lengths are set to 255 (nvarchar), even though this is not good pratice
we could live with it until someone else wanted a fixed length export
of the data.

So my idea was to work out the length of the fields and insert them as
the maximum width into the new table. Then the fixed length file would
look a lot better and cleaner.

Thanks for the reply, I will look into Dynamic SQL.

(bozzzza@lycos.co.uk) writes:
> Yhe problem is we have had some data supplied and the all the fields
> lengths are set to 255 (nvarchar), even though this is not good pratice
> we could live with it until someone else wanted a fixed length export
> of the data.
>
> So my idea was to work out the length of the fields and insert them as
> the maximum width into the new table. Then the fixed length file would
> look a lot better and cleaner.

Maybe. But what if the max lengths you find do agree with the actual
business rules? Next time you get a refresh, you could get an error
because of truncation.

So I would suggest that either you find out the actual max lengths, or
you leave the table the way it is.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

You have asked the same question in
microsoft.public.sqlserver.programming. Please don't post the same
question independently to diffferent groups. It's inconsiderate to
others who may waste time responding on something that has already been
answered elsewhere.

In your other thread you indicated that your intention is to
standardize the column sizes for reporting purposes. All the reporting
tools I know of allow you to specify a field width shorter than the
actual column width so I'm not sure why you would want to do this in
SQL. Keep it in the presentation tier is my suggestion.

--
David Portas
SQL Server MVP
--

David Portas wrote:
> You have asked the same question in
> microsoft.public.sqlserver.programming. Please don't post the same
> question independently to diffferent groups. It's inconsiderate to
> others who may waste time responding on something that has already been
> answered elsewhere.

Sorry.

> In your other thread you indicated that your intention is to
> standardize the column sizes for reporting purposes. All the reporting
> tools I know of allow you to specify a field width shorter than the
> actual column width so I'm not sure why you would want to do this in
> SQL. Keep it in the presentation tier is my suggestion.
>

Actually I needed to create a fix length text file of the data, so a
pascal programmer could import it into a DOS application, and the
programmer wasn't happy that the fields were coming out at 255 each.

After reading Erland's post, I gave the programmer the export in Comma
delimited format instead, so a refresh of the data won't effect the
export.

But thanks to all the posts I now know dynamic sql exists (I thought
exec was just for stored procedures) and it has opened up a whole new
world for me.

Yes it is possible!

Do it this way!

eg :-

declare @flen int
set @flen = 10

exec('select cast(somefield as char(' + @flen + ')) as newfield into
newtable
from oldtable')


Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***

> I now know dynamic sql exists (I thought
> exec was just for stored procedures) and it has opened up a whole new
> world for me.

Make sure you understand the implications. Dynamic SQL should usually
be a last resort in production code. See:
http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--