Return value - 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)

Return value

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-07-02 : 12:02:11

Hello:

I have a store procedure return some results, but also return no results.

PROC SP_NAME

@name varchar(100) output

select @name = student from class

How to accomplish if return no result, will set to: None.

As Always, you help is highly apprecaited.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-02 : 13:03:42

set @name = coalesce((select top 1 student from class), 'none')

or if you prefer
select @name = student from class -- this will end up with one student value from class
select @name = coalesce(@name, 'none')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-07-02 : 15:03:45

Thanks for the quick response.

This procedure is actually concatenate return string.

PROC SP_NAME

@returnString varchar(100) output

select @returnString = Coalesce(@returnString + ', ', '') + cast(student as varchar) from class

if called this procedure SP_NAME, I got return string: aaaa,abba,sscd,aczx

However, I have return string is NULL, I want set up if returnString is NULL, get me "none", else return the result

I use

select @returnString = Coalesce(@returnString, 'none') + cast(student as varchar) from class did not work.

Where I missed.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-02 : 17:01:18

Concatenating anything with null returns null, so try

select @returnString = (@returnString + cast(student as varchar),'None')
if @returnString is null or student is null, the returnstring will = 'none'

Jim

P.S cast(student as varchar) can only return one value

Everyday I learn something that somebody else already knew

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-07-02 : 17:16:11

Jim:

Thanks for the quick response.

I tried:

select @returnString = (@returnString + cast(student as varchar),'None')

I got syntax: Incorrect syntax near ','.

This procedure is supposed to return concatenated string but if the result is Null, it will return: None

Where I missed? Thank you very much for the help.

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-07-03 : 11:41:43

Hello:

Is anyone who could help me out?

This procedure is supposed to return concatenated string but if the result is Null, it will return: None

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-03 : 12:59:28

Presumably your problem is that there is no data in the class table - so the statement does not set a value in @returnString

You coould go with two statements
select @returnString = Coalesce(@returnString + ', ', '') + cast(student as varchar) from class
select @returnString = coalesce(@returnString, 'none')

or maybe something like

;with cte as
(
select student = coalesce(cast(student as varchar), 'none') from class -- cater for null student
union all
select student = 'none' where not exists (select * from class)
)
select @returnString = Coalesce(@returnString + ', ', '') + student from class

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources