Tag: syndicated

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.

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.

CREATE OR ALTER PROCEDURE dbo.StringTest
   @s VARCHAR(10)

AS

BEGIN
     SELECT @s = 'Some Code'

END

GO

Next, I add the OUTPUT keyword after the type.

CREATE OR ALTER PROCEDURE dbo.StringTest
   @s VARCHAR(10) = '' OUTPUT

AS

BEGIN
     SELECT @s = 'Some Code'

END

GO

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.

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

SQLNewBlogger

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?

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

This was a post that caught my eye, since I’d just written a piece on STRING_SPLIT(). Someone had this data:

DECLARE @Names VARCHAR(8000) = 'Steve, Grant, Kathi, Kendra';

They wanted this returned as a string that was sorted, so that the values would be:

Grant, Kathi, Kendra, Steve

String manipulation isn’t the strength of SQL Server, but we can do this with STRING_SPLIT(). If we use STRING_SPLIT(), we get a list of values. We want to remove the spaces after the comma, so we use REPLACE to remove that.

SELECT *

FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')

ORDER BY value;

We can then aggregate these back together in a variable assignment, adding the comma for each row.

SELECT @newtext = @newtext + Value + ', '

FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')

ORDER BY value;

This gives us:

SQLNewBlogger

A quick T-SQL application of some skills I learned. Can you do something similar? Maybe order dates or numbers that are in a string in the wrong order?

That should be a 10-15 minute post.