CTEs in Report Creation

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Comments posted to this topic are about the item CTEs in Report Creation

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • seema.maheshgauri

    Valued Member

    Points: 73

    CTE is good and very useful, but it cannot work with dynamic query

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Seema;

    You will find that CTE's work quite well within dynamic sql.

    Here is an example that can be used from any database in SQL Server 2005. It is not actually useful at all, but it demonstrates the concept:

    declare @Sql varchar(8000)

    set @Sql = '

    with CTE1 as

    (

    select * from information_schema.columns

    )

    select * from CTE1

    '

    exec (@sql)

    Now, what you may be thinking of is the fact you cannot define the CTE from outside of the dynamic SQL variable and then use it within the dynamic SQL call, for instance:

    --Note this does not work

    declare @Sql varchar(8000);

    with CTE1 as

    (

    select * from information_schema.columns

    )

    set @Sql = '

    select * from CTE1

    '

    exec (@sql)

    Gives the error that there is incorrect syntax near set. Keep in mind that a CTE is considered part of its accompanying SELECT, INSERT, UPDATE, or DELETE statement and cannot exist on its own.

    You can consider either putting the CTE inside of the dynamic sql variable along with its main statement or else look at creating a view. Keep in mind that if for some reason you do not want the view to be persistent you can simply drop it immediately after it is used.

    Of course, at the risk of getting off topic, always be careful to ensure you need dynamic SQL. As Erland Sommarskog has point out, dynamic SQl is both a great blessing and a great curse. http://www.sommarskog.se/dynamic_sql.html

    Edited at 1600 to fix a typo.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Anipaul

    SSC-Insane

    Points: 24681

    Hi

    As per microsoft:

    A CTE can be used to:

    * Create a recursive query.

    * Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

    * Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

    * Reference the resulting table multiple times in the same statement.

    Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

    CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

    🙂

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply