Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTEs in Report Creation


CTEs in Report Creation

Author
Message
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 Visits: 920
Comments posted to this topic are about the item CTEs in Report Creation

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
seema.maheshgauri
seema.maheshgauri
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 34
CTE is good and very useful, but it cannot work with dynamic query
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 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/
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6263 Visits: 1407
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.

Smile



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search