Dynamic @TableDefinition returns syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic @TableDefinition returns syntax error

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-18 : 10:13:40

I have two queries about a dynamic SQL script (full script at the end of this post). Table AFields has records with the names of the columns in the Customer table. The script succesfully generates a list of fields from AFields. And I then use this result to generate the column names to put between the brackets of

DECLARE @TableData TABLE(@TableDefinition)

This works if I print @TableDefinition to screen and then copy and paste in place of the parameter - using the parameter I get the error message

Incorrect syntax near '@TableDefinition'.

Ideas please?

The second question relates to the EXEC statement at the end - I would like to replace EXEC (@sql) (which works) with sp_executesql - but cannot find the right syntax - the error message is
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
- help please (!)

The whole script is as follows (SQL 2005):

BEGIN
DECLARE @TableName nVarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames nVarChar(MAX) SET @FieldNames = ''
DECLARE @TableDefinition VarChar(MAX) SET @TableDefinition = ''
DECLARE @Length int
--RETURN
SELECT @FieldNames = @FieldNames + COALESCE(quotename(FToAudit) + ', ','')
FROM AFields
WHERE TToAudit = @TableName
PRINT @FieldNames

SET @Length = DATALENGTH(@FieldNames)
IF (@Length > 0)
BEGIN
SET @FieldNames = SUBSTRING(@FieldNames,1, DATALENGTH(@FieldNames) - 2)
END
PRINT @FieldNames

SELECT @TableDefinition = REPLACE(@FieldNames,',',' varchar(200),') + ' varchar(200)'
PRINT @TableDefinition

DECLARE @SQL VarChar(1000)

SELECT @SQL = 'SELECT ' + @FieldNames
SELECT @SQL = @SQL + ' FROM dbo.'
SELECT @SQL = @SQL + quotename(@TableName)
PRINT @SQL

DECLARE @TableData TABLE
(
@TableDefinition
)
INSERT INTO @TableData
EXEC (@SQL)
--EXEC sp_executesql @SQL, --@FieldNames N'nvarchar(MAX)', @TableName = @TableName
SELECT * FROM @TableData
END

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-06-18 : 14:12:19

For better understanding, would you please provide sample tables with data and the expected output?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-18 : 14:22:03

You can't use @TableDefinition in @TableData like that. I think you'll instead need to dynamically create a # temp table as I don't think you do it with a table variable. The key is that it'll have to be dynamic as well since you can't pass a variable to CREATE TABLE.

@SQL will need to be nvarchar in order to use sp_executesql. Make it nvarchar(4000) due to double-byte data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-18 : 14:53:15

Thanks namman and tkizer - the example is as follows:

I have commented out the two lines that don't work - and put in 'hard coded' lines that work

USE TEST1
CREATE TABLE [dbo].[AFields]
(
[AFields_ID] [uniqueidentifier] NOT NULL,
[FToAudit] [nvarchar](30) NULL,
[TToAudit] [nvarchar](30) NULL
)
CREATE TABLE [dbo].[Customers]
(
[Customers_ID] [uniqueidentifier] NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](30) NOT NULL
)
Insert into [AFields] (AFields_ID, FToAudit, TToAudit)
VALUES (newid(), 'FirstName', 'Customers')
Insert into [AFields] (AFields_ID, FToAudit, TToAudit)
VALUES (newid(), 'LastName', 'Customers')
Insert into Customers (Customers_id, FirstName, LastName)
VALUES (newid(), 'Sian', 'Smith')
Insert into Customers (Customers_id, FirstName, LastName)
VALUES (newid(), 'Peter', 'Jones')
SELECT * FROM Afields
SELECT * FROM Customers
/** Now the Dynamic SQL**/
BEGIN
DECLARE @TableName nVarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames nVarChar(MAX) SET @FieldNames = ''
DECLARE @TableDefinition VarChar(MAX) SET @TableDefinition = ''
DECLARE @Length int
SELECT @FieldNames = @FieldNames + COALESCE(FToAudit + ', ','')
FROM AFields
WHERE TToAudit = @TableName
PRINT @FieldNames
SET @Length = LEN(@FieldNames)
Print @Length
IF (@Length > 0)
BEGIN
SET @FieldNames = SUBSTRING(@FieldNames, 1, LEN(@FieldNames) -1)
END
PRINT @FieldNames
SELECT @TableDefinition = REPLACE(@FieldNames,',',' varchar(200),') + ' varchar(200)'
PRINT @TableDefinition
DECLARE @SQL VarChar(1000)
SELECT @SQL = 'SELECT ' + @FieldNames
SELECT @SQL = @SQL + ' FROM dbo.'
SELECT @SQL = @SQL + quotename(@TableName)
PRINT @SQL
DECLARE @TableData TABLE
(
--@TableDefinition
FirstName varchar(200), LastName varchar(200)
)
INSERT INTO @TableData
EXEC (@SQL)
--EXEC sp_executesql @SQL, --@FieldNames N'nvarchar(MAX)', @TableName = @TableName
SELECT * FROM @TableData
END

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-18 : 15:01:04

thank you Tara - the EXEC sp_executesql @SQL now works with DECLARE @SQL nvarchar(4000)

I will try the # temp table rather than the variable @temp table

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-18 : 18:55:22

I think the secret is in creating the table variable or temporary table in the @SQL - as both can be made to work

I copy both below in full for the sake of completeness - although the first 20 or so lines are identical

Table Variable example:

BEGIN
DECLARE @TableName nVarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames nVarChar(MAX) SET @FieldNames = ''
DECLARE @TableDefinition VarChar(MAX) SET @TableDefinition = ''
DECLARE @Length int
SELECT @FieldNames = @FieldNames + COALESCE(FToAudit + ', ','')
FROM AFields
WHERE TToAudit = @TableName
PRINT @FieldNames
SET @Length = LEN(@FieldNames)
Print @Length
IF (@Length > 0)
BEGIN
SET @FieldNames = SUBSTRING(@FieldNames, 1, LEN(@FieldNames) -1)
END
PRINT @FieldNames
SELECT @TableDefinition = REPLACE(@FieldNames,',',' nvarchar(200),') + ' nvarchar(200)'
PRINT @TableDefinition
DECLARE @SQL nvarchar(4000)
SELECT @SQL = 'DECLARE @TableData TABLE ('
+ @TableDefinition + ')' + ' INSERT INTO @TableData' +
' SELECT ' + @FieldNames + ' FROM dbo.' + quotename(@TableName)
+ ' SELECT * FROM @TableData'
PRINT @SQL
EXEC sp_executesql @SQL
END

OR the temp table

BEGIN
DECLARE @TableName nVarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames nVarChar(MAX) SET @FieldNames = ''
DECLARE @TableDefinition VarChar(MAX) SET @TableDefinition = ''
DECLARE @Length int
SELECT @FieldNames = @FieldNames + COALESCE(FToAudit + ', ','')
FROM AFields
WHERE TToAudit = @TableName
PRINT @FieldNames
SET @Length = LEN(@FieldNames)
Print @Length
IF (@Length > 0)
BEGIN
SET @FieldNames = SUBSTRING(@FieldNames, 1, LEN(@FieldNames) -1)
END
PRINT @FieldNames
SELECT @TableDefinition = REPLACE(@FieldNames,',',' nvarchar(200),') + ' nvarchar(200)'
PRINT @TableDefinition
DECLARE @SQL nvarchar(4000)
SELECT @SQL = 'CREATE TABLE #TableData ('
+ @TableDefinition + ')' + 'INSERT INTO #TableData' +
' SELECT ' + @FieldNames + ' FROM dbo.' + quotename(@TableName)
+ ' SELECT * FROM #TableData DROP TABLE #TableData'
PRINT @SQL
EXEC sp_executesql @SQL
END

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-18 : 19:07:09

Thank you for steering me to this point - but now I have a couple more questions

Which is more secure from e.g. SQL Injection - a table variable or a temp table?
What are the crucial differences between the two?
Are there other things in the above code that could be improved to make them more secure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-18 : 19:22:17

Neither is really vulnerable to SQL injection. It just depends where this code will be. Will it be in a stored procedure with parameters that allow bad things to be passed?

The crucial difference between the two is performance. Table variables suffer from poor performance due to lack of statistics. I had previously been recommending table variables when the result set was small, however I now recommend avoiding table variables altogether due to the issues that we experienced in production and confirmed by Microsoft.

As to improvements, the whole idea is bad.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-22 : 12:12:54

Tara thank you for your help. The Stored Procedure now works fine. There is a restriction in the application (using SQL2005 as the database) that means I have to return the record set back in a view. So far I have tried OpenRowSet but I think this is inappropriate as the query would be in constant use and we would have to turn on Ad Hoc Queries. I have also tried using a Linked Server (back to the same database) and OpenQuery - but I get a syntax error. Although it would be great to use a table valued function I think this is not possible because of the Dynamic SQL. So far my best hope is the Linked Server and OpenQuery e.g. as follows:-

SELECT * FROM OPENQUERY([SERVER-1].Test1DB.dbo.[SP_RFTA])

but this gives error:
Incorrect syntax near ‘.’.

If I can get this to work then I will have to think about how to pass one parameter into the Stored Procedure.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-22 : 14:00:03

Don't you have to pass a query to OPENQUERY?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-06-23 : 05:46:49

Yes or perhaps no? ... please look at this blog

http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

I am sure that I am missing something really easy about selecting the results of a stored procedure in a view????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources