SQL Server: Storing Query Result into Variable

Question

I am trying to Concatenate Query Result for each Person into a Variable. Cursor Extract Columname from #temp table and extract data from PersonTable

For Example:For PID=1(FName: John LName:Hill HomeCountry=US HomeState=CH) 123JohnHillUSCH

  Person Table:

pid FName   LName   HomeCity    HomeState
1   Pascal    E    New York      NY
2   Steve     F    New York      NY

CREATE table #Temp
    ([Id] int,  [ColumnName] varchar(13))
;

INSERT INTO #Temp
    ([Id],[ColumnName])
VALUES
    (1,  'EID'),
    (2,  'FName'),
    (3,  'LName'),
    (4,  'HomeCountry'),
    (5,  'HomeState')
;

SELECT * FROM #Temp



SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER  PROCEDURE [dbo].[up_Conv_GenerateResultsFromMappingTable]
@Param1 VARCHAR(30)
AS
SET NOCOUNT ON

DECLARE @ColName VARCHAR(100);
DECLARE @Result VARCHAR(MAX)

DECLARE @Cur as CURSOR;

SET @Cur = CURSOR FOR
SELECT columnName FROM #Temp

OPEN @Cur;
FETCH NEXT FROM @Cur INTO @ColName;

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @query nvarchar(MAX)
PRINT @Param1;
SET @Query='SELECT '+@ColName+' FROM dbo.PERSON where PID='+@Param1+''  
PRINT @Query 
EXECUTE sp_executesql @Query
                  ,  N'@Param1 varchar(30)', 
                   @Param1 


FETCH NEXT FROM @Cur INTO @ColName;
END

CLOSE @Cur;
DEALLOCATE @Cur;

Expected result

1,1PascalENewYorkNY
2,2SteveFNewYorkNY

Are those columns you want to concatenate all in the same table? Or are they stored the way you show in your example, row by row? — Ryan Wilson
– Ryan Wilson, Commented Aug 30, 2018 at 13:00
Can you post sample data and expected results from the PERSON table — S3S
– S3S, Commented Aug 30, 2018 at 13:10
@RyanWilson: Yes but Row Wise for Each Employee..For Example: Output Table Contains EID, Concat(EID,FName,LName,HomeState,HomeCity),CreatedOn — SQLLearner
– SQLLearner, Commented Aug 30, 2018 at 13:42

BeardOfTriumph · Accepted Answer · 2018-08-30 15:05:17Z

2

In SQL Server 2012+, Why not just do

SELECT EID, CONCAT(EID, FName, LName, HomeCountry, HomeState) AS ConcatString, CreateDate
FROM Person

In SQL Server 2008R2 and below, it would have to be

SELECT EID, CAST(EID AS VARCHAR(20) + FName + LName + HomeCountry + HomeState AS ConcatString, CreateDate
FROM Person

And if You're wanting to return the results per user, you could parameterize it, and send in the EID individually from the source, or you could use a cursor...if you have absolutely no other option. If you're wanting to return the entire dataset, You can just consume the data straight from that dataset.

edited Aug 30, 2018 at 15:05

answered Aug 30, 2018 at 13:15

BeardOfTriumph

4722 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sean Lange Over a year ago

Great answer. Make sure when you use varchar that you ALWAYS specifiy the size. The defaults can change depending on usage. As with everything always better to be explicit. sqlblog.org/2009/10/09/…

BeardOfTriumph Over a year ago

Yeah I got lazy on that one. I went ahead and just changed it to the max allowable length for a bigint.

Collectives™ on Stack Overflow

SQL Server: Storing Query Result into Variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related