logo
down
shadow

SQl server Dynamic query


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 : facebook icon twitter icon
Sub query in dynamic query SQL Server 2008

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

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

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

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?

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;
Related Posts Related Posts :
  • How can I make the legend in an SSRS chart correspond with custom chart fill colors?
  • The MSDTC transaction manager was unable to push the transaction to the destination transaction manager due to communica
  • How to retrieve all rows with a date in this and the next week? SQL Server
  • Visual Studio + localdb on windows 10
  • pymssql : How to get distinct rows when a column has XML values
  • How to set up a Join according to a value for one Column?
  • How to insert specific datetime into dataset
  • If-else condition in visual basic to delete a record from SQL Server if record exist
  • Rolling calendar pivot in sql server?
  • Subtract variable from column while variable has remaining value
  • Error 0x80070005 Access is denied
  • Part of the row to columns
  • Sql Server Updating column by generating random number
  • Delete Rows not between Min and Max dates from another column
  • How select data where they have on second difference?
  • update the table with the previous column with out any loops
  • Output complex xml from SQL select query
  • Can not insert arabic data in SQL Server
  • Joining SQL Tables and display columns as rows to generate a report
  • Reduce Hash Match Cost in sql code
  • Data Insertion from one sql server to another
  • Return a Hourly minimum count of column?
  • SQL Query - Year by Year Growth of Sales
  • LINQ & SQL Server : order by from starting row
  • creating access to SQL Server Native Client OLE DB provider using CoCreateInstance fails with 80040154 error
  • SSIS SQL task Query creating error while casting parameters (string to Datetime)
  • run query against multiple databases on different server
  • Find all rows that have changed and write old-Newvalue to different table in SQL Server 2012
  • SQL Sum() Query error
  • How child record overrides parent record columns?
  • Calculate Type of the member in a table (Self Join/Case When or any other possible way)
  • Employee, Departments, and ManagerID Table Structures
  • Stored procedures in SQL Server 2012
  • How retrieve collection of objects from stored procedure which are in database connected with foreign key
  • Microsoft SQL Server 2014 no server
  • How to SUM up 2 Columns into 1 Column SQL Server
  • Best approach to merge three distinct TSQL select statments
  • How to display multiple data from the same table depending on one column value
  • How to create sequential numbering against two select statements that are unioned?
  • SQL Server : Second maximum value per group
  • T-SQL : xml how to add default namespace and root path with inner join
  • T-SQL - Using a parameter in where and clause
  • SSPI and SQL Server Windows Authentication
  • Do I NEED a subquery on this OVER example?
  • Check constraint to allow only one combination of two columns
  • How to id with foreign key in my table in SQL Server?
  • Unpivoting multiple columns displaying incorrect data and duplicates
  • Unable to store value in a variable
  • Wildcard in ContainsTable of full-text search
  • SQL count where between dates by month
  • Datatier applications not deploying filegroups on creation of database, only upgrade process
  • Azure SQL Server VM OR Azure Windows Server with SQL Server install
  • Convert from 4 digit Military time to Standard time
  • SQL inner join on Microsoft SQL server
  • Deny Read-Only Users to see DDL of a Stored Procedure MS SQL
  • How do I exclude rows when an incremental value starts over?
  • TSQL - compare adjacent rows
  • MSSQL DataBase Backup without a specific table
  • Find All Empty Tables Named MAP_ALERT In All Databases On Server
  • Ugly table query
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co