Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTEs in Report Creation Expand / Collapse
Author
Message
Posted Saturday, March 29, 2008 8:51 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
Comments posted to this topic are about the item CTEs in Report Creation

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #476657
Posted Monday, March 31, 2008 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 9, 2011 5:51 AM
Points: 21, Visits: 34
CTE is good and very useful, but it cannot work with dynamic query
Post #476849
Posted Monday, March 31, 2008 10:40 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
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/
Post #477117
Posted Monday, March 31, 2008 11:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 5,449, Visits: 1,401
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.

:)



Post #477416
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse