Why not display - 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)

Why not display

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-05-05 : 12:22:03

Hi, my query is

DECLARE @List varchar(2000)
          DECLARE @DESCR varchar(255)
          SET  @DESCR = (SELECT A.Message from(SELECT CONVERT(INT,Substring(MessageID,16,LEN(MessageID)-15)) AS GRP_ID,Message
          FROM MESSAGES
          WHERE MessageGroup COLLATE SQL_Latin1_General_CP1_CS_AS ='Screen'
          and MessageID COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'PERMISSION_%'
          and CONVERT(INT,Substring(MessageID,16,LEN(MessageID)-15)) in (1,2,3)
          as A
          WHERE A.GRP_ID=1)
	


          SELECT @List = COALESCE(@List + ', ', '') + Cast(@DESCR As varchar(51))
          FROM EMPLOYEE_GROUP, PERMISSION_GROUP WHERE PERMISSION_GROUP.GRP_ID = EMPLOYEE_GROUP.GRP_ID AND  EMP_ID = 'A2LEE'

I want to see @List but failed.
I think the error is

Cast(@DESCR As varchar(51))

If I remove CAST, it looks good. But I want to limit the length.

Thanks for help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 12:40:49

any error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-05-05 : 12:58:05

No error message. Just no display.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-05-05 : 13:00:33

Sorry, even I remove CAST, no showing as well.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-05 : 13:36:52

Do a

SELECT @List

again. It will display then.

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-05 : 13:40:33

Post the DDL for the table MESSAGES

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 07:10:57

It is becuase @DESCR is null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources