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

1

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

edited Oct 18, 2017 at 10:18

answered Oct 18, 2017 at 10:09

Rokuto

8141 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Katherine99 Over a year ago

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

1

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 + ' ...

answered Oct 18, 2017 at 10:05

user8766035

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related