1 Replies - 314 Views - Last Post: 16 April 2017 - 03:00 PM

#1 mager1794  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 153
  • Joined: 23-March 09

@variable returning empty after setting with select

Posted 16 April 2017 - 01:22 PM

SELECT @pivot = COALESCE(CONCAT(@pivot,','), '') + '[col',CAST(number + 1 as char(10)), ']' FROM spt_values
    where type = 'p' and number <= (
		SELECT MAX(length(value)-length(REPLACE(value,''[email protected]+'',''))) FROM tempTable
    );
    
    SELECT @pivot;



My @pivot value is continually returning null, I've rewritten this code at least 20 times somehow always managing to make something better but still unable to fix the issue. Any ideas what the issue could be?

Is This A Good Question/Topic? 0
  • +

Replies To: @variable returning empty after setting with select

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5078
  • View blog
  • Posts: 13,705
  • Joined: 18-April 07

Re: @variable returning empty after setting with select

Posted 16 April 2017 - 03:00 PM

You are doing a lot of things here. How about going back to basics and trying something slightly simpler and build your way back to this. For instance can you do something like...

SELECT @pivot = somecolumn FROM spt_values
    where type = 'p' and number <= (
		SELECT MAX(length(value)-length(REPLACE(value,''[email protected]+'',''))) FROM tempTable
    );



Does this put something into @pivot for you? If yes, then you know the problem lies in your coalesce or concatenation to a column. If it doesn't set it, then you know that perhaps your where clause is not giving you what you expect.

I am also not quite sure you can build dynamic column names exactly like this just by tacking them into there. You may need to set them first to a variable and use the variable into a prepared statement or something. But all in all, start smaller and see if all the parts work and then add in the rest. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1