Last night I was writing a simple T-SQL program something like this DECLARE @ROLEID AS INT SELECT @ROLEID = [ROLE ID] FROM TBLROLE ;WITH CTE AS ( SELECT * FROM SOMETABLE ) IF (@ROLEID = 1) BEGIN //SOMECODE END ELSE IF(@ROLEID = 2) BEGIN //SOMECODE END ELSE BEGIN //SOMECODE END I found after compilation th...
I have a table of values like this: CREATE TABLE ( Name1 VARCHAR (50), Name2 VARCHAR (50), Sequence INT ) In this table I have rows like this 'Bob', 'Jones', 1 'James','Ant', 2 I want the best way to UPDATE (UPDATE SET) sequence based on the order of say the Name2 column, so when re-sequenced the values are: 'James','...
My stored procedure looks like: WITH MYCTE(....) AS ( ... ) UPDATE ... (using my CTE) DELETE ( using my CTE) <--- says the object, my CTE, doesn't exist Can I only use it once? ...
Does anyone know if SQL Azure supports CTE syntax? Specifically; WITH, OVER, ROW_NUMBER(), PARTITION BY ...
Expanding on following question (Multiple Select Statement) I would like to know if I can do following: WITH cte1 as ( SELECT * from cdr.Location ), cte2 as ( SELECT * from cdr.Location WHERE cdr.Location.someField = cte1.SomeField ) select * from cte1 union select * from cte2 So accent here is on ...
With a table of the following structure and sample data: TableActivity ------------- Type VARCHAR(8) Activity VARCHAR(8) RelatedActivity VARCHAR(8) Type Activity RelatedActivity ------------------------------------------ Start a - Transfer a b Start b ...
DECLARE @UDFLabel1 VARCHAR(50), @UDFLabel2 VARCHAR(50), @UDFLabel3 VARCHAR(50), @UDFLabel4 VARCHAR(50), @UDFLabel5 VARCHAR(50) SELECT @UDFLabel1 = UserDefinedFieldName01, @UDFLabel2 = UserDefinedFieldName02, @UDFLabel3 = UserDefinedFieldName03, @UDFLabel4 = UserDefinedFieldName04, ...
I am using a Common Table Expression for paging: with query as ( Select Row_Number() over (Order By OrderNum ASC) as TableRowNum, FirstName, LastName From Users ) Select * from query where TableRowNum between 1 and 25 Order By TableRowNum ASC Immediately after making this query, I make make an almost identical ...
As we all know, CTEs were introduced in SQL Server 2005 and the crowd went wild. I have a case where I'm inserting a whole lot of static data into a table. What I want to know is which of the following is faster and what other factors I should be aware of. INSERT INTO MyTable (MyField) VALUES ('Hello') INSERT INTO MyTable (MyField) VAL...
I am executing ;with cte as ( select 1 as rn, 'name1' as nm union all select rn+1,nm = 'name' + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte and is receiving the error Msg 240, Level 16, State 1, Line 2 Types don't match between the anchor and the recursive part in column "nm" of recursive query "cte". Wh...
I have a requirement to turn a table into XML with counts for values. e.g. Table Id , Type, AnotherType 1, This, Widget 2, This, Fimble 3, That, Widget I want the output something like this. This needs to be dynamic so new columns or facets added would generate the this type of output. <matrix name="Type"> <facet name = "this" co...
Hi, I have got simple query WITH conn_cte ( ParentCategoryId, CategoryId, IdsPath ) AS ( SELECT ParentCategoryId ,CategoryId ,CAST(ParentCategoryId AS varchar(1000)) FROM Ind_CategoriesConnections WHERE ParentCategoryId = 0 UNION ALL S...
Trying to write a recursive CTE query, keep getting the following error: level, invalid column name This is the query, where am I going wrong? WITH OwnerHierarchy AS ( SELECT PairID, ChildID, ParentID, 0 AS level FROM BusinessHierarchy UNION ALL SELECT e.PairID, e.ChildID, ...
I am trying to write a recursive CTE query in SQL Server 2005 but am getting an odd set of results. My table is: PairID ChildID ParentID 900 1 2 901 2 3 902 3 4 This is my CTE Query: WITH TESTER (PairID, ChildID, ParentID, Level...
Lets say you have a table as such (SQL SERVER 2005) pairID childID parentID 0 1 2 1 2 3 2 3 4 And you have a CTE that return this dataset: pairID childID parentID level 0 1 2 2 1 2 3 1 2 3 4 0 How do you save the initial child ID so that yo...
Here's a brain-twister for the SQL guys - can anyone think of a reason why the first of these functions performs fine, and the second one runs dog-slow? Function A - Typically finishes in ~5 ms CREATE FUNCTION dbo.GoodFunction ( @IDs UniqueIntTable READONLY ) RETURNS TABLE AS RETURN SELECT p.ID, p.Node, p.Name, p.Level FROM...
Say I have a table of items representing a tree-like structured data, and I would like to continuously tracing upward until I get to the top node, marked by a parent_id of NULL. What would my MS SQL CTE (common table expression) look like? For example, if I were to get the path to get to the top from Bender, it would look like Comedy ...
I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total number of records that match the query before I page the result set. with recs as (select *, row_number() over (order by id) as rownum from ......) select * from recs where rownum between @a and @...
I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event. My solution to this is to add a CTE that groups scheduled events and counts ...
I'd like (using cte) to count children in table in that way to have at parent level number of all children including theirs children. Is there any sample available? ...