SQL Server: How to Share Data Between Stored Procedures

Erland Sommarskog has an excellent SQL Server article “How to Share Data Between Stored Procedures” tackling these two questions:

  • How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement?
  • How can I pass a table as a parameter from one stored procedure to another?

He discusses several methods, and points out their advantages and disadvantages. [Adding here so I remember where to find it in future!]

SSMS Table Column List Tip

On occasion, you will be at someone’s desk, looking over their shoulder when you suddenly notice something and exclaim “Hold on! How did you do that?” quickly followed by “I can’t believe I didn’t know about that!”.

I had just such an experience a few days ago. At the risk of being the last person to know this: in SQL Server Management Studio Object Explorer: open a Database, open the Tables folder, and open a Table. Left click on the Column list folder, and drag and drop to an open query window: you get a comma separated list of column names.

In the past, I had always right-clicked on the table, selected Script->Create To->New query window and then edited out the list of columns.  D’oh!

Reminder: Perth .NET User Group Meeting: Thurs 7th April, 5:30pm: LightSwitch- with Andrew Coates

Join us at the Perth .NET User Group Thurs, April 7th where Andrew Coates will demonstrate LightSwitch, a new product in the Visual Studio family aimed at developers who want to easily create business applications for the desktop or the cloud. LightSwitch simplifies the development process by letting you concentrate on the business logic, while LightSwitch handles the common tasks for you. In this demo-heavy session, you will see, end-to-end, how to build and deploy a data-centric business application using LightSwitch. We’ll also go beyond the basics of creating simple screens over data and demonstrate how to create screens with more advanced capabilities

  • TOPIC:    LightSwitch with Andrew Coates
  • DATE:     Thursday, April 7th, 5:30pm – 7:00pm
  • VENUE:   Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
  • COST:     Free. All welcome

Andrew Coates is a Developer Evangelist for Microsoft in Australia. Before joining Microsoft, Andrew was an independent consultant specialising in database applications using Microsoft technologies including SQL Server, Visual FoxPro and Microsoft Office. Andrew’s wide range of experience includes telecommunications, SMB inventory, dispatch and accounting systems, using both web and rich client front ends. He has developed and delivered class room training and has mentored a number of developers in Visual FoxPro and SQL Server technologies. Andrew has spoken at international conferences on Geographical Information Systems, health statistics and database maintenance and is a regular presenter at TechEd both locally and internationally. An MCPD, MCTS and MCSD, Andrew also holds a Masters degree in Civil Engineering. Andrew blogs at http://blogs.msdn.com/acoat

Useful Codeplex Libraries

I recently came across two useful codeplex libraries. The first enables you to create advanced Excel 2007/2010 spreadsheets without Office installed, EppPlus:

EPPlus is a .Net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

The second is a .NET wrapper of the ImageMagick image manipulation API, ImageMagick.NET

Quotes: When there is no…

When there is no wind … ROW!  – Latin Proverb

When there is no peril in the fight there is no glory in the triumph. – Pierre Corneille

When there is no enemy within, the enemies outside cannot hurt you. – African Proverb (often attributed to Winston Churchill)

Real love is a pilgrimage. It happens when there is no strategy, but it is very rare because most people are strategists. – Anita Brookner

Perfection is finally attained not when there is no longer anything to add, but when there is no longer anything to take away – Antoine de Saint Exupéry

Laws and principles are not for the times when there is no temptation: they are for such moments as this, when body and soul rise in mutiny against their rigour; stringent are they; inviolate they shall be. – Jane Eyre in Charlotte Bronte.

When there is no vision the people perish. – Franklin Delano Roosevelt (from his first inaugural address)

It is hard to apply oneself to study when there is no money to pay for food and lodging. – Zora Neale Hurston

Microsoft Virtual Academy

Microsoft (and just about everyone else) has a cloud focus these days, so it’s no surprise that Microsoft Learning has also ventured there. Last week saw the official debut of the Microsoft Virtual Academy.

The Microsoft Virtual Academy is targeted at the cloud, with self-paced courses for learning more about private and public clouds, to more specific coverage of Windows Azure and SQL Azure.

MVA requires membership, but it’s free. Check out the FAQ here to find out more and get started.

Binary Search: A Cautionary Tale!

Practically every developer knows what binary search is: a simple (indeed fundamental) searching algorithm which is an example of a natural divide-and-conquer strategy. Basically: starting with an already sorted array, compare the middle element of the array with the value we want to find. If the values are the same we are done, else if the array element is larger, repeat in the remaining lower half of the array, else if the array element is smaller, repeat in the remaining upper half of the array.

Unsurprisingly, binary search is an often used interview question (although perhaps less so, due to increasing complexity and the proliferation of programming frameworks; I must admit I’ve never personally been asked about it in an interview). It is a basic technique that you can reasonably expect every reasonable candidate to know and can be implemented in just a few lines of code.

Despite binary search’s simplicity, it is easy to implement it incorrectly!

Jon Bentley, in his book Programming Pearls (a programming classic), wrote that in a course he ran for professional programmers, he asked the participants to code binary search and found that 90% failed to implement it correctly. If you asked 100 programmers to write an implementation of binary search a large number of them would be incorrect and many of those those that apparently worked would actually contain subtle flaws. Indeed, many published implementations of binary search are wrong:

“a study reported that it is correctly implemented in only five out of twenty textbooks.”

Indeed, Jon Bentley’s own implementation of binary search, published in the ‘Writing Correct Programs’ chapter of Programming Pearls, contained a subtle bug that remained undetected for over twenty years. Here is an iterative C# implementation, adapted from Bentley’s pseudocode, that contains the bug (can you spot it?):

public static int BinarySearch(int[] sortedArray, int valueToFind)

{

int lower = 0;

int upper = sortedArray.Length – 1;

int m;

while (lower <= upper)

{

m = (lower + upper) / 2;

if (sortedArray[m] < valueToFind)

{

lower = m + 1;

}

else if (sortedArray[m] == valueToFind)

{

return m;

}

else

{

upper = m – 1;

}

}

return -1;

}

.

.

…Scroll down…

.

.

The bug is in the mid-point assignment

m = (lower + upper)/2 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

which performed as a direct sum could lead to an integer overflow. It should be replaced by the identically equivalent safe expression

m = lower + (upper – lower)/2

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This bug was highlighted in Chapter 1 of Algorithms for Interviews, IMO, a slightly misnamed book which could perhaps be better described as ‘Algorithms to make programmers think’, where the emphasis is on describing problems and getting the reader to attempt to solve them.

[Side Note: A quick check with Reflector shows that this is implemented correctly without the possible overflow flaw in .NET 4, in fact the developers have gone put of their way to make sure someone doesn’t introduce this bug by encapsulating the mid-point division in its own Method GetMedian()]