Dynamically named variables from query

Question

I want to write a SQL Server stored procedure that contains the following:

Imagine a query like this:

SELECT VARNAME, TYPE, VARVALUE, LEN(CAST(VARVALUE AS VARCHAR(5))) + 1 AS LENGTH
FROM SOMETABLE;

And further imagine it returns something like the following:

VARNAME	TYPE	VARVALUE	LENGTH
RED	INT	42	3
GREEN	VARCHAR	GRASS	6
PURPLE	CHAR	5	2

Using this data, I want to end up with a set of declared variables that are the equivalent of this:

DECLARE 
    @RED INT = 42,
    @GREEN VARCHAR(6) = 28,
    @PURPLE CHAR(2) = 5 `

so that I can do something like this later on in the stored procedure:

PRINT @RED
PRINT @GREEN
PRINT @PURPLE

MYVAR = @PURPLE + ' LBS OF ' + @GREEN + ' YIELDS ' + @RED + ' SQ INCHES OF PLANTS'

I've tried a bunch of things, like executing dynamic SQL, like writing query output that takes the form of DECLARE statements that I would execute, but none of it worked, because even if the variables did get created (I have no idea if they really did or not), I couldn't use them later on in the code because they aren't declared when I compile the stored procedure, and that makes the compile fail.

I am caught in a classic Catch-22 and I have no more ideas about how to accomplish this.

Is it possible? Can you show me how?

You might be interested in why I want this, and you might want to tell me how it's a bad idea. My purpose is too long to describe, and trust me, this will be a great idea if it works, so I'd like to steer away from those discussions please. I've pretty much convinced myself that it is impossible, but I thought I'd ask before I completely give up.

Thanks in advance.

You can absolutely do that, but everything has to be dynamic SQL so that they exist when the query runs — Dale K
– Dale K, Commented Jun 28, 2024 at 23:44
The trick is remembering that not only must your DECLARE statements be in the dynamic SQL, but so must the other T-SQL code that utilizes them. — AlwaysLearning
– AlwaysLearning, Commented Jun 29, 2024 at 0:34
This sounds like a XY problem. What are you trying to achieve actually — Squirrel
– Squirrel, Commented Jun 29, 2024 at 1:48

David Browne - Microsoft · Accepted Answer · 2024-06-29 13:16:40Z

1

Here you go:

DECLARE @RED INT = (select cast(varvalue as int) from sometable where varname = 'RED');

etc. I suppose you could generate that code dynamically. If you generate this in a dynamic batch, you'll have to include all the downstream code that references the variable in the dynamic batch.

answered Jun 29, 2024 at 13:16

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mightycpa Over a year ago

I like this a lot, and I appreciate the advice about the downstream code, which I responded to up above. I'll give this a try and report back, hopefully with some code. Thanks.

Collectives™ on Stack Overflow

Dynamically named variables from query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related