SELECT (CASE ... + datatypes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT (CASE ... + datatypes

Author

Topic

cupeet987
Starting Member

11 Posts

Posted - 2007-12-19 : 16:46:25

I'm with SQL Server Expression and I'm trying to write a select that always returns a char, whose value will be 'X' or the character for an algarism obtained from a tinyiny.

Both blocks of code are similar, but the first one doesn't use the variable, and works perfectly. The second block is a bit more problematic: it complains the following:

Msg 245, Level 16, State 1, Line 76
Conversion failed when converting the varchar value 'X' to data type tinyint.

Thank you very much for your time!

DECLARE @var tinyint;
SET @var = 10;

SELECT ''+( -- ##### 1st BLOCK - WORKS! #####
CASE
WHEN (10 = 10) THEN (SELECT 'X')
WHEN (1 < 2) THEN (SELECT '7')
ELSE (SELECT ('I love SQLTeam.com'))
END
);

-- ##############################

SELECT ''+( -- ##### 2nd BLOCK DOESN'T WORK! WHY??? ####
CASE
WHEN (@var = 10) THEN (SELECT 'X')
WHEN (@var <2 ) THEN (SELECT '0')
ELSE (SELECT ''+(''+@var))
END
);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 16:54:29

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

~~ELSE (SELECT ''+(''+@var))~~

ELSE (SELECT ''+(''+cast(@var as varchar)))

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 02:18:04

Why dont you use this?

DECLARE @var tinyint;
SET @var = 10;

SELECT
CASE
WHEN (@var = 10) THEN 'X'
WHEN (@var <2 ) THEN '0'
ELSE cast(@var as varchar(10))
END

Madhivanan

Failing to plan is Planning to fail

cupeet987
Starting Member

11 Posts

Posted - 2007-12-20 : 08:52:28

It worked. Thank you very much.

I must start studying casts...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 09:08:28

quote:
Originally posted by cupeet987

It worked. Thank you very much.

I must start studying casts...

Which worked?

Madhivanan

Failing to plan is Planning to fail

cupeet987
Starting Member

11 Posts

Posted - 2007-12-24 : 09:47:51

quote:
Originally posted by madhivanan

quote:
Originally posted by cupeet987

It worked. Thank you very much.

I must start studying casts...

Which worked?

Madhivanan

Failing to plan is Planning to fail

both worked
thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 09:53:53

But use non-dynamic version

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources