CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012dynamic-sql
user1254579
user1254579

Reputation: 4009

Dynamic SQL to store the execution result in a variable and concatenate that result to form a full query

I was trying to make a query with dynamic column name.So that the final query can execute

DECLARE @SQLFinal NVARCHAR(MAX)
DECLARE @SQLAlias NVARCHAR(MAX)='SELECT '',''+cast(ColumnName as varchar(20)) + '' as '' + cast(ColAliasName as varchar(20)) collate Latin1_General_CI_AS from #colandAlias' 
DECLARE @SQLAliasResult NVARCHAR(MAX)
EXEC sp_executesql @SQLAlias= @SQLAliasResult OUTPUT
select  @SQLAliasResult

--Assigned the sp_execute result to a variable , @SQLAliasResult.It gives NULL instead of the exec result

-- the intention is to concatinate the above result(@SQLAliasResult) with belwo query part to form a full query 

set @SQLFinal='SELECT

ID,Name,custid '

+@SQLAliasResult+
' FROM dbo.tableCustomer where custid=71'

EXEC @SQLFinal

-- how to assign the query execution result in a variable and execute @SQLFinal ?

---output of the@SQLAliasResult

,col1 as [201911]
,col2 as [201912]
,col1 as [202001]
,col2 as [202002]

--The content of @SQLFinal 

SELECT
ID,Name,custid 
,col1 as [201911]
,col2 as [201912]
,col1 as [202001]
,col2 as [202002]
FROM dbo.tableCustomer where custid=71

exec @SQLFinal would give the required out put from the dbo.tableCustomer with generated aliases

Upvotes: 0

Views: 67

Answers (1)

Emiliano Javier González
Emiliano Javier González

Reputation: 190

I think that there's no need for dynamic query in line 2, I see no dynamic fields. Also, I think that you need something like this:

DECLARE @Columns TABLE(
    ColumnName SYSNAME
)

INSERT INTO @Columns VALUES ( 'Column1' ), ( 'Column2' );

DECLARE @SelectString   VARCHAR(255)

-- This is the sentence that is going to concat everything
SELECT  @SelectString = ISNULL( @SelectString + ', ', '' ) + ColumnName
    FROM @Columns;

-- Execute this or do what you need
SELECT @SelectString;

Upvotes: 1

Related Questions