dynamic sql variable problem

Question

Hi All, I want to create primary key dynamically. However, I get an error when I include the @PKColumn in the code. declare @sourcetablename nvarchar(max) = N'[dbo].[AAG00200]' declare @targettablename nvarchar(max) = N'[dbo].[AAG00200_Test]' declare @PKname nvarchar(max) declare @PKColumn nvarchar(max) declare @sql nvarchar(max) set @sql = N' IF OBJECT_ID(''tempdb.dbo.#TempTable'', ''U'') IS NOT NULL DROP TABLE #TempTableName select * into #TempTable from '+@sourcetablename+' SELECT @PKname = Index_name FROM #TempTable where is_primary_key = 1' EXEC sp_executesql @sql,N'@PKname nvarchar(max) OUTPUT',@PKname = @PKname OUTPUT 'SELECT @PKColumn = column_name FROM #TempTable where is_primary_key = 1' EXEC sp_executesql @sql,N'@PKColumn nvarchar(max) OUTPUT',@PKColumn = @PKColumn OUTPUT SET @sql=N'ALTER TABLE '+@targettablename+' ADD CONSTRAINT '+@PKname+' PRIMARY KEY (ACTINDX)' EXEC sp_executesql @sql the error message is : Incorrect syntax near 'SELECT @PKColumn = column_name FROM #TempTable where is_primary_key = 1'. How do I fix this error? Thanks

Answer 1

Hi @Nishan Ratna I guess you want to add two variables inside the dynamic SQL. You could define two output variables in one single sp_executesql statement. Check this:

declare @sourcetablename nvarchar(max) = N'[dbo].[AAG00200]'
declare @targettablename nvarchar(max) = N'[dbo].[AAG00200_Test]'
declare @PKname nvarchar(max)
declare @PKColumn nvarchar(max)
declare @sql nvarchar(max)

set @sql = N'
IF OBJECT_ID(''tempdb.dbo.#TempTable'', ''U'') IS NOT NULL
DROP TABLE #TempTableName
select * into #TempTable from '+@sourcetablename+'
SELECT @PKname = Index_name FROM #TempTable where is_primary_key = 1
SELECT @PKColumn = column_name FROM #TempTable where is_primary_key = 1'
EXEC sp_executesql @sql
    ,N'@PKname nvarchar(max) OUTPUT,@PKColumn nvarchar(max) OUTPUT'
    ,@PKname = @PKname OUTPUT,@PKColumn = @PKColumn OUTPUT

SET @sql=N'ALTER TABLE '+@targettablename+' ADD CONSTRAINT '+@PKname+' PRIMARY KEY (ACTINDX)'
EXEC sp_executesql @sql

Best regards, Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Probably you should replace

 'SELECT @PKColumn = colu...

with

 set @sql = N'SELECT @PKColumn = colu...