?
Solved

SQL function with  coalesce not returning first column

Posted on 2008-06-23
3
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 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