February 6, 2011 at 11:52 pm
hi,
Can anyone tell me how to solve the below dynamic query? It says incorrect syntax near keyword 'SELECT'. I am not able to get the columns for insertion.
DECLARE @CID BIGINT,@SQL NVARCHAR(2000),@Val CHAR(8),@ID INT,@SQL1 NVARCHAR(800),@C INT,
@Column_Val_R VARCHAR(200)
SET @CID = 123
SET @C = 2
SET @ID = 2
SET @SQL = ''
SET @val = ''
SET @SQL1 = ''
SET @Column_Val_R = '[A, C] VARCHAR(80),[B, C] VARCHAR(80)'
SET @SQL = 'CREATE TABLE ##Result_' + CONVERT(NVARCHAR,@CID) + '(fldID INT IDENTITY(1,1),fldName CHAR(8))
INSERT INTO ##Result_' + CONVERT(NVARCHAR,@CID) + '
SELECT ''[A, C]'' UNION ALL SELECT ''[B, C]'''
EXEC sp_ExecuteSQL @SQL
WHILE (@C = @ID)
BEGIN
SET @SQL = ''
SET @SQL = '(SELECT fldName FROM ##Result_' + CONVERT(NVARCHAR,@CID) + ' WHERE fldID = ' + CONVERT(NVARCHAR,@ID) + ')'
SET @SQL1 = 'CREATE TABLE ##Result(fldID INT IDENTITY(1,1),' + @Column_Val_R + ')
INSERT INTO ##Result (' + (@SQL) + ')
SELECT * FROM ##RESULT_'+ CONVERT(NVARCHAR,@CID) + ' WHERE fldName = ' + (@SQL)
exec (@sql1)
select @sql1
END
SELECT * FROM ##Result
DROP TABLE ##Result_123
DROP TABLE ##Result
Thanks in Advance!
February 7, 2011 at 2:17 am
The compiler doesn't like the doubel parenthesis around your @sql string:
DECLARE @CID BIGINT,@SQL NVARCHAR(2000),@Val CHAR(8),@ID INT,@SQL1 NVARCHAR(800),@C INT,
@Column_Val_R VARCHAR(200)
SET @CID = 123
SET @C = 2
SET @ID = 2
SET @SQL = ''
SET @val = ''
SET @SQL1 = ''
SET @Column_Val_R = '[A, C] VARCHAR(80),[B, C] VARCHAR(80)'
IF OBJECT_ID('tempdb..#Fields') IS NOT NULL DROP TABLE #Fields
IF OBJECT_ID('tempdb..##Result') IS NOT NULL DROP TABLE ##Result
SET @sql = 'IF OBJECT_ID(''tempdb..##Result_'+ CAST(@CID AS varchar(50))+''') IS NOT NULL DROP TABLE ##Result_'+ CAST(@CID AS varchar(50))
PRINT @sql
EXEC(@sql)
SET @SQL = 'CREATE TABLE ##Result_' + CONVERT(NVARCHAR,@CID) + '(fldID INT IDENTITY(1,1),fldName CHAR(8))
INSERT INTO ##Result_' + CONVERT(NVARCHAR,@CID) + '
SELECT ''[A, C]'' UNION ALL SELECT ''[B, C]'''
CREATE TABLE #Fields (
fldName char(8)
)
DECLARE @fldName char(8)
PRINT @sql
EXEC sp_ExecuteSQL @SQL
WHILE (@C = @ID)
BEGIN
SET @SQL = ''
SET @SQL = ' SELECT fldName FROM ##Result_' + CONVERT(NVARCHAR,@CID) + ' WHERE fldID = ' + CONVERT(NVARCHAR,@ID) + ' '
TRUNCATE TABLE #Fields
PRINT @sql
INSERT #Fields
EXEC(@SQL)
SELECT @fldName = fldName
FROM #Fields
SET @SQL1 = 'CREATE TABLE ##Result(fldID INT IDENTITY(1,1),' + @Column_Val_R + ')
INSERT INTO ##Result ('+ @fldName +')
SELECT fldName FROM ##RESULT_'+ CONVERT(NVARCHAR,@CID) + ' WHERE fldName = @fldName '
print @sql1
EXEC sp_executeSQL @sql1, N'@fldName char(8)', @fldName
END
SELECT * FROM ##Result
That said, I can't understand what you're trying to do here. I fixed some things in your code, but I don't know what it should do.
Can you explain what is your expected results, maybe based on some sample data?
Take a look at the article in my signature line and you will find out how you can get quicker answers to your questions.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply