Can someone tell me why this creates an error:

declare @int int
set @int = 55

select cast(@int as char(Len(@int)))


CHAR takes an integer argument, and LEN returns an integer, so why
should it fail?

Probably something about set-based logic that eludes me, that would
work fine in procedural code.

Bob Graham

Your statement would evaluate as

SELECT CAST(55 as CHAR(LEN(55)))

which doesn't make sense to me. What are you trying to do?

Stu

For use in a stored procedure where the number being converted to
string isn't known ahead of time, and I need to pad it with 0's (using
Replicate) to be a certain length. Casting to VarChar instead of Char
works fine, I was just curious why the above wouldn't work.

[quoted text, click to view]

Are you aware that you are supposed to do formatting in the front end
and NEVER in the database?? That is ** the** fundamental idea behind
tiered architectures -- a far more general principle of programming
than SQL.

[quoted text, click to view]

Is this *the* Joe Celko??? or an imitation? :-)

Seriously, the reason for this is that I'm being asked to combine data
from tables that were never meant to be viewed together. So we're
adding a letter at the front of the identity column from several
different tables and displaying them padded so they will sort
alphabetically correctly, ie: G00233 will be *after* G00078, not
before.

On, a more genreal note, the power of any computer language is what you
can do that wasn't foreseen. Otherwise the languages would be a lot
smaller and none of us would have jobs. I think.

Now that I have this working right, it's leaps and bounds faster than
having my front-end program iterate through all the rows to prepend
characters.

Bob Graham

[quoted text, click to view]

Hi Bob,

Nothing to do with set-based logic.

The datatype char(..) and varchar(..) allow only an integer VALUE for
the character's length, not an expression.

DECLARE @test char(3+3) fails as well. As does
DECLARE @i int
SET @i = 6
DECLARE @test char(@i)


Best, Hugo
--

A possible solution would be to dynamically build the SP as a temporary SP
(with the required length for Char() or as a String and EXEC it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


[quoted text, click to view]

Stu (stuart.ainsworth@gmail.com) writes:
[quoted text, click to view]

No, len(55) returns 2, as 55 is implicitly converted to the string
'55' and then it goes downhill from there.




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

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

[quoted text, click to view]

You can not be so definitive.

How many systems have you had a hand in? Do you think pumping 1 million rows
out to a middle tier / front end when you just need page 5 or 'x' scales?

No, you would do it in SQL Server and only return the rows necessary.

Yet again you fail to understand real world concepts like scalability, user
front end performance and maintainability.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


[quoted text, click to view]

[quoted text, click to view]

Read that sentence back to yourself. Like Britney Spears' record
collection and squid farming statistics? How can such a result have
any meaning?

[quoted text, click to view]

And you are using IDENTITY columns in both unrelated tables, to further
confuse things? Then you are converting numerics to strings so you can
concatenate things to them. That is approximately the way we did it in
COBOL in the old days.

Why not just add a column for the data source ('R' = record collection,
'S' = squids, etc.) and then do a two column sort without the overhead
casting?

[quoted text, click to view]

And I would add to that, ".. with elegance and not kludging."

[quoted text, click to view]

More like a customer *guarantees* you that two business entities that
you're dealing with will **never** be viewed or reported together. Then
six months later he decides to combine them into a parent child
relationship, and now he wishes he had requested you to create
hierarchy instead of just duplicating a database and it's front end
components. Duplication took about ten minutes. That was what he wanted
to pay for. Creating dependant child objects that don't step on the
feet of the parent objects with duplicated primary keys and many other
issues would have taken weeks.

[quoted text, click to view]

Not much of an issue with read-only reporting, agree it would be a
morass if I needed to create updateable tables.

[quoted text, click to view]

Because I needed to display a four-tiered hierarchy as one table for
the purpose of this read-only report. Having the translated values
spread across multiple columns simly wouldn't give me the report I
wanted.

[quoted text, click to view]

Honestly, this can't be the same Joe Celko who wrote the foreword in my
favorite Ken Henderson T-Sql book. My solution was elegant, because it
took very little time, it's bullet-proof in a read-only report, and the
query runs in under a second, versus 5 seconds it used to take
manipulating values in the front-end!

Bob Graham