Solved

SQL function with  coalesce not returning first column

Posted on 2008-06-23
3
219 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 69

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 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

Need Help in Real-Time?

Connect with top rated Experts

29 Experts available now in Live!

Get 1:1 Help Now