Solved

SQL function with  coalesce not returning first column

Posted on 2008-06-23
3
229 Views
Last Modified: 2010-04-21
I have the following function that returns a string for pivoting data.  This function also casts it based on a value from another field.

When I try this it doesn't return the first value but does return subsequent values. I.E. a project should have 4 columns but only comes back with three.
ALTER function [dbo].[column_elements_casted] ( @projectid int)
returns varchar(Max)
as
begin
 
declare @res varchar(Max)
 
      select @res = coalesce(@res + ', cast ([' +  ELEMENT_NAME + '] as ' + Element_Type_Name + ') as [' + ELEMENT_NAME + ']', '')-- + ELEMENT_NAME
      from dbo.Project_Element_Names
      WHERE Project_ID = '53'
      
 
select @res
      
 
END

Open in new window

0
Comment
Question by:vhaum
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21849721
here we go:
ALTER function [dbo].[column_elements_casted] ( @projectid int)
returns varchar(Max)
as
begin
 
declare @res varchar(Max)
 
      select @res = coalesce(@res + ',' ,'' ) + cast ([' +  ELEMENT_NAME + '] as ' + Element_Type_Name + ') as [' + ELEMENT_NAME + ']' )
      from dbo.Project_Element_Names
      WHERE Project_ID = '53'
      
 
select @res
      
 
END

Open in new window

0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 21849735
your @res variable is NULL, try this:

declare @res varchar(Max)
set @res = ''
0
 

Author Closing Comment

by:vhaum
ID: 31469915
Thanks, I was thinking it was null, I was having a hard time remembering where to set it to ''.

Thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question