How to declare advanced SQL query in C#?

Question

I have a problem with getting result of this SQL query. It gives result by other tools, but I can't declare it in C#. I didn't have problems with easy queries without DECLARE, SET etc...

Query in SQL:

DECLARE @SelectCols nvarchar(MAX),
        @Cols nvarchar(MAX),
        @Query nvarchar(MAX),
        @MinDate date = '2020-03-01',
        @MaxDate date = '2020-03-07'

SET @SelectCols = STUFF((SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        ',ISNULL(' + QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))+',0) AS '+QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))
FROM    sys.all_objects a CROSS JOIN sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET     @Cols = STUFF((SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
                ',' + QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))
                FROM    sys.all_objects a CROSS JOIN sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @Query ='DECLARE @MinDate1 DATE = ''' + CAST(@MinDate AS varchar(20)) +'''
            DECLARE @MaxDate1 DATE = ''' +CAST(@MaxDate AS varchar(20)) + '''
            SELECT FIRMA,' + @SelectCols + ' FROM
            (
                SELECT Shortcut as FIRMA, cast(mg.data as date) as DATA, CAST(ABS(SUM(mg.wartoscWz)) as decimal(20,2)) as WART
                FROM HM.MG
                INNER JOIN SSCommon.STContractors STC ON MG.khid = STC.id
                WHERE MG.subtyp = 89
                AND MG.aktywny = 1
                AND MG.anulowany = 0
                AND MG.bufor = 0
                AND MG.kod like ''%PZ''
                AND MG.typ_dk <> ''SrT''
                AND MG.createdDate >= @MinDate1 and MG.createdDate < DATEADD(day, 1, @MaxDate1)
                Group by (Shortcut), mg.data
            ) DANE
            PIVOT
            (
            max(WART) FOR [DATA] IN ( ' +@cols+ ')
            ) p
            ORDER BY FIRMA
            '
Execute(@query)

This query is working, but my declaration in C# is not working and I cannot find bug or I made something wrong.

String sql = @"DECLARE @SelectCols nvarchar(MAX), @Cols nvarchar(MAX), @Query nvarchar(MAX), "
    + "@MinDate date = '" +parametra + "', "
    + "@MaxDate date = '" + parametrb + "' "
    + "SET @SelectCols = STUFF((SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) "
    + "',ISNULL(' + QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))+',0) AS '+QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20))) "
    + "FROM sys.all_objects a CROSS JOIN sys.all_objects b "
    + "FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') "
    + "SET @Cols = STUFF((SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) "
    + "',' + QUOTENAME(CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20))) "
    + "FROM sys.all_objects a CROSS JOIN sys.all_objects b "
    + "FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') "
    + "SET @Query ='DECLARE @MinDate1 DATE = ''' + CAST(@MinDate AS varchar(20)) +'''"
    + "DECLARE @MaxDate1 DATE = ''' +CAST(@MaxDate AS varchar(20)) + ''' "
    + "SELECT FIRMA,' + @SelectCols + ' FROM "
    + "( "
    + "SELECT Shortcut as FIRMA, cast(mg.data as date) as DATA, CAST(ABS(SUM(mg.wartoscWz)) as decimal(20, 2)) as WART "
    + "FROM HM.MG "
    + "INNER JOIN SSCommon.STContractors STC ON MG.khid = STC.id "
    + "WHERE MG.subtyp = 89 "
    + "AND MG.aktywny = 1 "
    + "AND MG.anulowany = 0 "
    + "AND MG.bufor = 0 "
    + "AND MG.kod like ''%PZ'' "
    + "AND MG.typ_dk <> ''SrT'' "
    + "AND MG.createdDate >= @MinDate1 and MG.createdDate < DATEADD(day, 1, @MaxDate1) "
    + "Group by (Shortcut), mg.data "
    + ") DANE "
    + "PIVOT "
    + "( "
    + "max(WART) FOR [DATA] IN ( ' +@cols+ ') "
    + ") p "
    + "ORDER BY FIRMA "
    + "Execute(@query)";

Which error do you get ? Anyway there is a single quote missing between ```ORDER BY FIRMA``` and ```Execute```. So your @Query string value is not closed — Olivier Depriester, Apr 06 '20 at 11:41
TBH it doesn't really make sense to use TSQL to build up the dynamic SQL query here anyway. It will be much less messy to do it in C# - [“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live”](https://stackoverflow.com/questions/876089/who-wrote-this-programing-saying-always-code-as-if-the-guy-who-ends-up-maintai) — Martin Smith, Apr 06 '20 at 11:43
@MartinSmith I'm making WebApp in ASP.NET MVC and asking user about parameters and execute results to Excel — Wiktor, Apr 06 '20 at 11:46
That doesn't change anything. `@SelectCols` and `@Cols` can be worked out in the C# side, no need to use SQL Server to calculate these. Then you are left with one query template to fill in on the C# side — Martin Smith, Apr 06 '20 at 11:46
`"@MinDate date = '" +parametra + "', "` Why are you decalring a variable and then injecting it's value. **Parametrise**; what you have there is a huge security flaw. — Thom A, Apr 06 '20 at 11:47
@Larnu I know about this parameters, i'm posting this parameters via HTTP Post, the problem was single quote missing between two last lines. — Wiktor, Apr 06 '20 at 11:50
If you know about parameters, why are you not parametrising, @Wiktor ? — Thom A, Apr 06 '20 at 11:51
Dynamic SQL _and_ not parameterizing the query? You will get hit by SQL injection. — alroc, Apr 06 '20 at 12:12
Advisory: both your SQL version and C# version are actively dangerous. You should **NOT** concatenate parameters to create TSQL; both C# and SQL allow you to parameterize queries. In C# this would be via the ADO.NET "parameters" collection (on via something like Dapper to make it easier); in TSQL this would be via `sp_executesql` using the varadic form that allows you to pass values *into* a parameterized query — Marc Gravell, Apr 06 '20 at 12:15

score 1 · Answer 1 · answered Apr 06 '20 at 14:01

As others have noted there was a syntax error. But that error was caused by embedding SQL in C# in the worst possible way. Just paste it into a multi-line verbatim string literal.

eg

        var sql = @"
DECLARE @SelectCols nvarchar(MAX),
        @Cols nvarchar(MAX),
        @Query nvarchar(MAX),
        @MinDate date = '2020-03-01',
        @MaxDate date = '2020-03-07'

SET @SelectCols = STUFF((SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        ',ISNULL(' + QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))+',0) AS '+QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))
FROM    sys.all_objects a CROSS JOIN sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET     @Cols = STUFF((SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
                ',' + QUOTENAME (CAST(DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)AS varchar(20)))
                FROM    sys.all_objects a CROSS JOIN sys.all_objects b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @Query ='DECLARE @MinDate1 DATE = ''' + CAST(@MinDate AS varchar(20)) +'''
            DECLARE @MaxDate1 DATE = ''' +CAST(@MaxDate AS varchar(20)) + '''
            SELECT FIRMA,' + @SelectCols + ' FROM
            (
                SELECT Shortcut as FIRMA, cast(mg.data as date) as DATA, CAST(ABS(SUM(mg.wartoscWz)) as decimal(20,2)) as WART
                FROM HM.MG
                INNER JOIN SSCommon.STContractors STC ON MG.khid = STC.id
                WHERE MG.subtyp = 89
                AND MG.aktywny = 1
                AND MG.anulowany = 0
                AND MG.bufor = 0
                AND MG.kod like ''%PZ''
                AND MG.typ_dk <> ''SrT''
                AND MG.createdDate >= @MinDate1 and MG.createdDate < DATEADD(day, 1, @MaxDate1)
                Group by (Shortcut), mg.data
            ) DANE
            PIVOT
            (
            max(WART) FOR [DATA] IN ( ' +@cols+ ')
            ) p
            ORDER BY FIRMA
            '
Execute(@query)
";

score 0 · Accepted Answer · answered Apr 06 '20 at 12:12

Your problem is a missed quote. You didn't spot this yourself as the code is an unreadable mess.

It doesn't really make sense to use TSQL to build up the dynamic SQL query here anyway. It will be much less messy to do it in C# as below

            DateTime minDate = DateTime.Parse("2020-03-01");
            DateTime maxDate = DateTime.Parse("2020-03-07");

            //array of strings in format "[2020-03-01]" covering whole date range
            var pivotColumns =
                Enumerable.Range(0, 1 + maxDate.Subtract(minDate).Days)
                .Select(offset => minDate.AddDays(offset).ToString("[yyyy-MM-dd]"))
                .ToArray();

            //enumerable of strings in format "ISNULL([2020-03-01], 0) AS [2020-03-01]"
            var selectColumns = pivotColumns.Select(pc => "ISNULL(" + pc + ", 0) AS " + pc);

            string query = @"
SELECT FIRMA,
       " + string.Join(",", selectColumns) + @"
FROM   (SELECT Shortcut                                       AS FIRMA,
               CAST(mg.data AS DATE)                          AS DATA,
               CAST(ABS(SUM(mg.wartoscWz)) AS DECIMAL(20, 2)) AS WART
        FROM   HM.MG
               INNER JOIN SSCommon.STContractors STC
                       ON MG.khid = STC.id
        WHERE  MG.subtyp = 89
               AND MG.aktywny = 1
               AND MG.anulowany = 0
               AND MG.bufor = 0
               AND MG.kod LIKE '%PZ'
               AND MG.typ_dk <> 'SrT'
               AND MG.createdDate >= @MinDate
               AND MG.createdDate < DATEADD(day, 1, @MaxDate)
        GROUP  BY ( Shortcut ),
                  mg.data) DANE
       PIVOT ( MAX(WART)
             FOR [DATA] IN ( " + string.Join(",", pivotColumns) + @") ) p
ORDER  BY FIRMA 
";

            //TODO: Execute query and pass @MinDate/@MaxDate as parameters

How to declare advanced SQL query in C#?

2 Answers2