Programming Field Lengths

[bozzzza]

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

[Simon Hayes]

<bo*****@lycos.co.uk> wrote in message
news:11**********************@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

[Erland Sommarskog]

(bo*****@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, es****@sommarskog.se

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

[bozzzza]

Erland Sommarskog wrote:

(bo*****@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.

[Erland Sommarskog]

(bo*****@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, es****@sommarskog.se

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

[David Portas]

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
--

[bozzzza]

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.

[debian mojo]

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 ***

[David Portas]

> 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
--