dynamic variable names from table

[aas4mis]

Is it possible to DECLARE variable names and set the values in TSQL dynamically at the start of a stored procedure so that the "setting/value" pairs may be used throughout the stored procedure?

Something along these lines...

-- settings table
CREATE TABLE tbl_Settings (
SETTING_ID int IDENTITY(1,1) NOT NULL,
SETTING nvarchar(max) NOT NULL,
VALUE nvarchar(max) NOT NULL
)

-- some data
INSERT INTO tbl_Settings
SELECT 'MaxRows', '1000'

--stored procedure
CREATE PROCEDURE MyProc
AS
BEGIN

DECLARE @SETTINGID
DECLARE @SETTING nvarchar(max)
DECLARE @VALUE nvarchar(max)

SELECT * 
INTO #tempSETTINGS 
FROM tbl_SETTINGS

WHILE (SELECT COUNT(*) FROM #tempSETTINGS) > 0
  BEGIN
    SELECT TOP 1 
      @SETTINGID = SETTING_ID
      ,@SETTING = SETTING
      ,@VALUE = VALUE
    FROM #tempSETTINGS

    -- is something like this possible?
    DECLARE 'var' + @SETTING AS nvarchar(max)
    SET 'var' + @SETTING = @VALUE

    DELETE #tempSETTINGS
    WHERE SETTING_ID = @SETTINGID

  END
END

--do something with variable
IF @varMaxRows >= 1000
  SELECT 'It Works!'

It would be a great benefit to store my "settings" in a centralized location such as a table. If this is not possible please post any ideas to achieve the same goal.

Thanks!

[aas4mis]

Must not be possible. Usually the responses are pretty quick around here. .. I hope somebody benefits from the "fake cursor". :)

[ck9663]

You have to build your DELETE statement or any t-sql statement that will use the value as a dynamic statement and execute it either through a sp_executesql or straightforward EXEC statements.


Happy Coding!!!


~~ CK

[aas4mis]

Thanks for the reply, but I don't think I got my point across. I'm familiar with dynamic sql, but my struggle is DECLAREing the variables programmaticall y. Once this is accomplished I'll be a happy camper. :)

I'm looking for a way to get lines 34 and 35 to work. If I can get this to work I can setup a master table of variables/settings for use in multiple stored procedures. My settings would be easily maintained and updated across several stored procedures at once. No need to update several stored procedures, just one UPDATE to the table. I'm looking for an #Include for TSQL so to speak. .. Is this just a pipe dream?

[ck9663]

The only way I can think of that you can do that is through a dynamic sql. You can even declare variable inside a dynamic sql statement.

Good Luck!!!


~~ CK

[aas4mis]

Ruheeeally? I didn't realize a variable declared in dynamic sql would exist outside of that scope and be available to use in the calling stored procedure I thought that was only true for tables.

I'll give it a shot and let you know if it works. Thanks CK!!!

[ck9663]

It actually depends on how you call your dynamic query. If you use sp_executesql, you will be able to pass a value as parameter (just like passing into a stored proc) inside your query and it will be able to return a value as well (as in calling a stored proc).

Read the BOL for more details and examples.

Happy Coding!!!


~~ CK

[aas4mis]

... not leaving without posting my results, just haven't had an opportunity to get back on this. ... deadlines. :(