Category: sql-server

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

But mostly I am just trying to understand recursive CTEs.

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

EDIT

For the record, I know a join would work but I had a reason for asking about recursion. To give you context, I am working on a result set that is an EXPORT file. Each row is only one column called LINE. Furthermore, the export file needs to output in the order of my example...details need to be under header rows and that pattern needs to repeat...header/details ... header/details. I thought that maybe recursion could solve this problem. But I know simple join could solve it too as long as I can get the rows into the correct order...header / detail ... header / detail, maybe by having some way to do a column for sorting that numbers the output in the right way.

My export will NOT be depending on the calling app to format the data...the stored procedure needs to format the data.

Nov 08 2016

Is there a way to aggregate a variable range of dates in SQL using a SET operation

I have a table like this one....

CREATE TABLE AbsentStudents
(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
)

This is a very large table that has 1 row for each student for each day that they were absent.

I have been asked to write a stored procedure that gets student absences by date range. What makes this query tricky is that I have to filter/aggregate by "absence episodes". The number of days that constitutes an "absence episode" is a procedure parameter so it can vary.

So for example, I need to get a list of students who were absent between 1/1/2016 to 1/17/2016 but only if they were absent for more than @Days (2 or 3 or whatever the parameter dictates) days.

I think that alone I could figure out. However, within the date range a student can have more than one "absence episode". So a student might have been absent for 3 days at the beginning of the date range, 2 days in the middle of the date range, and 4 days at the end of the date range and each of those constitutes a different "absence episodes". Assuming that my @Days parameter is 2, that should return 3 rows for that student. And, each returned row should calculate how many days the student was absent for that "absence episode."

So I would like my procedure require 3 parameters (@StartDate datetime,@EndDate datetime, @Days int) and return something like this...

StudentId, InitialAbsentDate, ConsecutiveDaysMissed

And ideally it would do this using a SET operation and avoid cursors. (Although cursors are fine if that is the only option.)

UPDATE (by Shnugo)

A test scenario

DECLARE @AbsentStudents TABLE(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
);
INSERT INTO @AbsentStudents VALUES
--student 1
 (1,{d'2016-10-01'}),(1,{d'2016-10-02'}),(1,{d'2016-10-03'}) --three days 
,(1,{d'2016-10-05'}) --one day
,(1,{d'2016-10-07'}),(1,{d'2016-10-08'}) --two days
--student 2
,(2,{d'2016-10-01'}),(2,{d'2016-10-02'}),(2,{d'2016-10-03'}),(2,{d'2016-10-04'}) --four days
,(2,{d'2016-10-08'}),(2,{d'2016-10-09'}),(2,{d'2016-10-10'}) --three days
,(2,{d'2016-10-12'}); --one day

DECLARE @startDate DATETIME={d'2016-10-01'};
DECLARE @endDate DATETIME={d'2016-10-31'};
DECLARE @Days INT = 3;
Nov 08 2016

Is there a way to aggregate a variable range of dates in SQL using a SET operation

I have a table like this one....

CREATE TABLE AbsentStudents
(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
)

This is a very large table that has 1 row for each student for each day that they were absent.

I have been asked to write a stored procedure that gets student absences by date range. What makes this query tricky is that I have to filter/aggregate by "absence episodes". The number of days that constitutes an "absence episode" is a procedure parameter so it can vary.

So for example, I need to get a list of students who were absent between 1/1/2016 to 1/17/2016 but only if they were absent for more than @Days (2 or 3 or whatever the parameter dictates) days.

I think that alone I could figure out. However, within the date range a student can have more than one "absence episode". So a student might have been absent for 3 days at the beginning of the date range, 2 days in the middle of the date range, and 4 days at the end of the date range and each of those constitutes a different "absence episodes". Assuming that my @Days parameter is 2, that should return 3 rows for that student. And, each returned row should calculate how many days the student was absent for that "absence episode."

So I would like my procedure require 3 parameters (@StartDate datetime,@EndDate datetime, @Days int) and return something like this...

StudentId, InitialAbsentDate, ConsecutiveDaysMissed

And ideally it would do this using a SET operation and avoid cursors. (Although cursors are fine if that is the only option.)

UPDATE (by Shnugo)

A test scenario

DECLARE @AbsentStudents TABLE(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
);
INSERT INTO @AbsentStudents VALUES
--student 1
 (1,{d'2016-10-01'}),(1,{d'2016-10-02'}),(1,{d'2016-10-03'}) --three days 
,(1,{d'2016-10-05'}) --one day
,(1,{d'2016-10-07'}),(1,{d'2016-10-08'}) --two days
--student 2
,(2,{d'2016-10-01'}),(2,{d'2016-10-02'}),(2,{d'2016-10-03'}),(2,{d'2016-10-04'}) --four days
,(2,{d'2016-10-08'}),(2,{d'2016-10-09'}),(2,{d'2016-10-10'}) --three days
,(2,{d'2016-10-12'}); --one day

DECLARE @startDate DATETIME={d'2016-10-01'};
DECLARE @endDate DATETIME={d'2016-10-31'};
DECLARE @Days INT = 3;
Nov 08 2016

Is there a way to aggregate a variable range of dates in SQL using a SET operation

I have a table like this one....

CREATE TABLE AbsentStudents
(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
)

This is a very large table that has 1 row for each student for each day that they were absent.

I have been asked to write a stored procedure that gets student absences by date range. What makes this query tricky is that I have to filter/aggregate by "absence episodes". The number of days that constitutes an "absence episode" is a procedure parameter so it can vary.

So for example, I need to get a list of students who were absent between 1/1/2016 to 1/17/2016 but only if they were absent for more than @Days (2 or 3 or whatever the parameter dictates) days.

I think that alone I could figure out. However, within the date range a student can have more than one "absence episode". So a student might have been absent for 3 days at the beginning of the date range, 2 days in the middle of the date range, and 4 days at the end of the date range and each of those constitutes a different "absence episodes". Assuming that my @Days parameter is 2, that should return 3 rows for that student. And, each returned row should calculate how many days the student was absent for that "absence episode."

So I would like my procedure require 3 parameters (@StartDate datetime,@EndDate datetime, @Days int) and return something like this...

StudentId, InitialAbsentDate, ConsecutiveDaysMissed

And ideally it would do this using a SET operation and avoid cursors. (Although cursors are fine if that is the only option.)

UPDATE (by Shnugo)

A test scenario

DECLARE @AbsentStudents TABLE(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
);
INSERT INTO @AbsentStudents VALUES
--student 1
 (1,{d'2016-10-01'}),(1,{d'2016-10-02'}),(1,{d'2016-10-03'}) --three days 
,(1,{d'2016-10-05'}) --one day
,(1,{d'2016-10-07'}),(1,{d'2016-10-08'}) --two days
--student 2
,(2,{d'2016-10-01'}),(2,{d'2016-10-02'}),(2,{d'2016-10-03'}),(2,{d'2016-10-04'}) --four days
,(2,{d'2016-10-08'}),(2,{d'2016-10-09'}),(2,{d'2016-10-10'}) --three days
,(2,{d'2016-10-12'}); --one day

DECLARE @startDate DATETIME={d'2016-10-01'};
DECLARE @endDate DATETIME={d'2016-10-31'};
DECLARE @Days INT = 3;
Oct 18 2016

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE?

Looking at other questions here on SO and google seems to indicate MS Sql Server does not guarantee to short circuit the expressions in the WHERE clause.

Does that mean that a WHERE expression like this this one cannot be trusted to work...?

... WHERE (@include_voided = 1 OR mytable.void = 0) AND ... 

I frequently use this kind of expression in my stored procedures. Having researched short-circuiting for another reason I am now wondering if this should be replaced with ...

... WHERE mytable.void = case when @include_voided=1 then mytable.void else 0 END

...as all of the articles seem to indicate that CASE statements are the only ones guaranteed to short-circuit.

I am hoping that the first expression is fine just because it is more readable and easier to type.

Oct 18 2016

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE? [duplicate]

This question already has an answer here:

Looking at other questions here on SO and google seems to indicate MS Sql Server does not guarantee to short circuit the expressions in the WHERE clause.

Does that mean that a WHERE expression like this this one cannot be trusted to work...?

... WHERE (@include_voided = 1 OR mytable.void = 0) AND ... 

I frequently use this kind of expression in my stored procedures. Having researched short-circuiting for another reason I am now wondering if this should be replaced with ...

... WHERE mytable.void = case when @include_voided=1 then mytable.void else 0 END

...as all of the articles seem to indicate that CASE statements are the only ones guaranteed to short-circuit.

I am hoping that the first expression is fine just because it is more readable and easier to type.

Oct 29 2013

Great article on SQL Command utility.

I thought I knew about the Sql Server sqlcmd utility … until I read this article…

https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/

One of the coolest things I learned is that DOS has an “if exist” command.

Seth

Jun 17 2013

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.

Jun 17 2013

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.