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 LENTH
FROM SOMETABLE;

And further imagine it returns something like the following:

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 SP:

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 SP, 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.