Getting Parameters Out From a Stored Procedure–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the lesser used and known features of T-SQL are the output parameters from a stored procedure. I used one of these recently, so I wanted to blog about it.

Getting a String

I was working on part of the 2018 Advent of Code, which is a great set of programming exercises for anyone. I typically build a procedure to solve each puzzle, since that’s a common way of capturing code. If there’s a single numeric result, a RETURN code works fine.

In one puzzle, I needed to return a string. If you try this in a procedure, it won’t work.

2018-12-27 10_04_01-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

Instead, I need another solution. I could certainly SELECT back my string, but in this case, I wanted to have this assigned to a variable. I could do that in a few ways, but decided the easiest was an OUTPUT parameter.

To add an output parameter to my procedure, I first add my variable as a regular parameter.

   @s VARCHAR(10)
     SELECT @s = 'Some Code'

Next, I add the OUTPUT keyword after the type.

   @s VARCHAR(10) = '' OUTPUT
     SELECT @s = 'Some Code'

My call to the procedure should also include the OUTPUT keyword.

DECLARE @result VARCHAR(10);
EXEC dbo.StringTest @s = @result OUTPUT;
SELECT @result;

This works fine, allowing me to pass some value back to the caller.

2018-12-27 10_08_40-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

Not something I use often, but if I need to get some singular value back, this works.


This post was started at 10:00am one morning. I got back to this sentence at 10:09. That was the entire setup of the code, capturing screen shots, and writing the post. Easy for you to do as well.

Give this a try. How would you use an OUTPUT parameter?

Posted in Blog | Tagged , , | Leave a comment

Fun with Dates and Times

The other day I got a Github issue that asked if we here at SQLServerCentral should set our dates as YYYYMMDD in the new SQLServerCentral site. The issue noted that there were some inconsistent dates. Article dates are in a similar format, YYYY/MM/DD, but we do have some DD MMM YYYY and a few places where I think the American MMBBYYYY has lived on.

In any case, I made a joke on Twitter of asking if people were alright moving to YYYYMMDD. Most people were in favor, though someone asked about MMMM DD, YYYY, which is easily read. I have to say that I somewhat like that for reading, but as data, as something I’d work with, I prefer having the year month day format. I do want separators, and while I grew up using slashes ( as in YYYY/MM/DD), I know the ISO format is dashes (YYYY-MM-DD). ISO 8601 has dashes, so I decided to add that to the GitHub issue as a way of formatting.

What about using month names? As I look through various sites, I find inconsistencies. Some use dates, like Jan 11, 2019. Others might use 11 Jan 2019 or 2019-01-11. I find that MSDN uses regional settings, so the en-us version of a page will have 01/11/2019, but if I change to the en-Gb version, I see 11/01/2019. That seems find as a UI enhancement, but I hope that the actual value is stored as 2019-01-11. We don’t need them time

Or do we? There are times involved. While most of us might not care about whether something was published at 8am or 9am, we might care about where it was published and the time. Certainly 8am in Christchurch if far different from 8am in Honolulu. Do we store time zones? Or keep all dates in one? The logical thing is to keep dates in UTC and then adjust based on the client settings to display something that matches their time zone. We’ve been storing data in UTC, though the server time has been the Mountain time zone in the US for historical reasons. I don’t like that dependency, so I’m sure we’ll change that.

Dates and times are both simple and not simple. There are decisions to be made, and far too many of us take them lightly. While the display is of secondary importance, it’s likely that we’ll often use this data in other systems, as a way of marking a change, or even as a way of ordering our data. Having consistent and known date time formats is important as a data professional. I hope you pay attention to how you gather and store this data.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

Measuring Effectiveness

I’ve never been the guy that produced code the fastest, or the most code in a day. I haven’t been able to show that I work the most hours, or that I even keep great hours. I’m often late or erratic in the times I come and go from work. I don’t show that I am closing tickets in consistent times as I often will be slow to close new issues. I haven’t ever been the employee of the month.

What I have been is effective. I get things done. I leave the organization and the job better than when I found it. I work to deliver value for the salary I’m paid. That isn’t something you can easily measure with many traditional metrics that people use, but there are ways to decide that I’m a good employee. It’s a little ephemeral and sometimes uncomfortable to have an employee that doesn’t seem to fit the same model as most other employees, but that doesn’t mean you wouldn’t want me working on your staff.

This article on measuring DevOps tries to explain how you can determine if your DevOps process is working, which is a similar ephemeral way of working. When we implement DevOps, we don’t have large project plans, we don’t look forward to completing a system. We undertake a set of work, knowing that we don’t have an end date. We just keep doing work and getting things done that are needed. In this case, how can we measure if a project was on time, on budget, and finished?

I’d argue, as would the article, that those aren’t good measurements. Those are the ways we would traditionally look at work, but in software, those aren’t often the things we want to look at. If we deliver software that meets some requirements, but users struggle to use it or complain, is it done? Most management would call it done, just like the airlines mark the plane as leaving on time when it pushes back from the gate. Standing on the runway for 45 minutes doesn’t count against that target, even though I’d call that leaving late.

The article talks about picking things that bother a manager or team members about the software or their process. Some of these are easy, like the time to deliver a new server or recover from a failure. Others that I’ve found to be useful are the time to assemble a release or deploy software to an environment. While we can sometimes play with what these numbers measure by moving to smaller set of changes or altering our process, we’ll quickly find out if we have a lot of overhead in our work by adding these measurements.

What about the software itself? Can I actually compare the time to deliver report a vs. report b? One might be much more complex than the other. That’s true, but we ought to average out delivery estimates over time, and we can certainly separate out query writing of the complex logic against the time required for formatting. In fact, it might be good to start to measure different parts of software delivery to find out if certain people are better at some parts, or if requirements from clients are causing unnecessary delays. I know I’ve certainly had some formatting complaints require more time to get right than the entire rest of the software.

It can be hard to develop metrics that have outcomes and are truly actionable, but it’s a better way to determine if your team is improving. Relying on simple and traditional metrics is lazy, and allows for lots of argument and debate over whether a team is doing well or not. Tackling those specific items that are irritating to management or customers might take some work, but you’ll end up with a list of things that can be targeted for improvement and show progress that actually means something.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | 1 Comment

Pro SQL Server on Linux- More Installs

As part of my learning goals for 2018, I wanted to work through various books. This is part of my series on Pro SQL Server on Linux from Bob Ward.

After my last post, I decided to try and install the SQL Server 2019 preview version and see how that worked. I followed the instructions from Books Online.

Adding a new Repo

At first, I used curl to get the new repo file and then run the install. When looking at the install (sudo yum install –y mssql-server), I wondered if this would know I wanted 2019 instead of 2017. At this point,  my system has two repos, so how does it know?

No idea, but it worked.

2018-12-20 14_32_09-RHEL74 Bob - VMware Workstation

Or did it?

2018-12-20 14_35_41-RHEL74 Bob - VMware Workstation

I think I upgraded my instance, which is fine. This is testing, so it works fine for me.

Posted in Blog | Tagged , , , , | Leave a comment