T-SQL dynamic SQL [duplicate]

Question

I need a select from a dynamic database in a view, so no declare could be done.

select * from (select db_table from systables where id = 6)

The result of select db_table from systables where id = 6 is my table e.g. table16.

So the above query should be like: select * from table16

I already tried to concat the query to get a varchar but then I get the error at "concat", cause it isn't a object.

Any ideas?

Why can't the table name be hard coded? Seems like an XY question — Zohar Peled
– Zohar Peled, Commented Oct 18, 2023 at 12:59
@ZoharPeled cause I need then several querys, with this I could just use one and the ids I already have in my query — Qhiliqq
– Qhiliqq, Commented Oct 18, 2023 at 13:02
Another question - What's the point of having a view that just do select *, without even filtering the data? Why not query the table(s) directly? — Zohar Peled
– Zohar Peled, Commented Oct 18, 2023 at 13:04
for views, the only way is to do a big UNION ALL from all your tables and then do some sort of filtering on the outside. This is usually a really bad idea, so i think you should re-think your life decisions that lead to this solution — siggemannen
– siggemannen, Commented Oct 18, 2023 at 13:06

Slava Rozhnev · Accepted Answer · 2023-10-18 13:12:26Z

0

Dynamic SQL allowed in Stored Procedures, so you can write one

CREATE PROCEDURE getTable
@tableid int
AS
BEGIN
    DECLARE @tblname sysname
    DECLARE @sql varchar(500)

    SELECT @tblname = name FROM sys.tables WHERE object_id = @tableid
    SET @sql = 'SELECT * FROM '+ @tblname
 
    EXEC (@sql)
END
Go

execute getTable 901578250

Test code here

edited Oct 18, 2023 at 13:12

answered Oct 18, 2023 at 12:56

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

5 Comments

GuidoG Over a year ago

Are you sure of this, is this in the documentation ?

Slava Rozhnev Over a year ago

@GuidoG, looks yourigth, not only in stored procedures

Zohar Peled Over a year ago

Don't use varchar(64) for table name. SQL Server use sysname for all its identifiers, you should use that data type as well. sysname is functionally equivalent to a non-nullable nvarchar(128)

GuidoG Over a year ago

It seems the OP wants to use this on views, have you tested this with views ?

Thom A Over a year ago

If you are injecting object names you really need to properly quote them (with QUOTENAME) and should schema qualify. Not to mentioned, as well, the bad habit of using EXEC(@SQL). Such statements cannot be parametrised, which promote bad habits that result in security flaws like SQL injection. If you need to run a statement that is within a variable or literal string then use sys.sp_executesql. Then you can easily parametrise the statement if you need to.

Collectives™ on Stack Overflow

T-SQL dynamic SQL [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related