Dynamic Query : How to define Table Name or other String in SQL-Server

Question

I have the following 2 queries that should return the same information.

One normal query and the dynamic query which does not retrieve any result because of the @TABLENAME variable naming I guess.

DECLARE @TABLENAME as NVARCHAR (MAX) SET @TABLENAME = 'MyTable'

Query Returning Results

select * from jfa.[dbo].[MyTable] INNER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TableName and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2

Dynamic Query that does not return any result

DECLARE @sql as nvarchar(max) set @sql = 'select * from jfa.[dbo].[MyTable] INNER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = ''@TableName'' and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2' exec sp_executeSQL @sql, N'@TABLENAME nvarchar', @TABLENAME

I guess the problem is in the following line:

 on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = ''@TableName''

But I have tried several things an no success.

Many thanks in advance,

Kat

Possible duplicate of How to write Select query with dynamic table name in Stored Procedure? — Prabhat G
– Prabhat G, Commented Oct 18, 2017 at 10:05

Rokuto · Accepted Answer · 2017-10-18 10:18:08Z

Problem is here:

N'@TABLENAME nvarchar',

This code means that variable @TABLENAME is nvarchar(1). Change it to e.g.

N'@TABLENAME nvarchar(45)',

Also you have to delete double ' from INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = ''@TableName''. So your code will look like:

DECLARE @sql as nvarchar(max) set @sql = 'select * from jfa.[dbo].[MyTable] INNER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TableName --change here and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2' exec sp_executeSQL @sql, N'@TABLENAME nvarchar(45)', --change here @TABLENAME

However, if you want to dynamically change table from which you are selecting data, then you have to change static jfa.[dbo].[MyTable] to jfa.[dbo].'+@TableName+'. Finally, your query will look like:

DECLARE @sql as nvarchar(max) set @sql = 'select * from jfa.[dbo].'+@TableName+' --change here INNER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TableName --change here and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2' exec sp_executeSQL @sql, N'@TABLENAME nvarchar(45)', --change here @TABLENAME

Thanks Rokuto, I adapted it, I also defined for other Varchar (45). When inserting into the table it was just adding the first char.

user8766035 · Accepted Answer · 2017-10-18 10:05:57Z

Try this instead:

... set @sql = 'select * from jfa.[dbo].[MyTable] INNER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = ' + @TableName + ' and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2' ...

You need to concatenate the parameter value directly to the dynamic sql query, so use ...' + @TableName + ' ...

Collectives™ on Stack Overflow

Dynamic Query : How to define Table Name or other String in SQL-Server

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related