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.
| Author |
Topic |
|
dotnetallday
Starting Member
16 Posts |
 Posted - 2007-09-08 : 15:42:40
|
| For some reason this baffles me. How can I return an empty string value (e.g., "") when my select query returns an empty recordset (that is, no rows are returned)?My current query (which returns an empty row - I'd like for it to return me City = "") is:ALTER PROCEDURE [dbo].[FS_GetCityByZipCode] @ZipFive int, @ZipFour smallintASBEGIN SET NOCOUNT ON; DECLARE @Result nvarchar SET @Result = ( SELECT TOP 1 City FROM FS_Cities WHERE ZipFive = @ZipFive AND ZipFourLow <= @ZipFour AND ZipFourHigh >= @ZipFour) SELECT @Result AS CityENDThanks!Jason A. Kiesel Kiesel Media Group, Inc. http://www.kmg.net http://hosting.kmg.net *ASP.NET Web Hosting Starting @ $9.99 / Month |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-08 : 16:15:38
|
| SELECT coalesce(@Result,'') AS City--------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-09 : 05:49:29
|
That would also return empty string if the first row returned happened to have a NULL City.Note that using "SELECT TOP 1 ..." it is common to include an ORDER BY, otherwise you will not get repeatable results, so users may see a different City each time for the same query Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-09 : 14:04:46
|
| then this?SELECT case when @@rowcount=0 then '' else @result end AS City--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-09 : 14:07:54
|
| Indeed, I reckon that is "technically" more accurate, but either may well do for the OP |
|
|
|
|
|
|
How Do I Return an Empty String When Empty?