SQl server Dynamic query

» sql-server » SQl server Dynamic query

By : Tom Reed

Date : December 01 2020, 05:00 PM

To fix this issue I have a table Connection

code :

CREATE FUNCTION [dbo].[FN_SPLIT](@Long_str varchar(max),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID      inT IDENTITY PRIMARY KEY,      
    SPLIT   varchar(max)    
)    
AS   
BEGIN 
        DECLARE @sxml XML

        SET @sxml='<root><node>'+REPLACE(@Long_str,@split_str,'</node><node>')+'</node></root>'
        INSERT INTO @tmp([SPLIT])
        SELECT b.value('.','varchar(max)') FROM @sxml.nodes('root/node') AS s(b)


    RETURN     
END 
GO

DECLARE @sql as nvarchar(4000)
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
;WITH testdata AS 
(
select 'PRM' AS [Key],'Table1' AS Base_TBL,'Table2,Table3' AS  Connected_table,'colm1' AS Base_tbl_colmn,'Colm2,colm3' AS Connected_table_colmn
)

SELECT * INTO #temp FROM testdata

select @sql= 'select * from '+ Base_TBL +' ' +t.joinstring
FROM #temp
CROSS APPLY (
    SELECT STUFF((
 SELECT ' INNER JOIN '+ ft.[SPLIT] + ' ON ' + Base_TBL+'.'+Base_tbl_colmn +'='+ft.[SPLIT]+'.'+fc.[SPLIT]
FROM 
dbo.FN_SPLIT(Connected_table,',') AS ft 
INNER JOIN dbo.FN_SPLIT(Connected_table_colmn,',') AS fc ON fc.ID=ft.ID
FOR XML PATH('')),1,1,'') AS joinstring
) AS t
SELECT @sql

SELECT STUFF((
        select  '  select * from '+ Base_TBL +' ' +t.joinstring 
        FROM #temp
        CROSS APPLY (
            SELECT STUFF((
         SELECT ' INNER JOIN '+ ft.[SPLIT] + ' ON ' + Base_TBL+'.'+Base_tbl_colmn +'='+ft.[SPLIT]+'.'+fc.[SPLIT]
        FROM 
        dbo.FN_SPLIT(Connected_table,',') AS ft 
        INNER JOIN dbo.FN_SPLIT(Connected_table_colmn,',') AS fc ON fc.ID=ft.ID
        FOR XML PATH('')),1,1,'') AS joinstring
        ) AS t FOR XML PATH('')
),1,1,'')

Share :

Sub query in dynamic query SQL Server 2008

By : user3722540

Date : March 29 2020, 07:55 AM

will help you The following select statement is not working if it placed inside the dynamic query. It works fine if it moved out of dynamic query , use this instead (one SELECT is missing thus the error message):

code :

'SELECT * FROM
        (SELECT [CaseID],
            ( select 
                Attr.AttributeValue 
                from 
                [dbo].[CV_CaseAttributes] Attr
                Where Attr.CaseID = C.CaseID ANd Attr.AttributeID = @AttributeID
            ) AS CaseTitle
            ,[UserID]           
        FROM [dbo].[CaseMaster] C
        WHERE SpaceID =  @sid
        )
    AS Details'

DECLARE @sidV UNIQUEIDENTIFIER , @AttributeIDV UNIQUEIDENTIFIER
SET @sidV = 'c0b5956b-47f2-4ad6-bb9a-67a5a249e4b7'
SET @AttributeIDV = 'F1A0D9D6-702E-4492-9EBC-63AD22E60E6A'

DECLARE @sql NVARCHAR(MAX) 
SET @sql = 'SELECT * FROM
        (select [CaseID],
            ( select 
                Attr.AttributeValue 
                from 
                [dbo].[CV_CaseAttributes] Attr
                Where Attr.CaseID = C.CaseID ANd Attr.AttributeID = @AttributeID
            ) AS CaseTitle
            ,[UserID]           
        FROM [dbo].[CaseMaster] C
        WHERE SpaceID =  @sid
        )
    AS Details'

    EXEC sp_executesql @sql
                  ,N'@sid UNIQUEIDENTIFIER,@AttributeID UNIQUEIDENTIFIER'
                  ,@sid=@sidV,@AttributeID =@AttributeIDV

SQL Server dynamic query -- cannot locate linked server

By : scottdlc

Date : March 29 2020, 07:55 AM

will help you As per my thinking and test it only allow parameter in query part like in where and other condition.
Try this way.

code :

declare @node int = 9044;

DECLARE @sqlCommand NVARCHAR(MAX) = 
(
    'SELECT * FROM [@node].[database_name].dbo.table_name'
);

DECLARE @paramList NVARCHAR(400) =
(
    '@node int'
)


SET @sqlCommand  = REPLACE(@sqlCommand , '@node',@node)
exec sp_executesql @sqlCommand, @paramlist, @node;

Invalid Column Name errors in dynamic query. Works in SQL Server 2008 but not in SQL Server 2012

By : abhinav tripathi

Date : March 29 2020, 07:55 AM

like below fixes the issue I'm in the middle of going from SQL Server 2008 (not R2) to SQL Server 2012. I've restored my database into SQL Server 2012 but I'm getting runtime errors trying to call a certain dynamic query. , The problem turned out to be with the Order By part of the query.

code :

-- Determine Sorting
If @orderBy = 'Desc'
    Select @orderLit = 'p.ProductDesc'
Else if @orderBy = 'Price'  
    Select @orderLit = 'p.CustPrice'
Else
    Select @orderLit = 'p.ProductID'

-- Determine Grouping
If @grouping = 1
    Select @sql = @sql + ' ORDER BY p.Vendor, p.Type, p.SubType, ' + @orderLit
Else
    Select @sql = @sql + ' ORDER BY ' + @orderLit

-- Determine Sorting
If @orderBy = 'Desc'
    Select @orderLit = 'ProductDesc'
Else if @orderBy = 'Price'  
    Select @orderLit = 'CustPrice'
Else
    Select @orderLit = 'ProductID'

-- Determine Grouping
If @grouping = 1
    Select @sql = @sql + ' ORDER BY Vendor, Type, SubType, ' + @orderLit
Else
    Select @sql = @sql + ' ORDER BY ' + @orderLit

Convert SQL Server query to C# dynamic query fails

By : Filphrench

Date : March 29 2020, 07:55 AM

wish help you to fix your issue Finally got it to work after running a subset of the string query generated just before execution then adding more each time and comparing the output with the original query.
I'll post the final result here if anyone get the same problem and need to figure it out on their own query.

code :

  string query = "";

  query += "declare @lookingFor nvarchar(36);";

  // Search for the UID of the InputKey associated to the Data Input we want to query the status for
  query += string.Format(" select @lookingFor = convert(nvarchar(36), (select top 1(UID) from StudyInput where {0} = @id));", this._config.InputKey);
  query += " if (@lookingFor is null) return;";

  query += " DECLARE @query NVARCHAR(MAX);";

  // Build a dynamic query to get the status of the data for each step it has been involved with
  // Starting with StudyInput
  query += " set @query = ''select null as InUID, OutUID, InNext, Status, ''''StudyInput'''' as TableName from StudyInput_InOut where OutUID = '''''' + @lookingFor + '''''''';";

  // Get all the direct links involving the Data
  // And build a dynamic query for each Step table
  query += " SELECT @query = COALESCE(@query + '' union all select InUID, OutUID, InNext, Status, '''''' + stepName + '''''' as TableName from '' + stepName + ''_InOut where OutUID = '''''' + convert(nvarchar(36), ProcessUID) + '''''''', '''')";
  query += " FROM StudyInput_DirectLink";
  query += " where InputUID = @lookingFor;";

  // Execute the dynamic query
  query += " EXECUTE sp_executesql @query;";

How to create dynamic query to connect different server database in SQL Server?

By : user1812374

Date : March 29 2020, 07:55 AM

wish helps you You can't provide a variable to replace the name of an object. For example, the following won't work:

code :

DECLARE @o nvarchar(255) = N'sys.objects';

SELECT *
FROM @o;

DECLARE @s sysname N'dbo', @t sysname = N'objects';
DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@s) + N'.' + QUOTENAME(@o) + N';';
EXEC sp_executesql @SQL;

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT d1.[Name]' + NCHAR(10) +
           N'FROM ' + @Server1IP + N'[Details] d1' + NCHAR(10) +
           N'WHERE NOT EXISTS (SELECT 1' + NCHAR(10) +
           N'                  FROM ' + @Server2IP + N'[Details] d2' + NCHAR(10) +
           N'                  WHERE d1.ID = d2.ID);';
PRINT @SQL; --Your best friend
EXEC sp_executeSQL @SQL;