How to represent dynamic query in SQL Server view-CodePudding

I have the following SQL code:

DECLARE @i INT = 1;
DECLARE @sql_code varchar(max) = '';
DECLARE @repeats INT = 4;

WHILE @i <= @repeats

BEGIN
    SET @sql_code = @sql_code 'SELECT ''foo' cast(@i as varchar) ''' as bar UNION ALL '
    
    SET @i = @i   1
END;

SET @sql_code = LEFT(@sql_code,LEN(@sql_code) - 10)

exec (@sql_code)

,which when run in SSMS produces this:

bar
----
foo1
foo2
foo3
foo4

How can I reproduce the same result as view (dynamically)?

I know you can't use declarations in view, but could it be done through stored procedure or function?

CodePudding user response：

You can't use dynamic sql inside a view. But yes you can create table valued User-Defined functions as mentioned in this post.

Link to the post: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3cdeda6c-af19-46e9-b89f-e575fecd475b/dynamic-query-in-view?forum=transactsql

Answer by Gavin Campbell should give you the idea of what can be done.

Note : For more information on Table valued User-Defined Functions: Visit this documentation: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191165(v=sql.105)?redirectedfrom=MSDN

CodePudding user response：

Actually, despite what Utsav's good answer says, you can do anything if you put your mind to it.