Scalar Variable not Populating

Question

Here's the story. I'm trying to pull metadata from a master table and from a series of tables whose names are based on values in the master table. There is no foreign key.

If there was a key, it is that the primary key from from the master table is appended to the end of the child table. The master table is hsi.keytypetable. The child tables are hsi.keyitemxxx where the xxx is a value (keytypenum) pulled from the master table.

All I'm trying to pull from the child table right now is a count of values. In the current form, the query, @sql1, is failing to populate @keytypenum, although when I look at query itself, and run it in a separate window, it works like a champ. The problem continues in the second query, @sql2, where I am getting the error,

Must declare the scalar variable "@keytypenum"

As far as I can tell, I've declared the thing. I'm guessing I have a similar problem with syntax in each query.

SET CONCAT_NULL_YIELDS_NULL OFF
declare @keytypedata table
    (
        Keyword varchar(50),
        DateType varchar(50),
        "Length" int,
        "Count" int
    )

declare @keywordcount int
declare @x int = 1
declare @keytypenum int
declare @sql1 varchar(max)
declare @sql2 varchar(max)

/* Determine how many records are in the master table so that I can cycle thru each one, getting the count of the child tables. */
Select @keywordcount = (Select count(*) from hsi.keytypetable)

/* @x is the counter. I'll cycle through each row in the master using a WHILE loop */

WHILE @x < @keywordcount+1
    BEGIN

/* One row at a time, I'll pull the KEYTYPENUM and store it in @keytypenum. (I don't really need the order by, but I like having things in order!)
** I take the rows in order b using my counter, @x, as the offset value and fetch only 1 row at a time.  When I run this query in a separate screen,
** it works well, obviously with providing a fixed offset value. */

        set @sql1 =
            'Set @keytypenum = 
            (Select
                KEYTYPENUM
            from hsi.keytypetable
            order by KEYTYPENUM
            OFFSET ' + cast(@x as varchar(4)) + ' ROWS
            FETCH NEXT 1 ROWS ONLY)'
    EXEC(@sql1)

/* For debugging purposes, I wanted to see that @keytypenum got assigned.  This is working. */
print 'KeyTypeNum:  '+cast(@keytypenum as varchar(4))

/* I don't know why I had to be my table variable, @keytypedata, in single quotes at the beginning, but it wouldn't work if 
** I didn't.  The problem comes later on with restricting the query by the aforementioned @keytypenum.  Remember this variable is an INT.  All values
** for this field are indeed integers, and there are presently 955 rows in the table.  The maximum value is 1012, so we're safe with varchar(4).
*/
    SET @sql2 = 
    'Insert into ' + '@keytypedata' + '
    Select
        keytype,
        CASE
                WHEN k.datatype = 1  THEN ''Numeric 20''
                WHEN k.datatype = 2  THEN ''Dual Table Alpha''
                WHEN k.datatype = 3  THEN ''Currency''
                WHEN k.datatype = 4  THEN ''Date''
                WHEN k.datatype = 5  THEN ''Float''
                WHEN k.datatype = 6  THEN ''Numeric 9''
                WHEN k.datatype = 9  THEN ''DateTime''
                WHEN k.datatype = 10 THEN ''Single Table Alpha''
                WHEN k.datatype = 11 THEN ''Specific Currency''
                WHEN k.datatype = 12 THEN ''Mixed Case Dual Table Alpha''
                WHEN k.datatype = 13 THEN ''Mixed Case Single Table Alpha''
            END,
            keytypelen,
            (Select count(*) from hsi.keyitem' + cast(@keytypenum as varchar(4)) + ')
    FROM
        hsi.keytypetable k
    where
        k.keytypenum = ' + cast(@keytypenum as varchar(4))+''

/* Printing out where I am with cycling thru the master table, just for troubleshooting*/
print @x

/* Increment the counter*/
    set @x = @x + 1
END

/* This query is simply to display the final results. */

select * 
from @keytypedata
order by 1

/* Print statements below are for troubleshooting.  They should show what the 2 queries currently look like. */
Print @sql1
Print @sql2

Format your question, and make it a [Minimal, Complete, and Verifiable Example](https://stackoverflow.com/help/mcve) with emphasis on *minimial* to start. — S3S, Aug 16 '17 at 20:35

TT. · Answer 1 · 2017-08-16T20:53:11.677

0

When you declare variables, the visibility is restricted to the scope in which they are declared. When you EXEC a statement, a new session and thereby a new scope is created.

What you need to do is output the scalar variable using an OUTPUT parameter in a call to sp_executesql:

SET @sql='Set @keytypenum = ...';
EXEC sp_executesql @sql,N'@keytypenum INT OUT', @keytypenum OUTPUT;

Note that the @sql variable needs to be an NVARCHAR.

You can find more examples here.

edited Aug 16 '17 at 20:53

answered Aug 16 '17 at 20:46

TT.

15,774
6
47
88

This is awesome and clearly resolved my problem with the first of the queries right away. Followup question, if you don't mind. My second query uses a table variable, keytypedata. I was hoping to use this "virtual" table to summarize all of my results for output. As I'm getting the error, "Must declare the table variable @keytypedata." I'm concerned that I will not be able to append rows to the table because it will reset after every EXEC. Will sp_executesql get around that, or am I screwed? :( Thank you SO much! – Brad Aug 16 '17 at 22:19
@Brad Hi Brad. Table valued parameters are always read-only and cannot be used for output (see [this question](https://stackoverflow.com/q/19270727/243373) for more about that). You could use temporary tables instead. These do not have to be passed as parameters. They temporarily exist as though they were normal tables, as long as the session is active. – TT. Aug 17 '17 at 05:52
@Brad If my answer solved your problem, please check the checkmark (✔) next to the answer. Read more about that here: [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers). Good luck! – TT. Aug 17 '17 at 05:55

score 0 · Answer 2 · answered Aug 16 '17 at 20:46

Yeah, you can't do that. Your variables are going out of scope.

Each variable has it's scope restricted to it's own session, and exec() basically creates a new session.

This will throw and error that @x is undefined:

declare @x int = 1

exec ('select @x')

As will this:

exec ('declare @x int = 2')

exec ('select @x')

And this:

exec ('declare @x int = 2')

select @x

You'd have to do it like this:

exec ('declare @x int = 2; select @x')

Or otherwise pass the results back somehow. The sp_executesql suggestion in @TT.'s answer is a good idea.

Scalar Variable not Populating

2 Answers2