SqlSever: Multiple CTE in EXECUTE SP_EXECUTESQL

Question

    DECLARE @sqlString nvarchar(500); SET @sqlString = ';WITH Data_CTE 
        AS
        (
            SELECT pj.ProjectID, pj.ProjectName, pj.ProjectOwner, cs.CustomerName
            FROM Projects as pj
            LEFT OUTER JOIN [Customers] cs
                ON pj.CustomerId = cs.CustomerID
            WHERE  pj.ProjectOwner =  @ename 
                AND (pj.ProjectStatus = 1 OR pj.ProjectStatus = 0)
        )
        SELECT *, (SELECT COUNT(*) FROM Data_CTE)  AS TotalRows
        FROM Data_CTE
        ORDER BY ProjectName
        OFFSET 0 * 10 ROWS
        FETCH NEXT 10 ROWS ONLY;'


SET @ParmDefinition = N'@ename varchar(100)';
SET @ename = 'mohapam@test.com';

EXECUTE SP_EXECUTESQL @sqlString, @ParmDefinition, @ename = @ename;

Here I am trying to fetch no of records based on paging and get total no of count as well. When I am executing above query it show error at syntax 'COU.

Please suggest any other way to get records with total count.

Where's your declaration of `@sqlString`? It should be `NVARCHAR(MAX)`, but the error message strongly suggests it's getting cut off. — Jeroen Mostert, Jul 26 '19 at 09:26
Why are you using `sp_executesql` at all? You've tagged dynamic SQL, but that statement isn't dynamic. — Thom A, Jul 26 '19 at 09:26
Nice work on the crystal ball @JeroenMostert :) Manas, if you use `PRINT @sqlString;` notice the value has been truncated. The first parameter for `sp_executesql` is `nvarchar(MAX)`. But, as discussed, it doesn't even need `sp_executesql`. — Thom A, Jul 26 '19 at 09:37
https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/ — Manas Kumar, Jul 26 '19 at 09:41
That comparison is between `EXEC(@SQL)` and `EXEC sp_executesql @SQL` @ManasKumar. That doesn't explain why you're using `sp_executesql` when there's nothing dynamic in your statement. You don't need `sp_executesql`. — Thom A, Jul 26 '19 at 09:45
@Larnu, the issue is it was truncating the sqlString. Thank you — Manas Kumar, Jul 26 '19 at 11:50

score 1 · Answer 1 · answered Jul 26 '19 at 10:00

As mentioned, there's no need for sp_executesql here at all. Dynamic SQL is when you have an object that is dynamic (as the name suggests). As a simply example you can't have something like:

DECLARE @TableName sysname = N'MyTable',
        @Name varchar(50) = 'Jane Smith';

SELECT *
FROM @TableName
WHERE [Name] = @Name;

This would return an error stating that the table variable @TableName hasn't been declared. Instead you would do the following:

DECLARE @TableName sysname = N'MyTable',
        @Name varchar(50) = 'Jane Smith';

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N'WHERE [Name] = @Name;';

EXEC sp_executesql @SQL, N'@name varchar(50)', @Name;

In your statement, however, you have no dynamic objects; we can easily see this as you don't inject any object names into the query.

Also, we can change the subquery (SELECT COUNT(*) FROM Data_CTE) to a windowed function, saving an extra can on the tables. This gives a final query below:

DECLARE @email varchar(100) = 'mohapam@test.com';

WITH Data_CTE AS
    (SELECT pj.ProjectID,
            pj.ProjectName,
            pj.ProjectOwner,
            cs.CustomerName,
            COUNT(*) OVER () AS TotalRows
     FROM Projects pj
          LEFT OUTER JOIN [Customers] cs ON pj.CustomerId = cs.CustomerID
     WHERE pj.ProjectOwner = @ename
       AND (pj.ProjectStatus = 1
         OR pj.ProjectStatus = 0))
SELECT ProjectID,
       ProjectName,
       ProjectOwner,
       CustomerName,
       DC.TotalRows
FROM Data_CTE DC
ORDER BY ProjectName OFFSET 0 * 10 ROWS FETCH NEXT 10 ROWS ONLY;

As for why you were getting the error you were, if you look at the length of the literal string in your question, it's over 500 characters, yet you stated you defined @sqlString as a varchar(500). As a result the value is truncated, and so the SQL doesn't work.

score 0 · Answer 2 · answered Jul 26 '19 at 09:31

0

You can use OVER clause for getting total count of rows. You don't need to make it complex with CTE. Use your query like below.

SELECT pj.ProjectID, pj.ProjectName, pj.ProjectOwner, cs.CustomerName, COUNT(*) OVER() AS TotalRows
FROM Projects as pj
LEFT OUTER JOIN [Customers] cs
    ON pj.CustomerId = cs.CustomerID
WHERE  pj.ProjectOwner =  @ename 
    AND (pj.ProjectStatus = 1 OR pj.ProjectStatus = 0
ORDER BY pj.ProjectName
OFFSET 0 * 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Click here for more details about OVER Clause

answered Jul 26 '19 at 09:31

Karan

12,059
3
24
40

Yes, that will work. However, it more performance cost as compared to CTE. So I used CTE instead of OVER() – Manas Kumar Jul 26 '19 at 09:38
I'd be very surprised your method (performing a count of the rows defined in the CTE) will be a lower cost. The method you have will require 2 scans of the objects `Customers` and `Projects`; this method won't – Thom A Jul 26 '19 at 09:46
@Larnu, I have tested and found that `COUNT(*)` will give total number of rows which will be returned from `WHERE` condition. `OFFSET` doesn't affect the `COUNT(*)`. – Karan Jul 26 '19 at 10:36

SqlSever: Multiple CTE in EXECUTE SP_EXECUTESQL

2 Answers2