Execute Dynamic query inside Select query in stored procedure SQL Server

Question

I am trying to execute dynamic generated select query as a column value of Select statement. I tried using functions but that don't allow to execute dynamic query block.

Can anyone have way around to get me the desired solution?

create table #temp(column1 nvarchar(10), column2 nvarchar(10), column3 nvarchar(10));
insert into #temp exec fetch_some_data;

select 
columnX, 
columnY,
(select COALESCE('column3', null) from #temp a where coalesce('a.column1', null) = 'somevalue') as columnZ
from TableName

P.S.: This columns of #temp are not fixed, they are created dynamically from reading csv. Just for explanation they are added

"This columns of #temp are not fixed" <-- could you elaborate? The names of the columns can be different but also the number of columns in #temp can vary? eg. if you fetch Address.csv then #temp(Name, Address, City) and if you fetch Person.csv then #temp(Fname, Lname, Age, Email)? Or is it that the number of columns of #temp is always the same (i.e 3) an only the column names differ for each csv? It would be very helpful If you could provide a more detailed example, for two different csv files and the corresponding sql that needs to be executed. — lptr, Dec 15 '19 at 11:25
@lptr "This columns of #temp are not fixed"- This means the csv will be having different number of columns and name won't be fix. — Akshay, Dec 16 '19 at 06:05
thanks for the info. Could you provide more details on the query? (select COALESCE('column3', null) from #temp a where coalesce('a.column1', null) = 'somevalue') as columnZ ? you want to use all columns of #temp or only the columns at position 1 and 3? Just write the query when #temp has col1, col2, col3, col4, col5. A bit more of explanation is needed. — lptr, Dec 16 '19 at 11:30

lptr · Answer 1 · 2019-12-14T08:42:52.050

I tried using functions but that don't allow to execute dynamic query block.

You are on the right track. You can not use a normal tsql function (exec query limitation) but you could use a scalar clr function.

How-to

Check the example of the clr function at https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-functions/clr-scalar-valued-functions?view=sql-server-ver15#example-of-a-clr-scalar-valued-function

for executing a dynamic statement, the sample code could be:

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static object ExecDynamicSql(string sql)
    {
        using (SqlConnection conn
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            return cmd.ExecuteScalar();
        }
    }
}

compile it to a dll (either with visual studio or csc.exe)

then in ssms:

CREATE ASSEMBLY DynamicSqlAssembly   
FROM 'C:\Users\..xyz...\Documents\Visual Studio xzy\Projects\xyz\xyz\bin\Debug\xyzname.dll'  --<-- path to dll here
WITH PERMISSION_SET = SAFE; 
GO

CREATE FUNCTION dbo.FnExecSql(@query nvarchar(max)) 
RETURNS sql_variant
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DynamicSqlAssembly.T.ExecDynamicSql;   
GO 


select dbo.FnExecSql('select getdate()');

select name, dbo.FnExecSql('select count(*) from ' + name) as tablecount
from sys.tables;

For completeness, you might want to add exception handling to the clr code and possibly return null when an exception occurs.It depends on your requirements.

Execute Dynamic query inside Select query in stored procedure SQL Server

1 Answers1