Inserting stored procedure results into a table

Working with one of my colleagues earlier today, we found that we needed a way to store the results of a stored procedure execution in a table. He found this helpful blog post that shows exactly how.

One thing we found that the original blog post didn’t mention is that this approach works with table variables as well.  A revised example that uses a table variable is available as a gist on GitHub.

Saving Changes is Not Permitted (SQL Server 2008 R2)

We just upgraded our development VMs at work, and I got bitten by one of the more annoying default settings in SQL Server Management Studio again. I imported some data for use in some queries and needed to change one of the column types. But when I tried to save the change, I got the dreaded “Saving changes is not permitted.” error.

Fortunately, this blog post directed me to the setting I needed to change in order for SSMS to do what I wanted.

Random SQL Tricks (Part 2)

In my previous random SQL tricks post, I discussed how to generate random alphanumeric strings of any length.  A slight variation on that idea that also proved useful in generating test data is the following stored procedure (which generates a varchar consisting entirely of numbers):

CREATE PROCEDURE [dbo].[SpGenerateRandomNumberString]
@randomString varchar(15) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”

WHILE @counter <= 15
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (57-48+1)*RAND()))

SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
GO

The range in the select for @nextChar maps to ASCII values for the digits 0-9.  Unlike the stored procedure from my first post, there’s no if statement to determine whether or not the random value retrieved is allowed because the ASCII range for digits is contiguous.  The needs of my application restricted the length of this numeric string to 15 characters.  For more general use, the first refactoring would probably add string length as a second parameter, so the numeric string could be a variable length.

Random SQL Tricks (Part 1)

One of my most recent tasks at work has been generating test data for integration tests of a new application.  We don’t have the version of Visual Studio which does it for you, and rather than write an app that did it, I spent the past week hunting for examples that just used Transact-SQL.  The initial post that I found the most useful is this one, in which the author provides five different ways of generating random numbers.  I use his third method quite often, as you’ll see in this post (and any others I write on this topic).

One of our needs for random test data was alphanumeric strings of varying lengths.  Because the content of the text mattered less than the need for text, it didn’t have to resemble actual names (or anything recognizable).  The first example I found of a T-SQL stored procedure for generating a random string was in this blog post by XSQL Software.  The script does generate random strings, but they include non-alphanumeric characters.  To get the sort of random strings I wanted, I took the random number generation method from the first post and the stored procedure mentioned earlier and adapted them to this:

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ”

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

The range in the select for @nextChar is the set of ASCII table values that map to digits, upper-case letters, and lower-case letters (among other things).  The “if” branch values in the set are those ASCII table values that map to punctuation, brackets, and other non-alphanumeric characters.  Only alphanumeric characters are added to @randomString as a result.  Having a stored procedure like this one available makes it much easier to generate test data, especially since it can be called from other stored procedures.

Paging in Microsoft SQL Server stored procedures

Today I found out that an old boss of mine at Ciena Corporation has a blog (thanks to Plaxo).   I learned a lot about  ASP.NET, C#, and Plumtree through the work he assigned to me.  From looking at the posts he’s got in there so far, if you’re looking to overclock a PC or find out more about Vista, it’s worth checking out.
An older post of his has a good example of how to implement paging in a SQL Server stored procedure.