Setting a variable named by the value of a variabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Setting a variable named by the value of a variabl

Author

Topic

dkperez
Starting Member

5 Posts

Posted - 2008-07-21 : 16:15:46

How do I create a variable from the VALUE of a variable?

In a SQL Server stored procedure I have in incoming
paramater @inparam that contains the value 'xyz'
and a local variable @localval loaded from a cursor

@localval contains the value "inparam" (the name of the incoming parameter) and inparam contains the value I want to see...

How do I do the equivalant of
IF @(@localval) IS NOT NULL -- and have it be testing the value (xyz) of the VALUE (inparam) of localval?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-21 : 22:10:20

Tried with dynamic sql?

dkperez
Starting Member

5 Posts

Posted - 2008-07-22 : 01:17:54

It's not a SQL statement... It's standard in-line stored procedure code that will be used as
a parameter in a call to a subroutine... The whole thing is in a cursor loop, and "@localval"
will be changing will each loop. I need to use the value in @localval to get the right data
from incoming parameters....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 03:42:23

You still need to check the value dynamically with semi-advanced SQL.
This normal code will not work

DECLARE	@Param VARCHAR(10),
	@Variable VARCHAR(10),
	@SQL VARCHAR(1000)

SELECT	@Param = '@Variable',
	@Variable = 'Peso'

SET	@SQL = 'PRINT ' + @Param

PRINT	@SQL

EXEC	(@SQL)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 03:53:52

The reason is that the variable referenced is out of scope.
You can solve this matter by using a similar logic as this below.

CREATE PROCEDURE dbo.uspMyProc
(
	@LocalVal VARCHAR(10),
	@InParam1 VARCHAR(10),
	@InParam2 VARCHAR(10)
)
AS

SET NOCOUNT ON

DECLARE	@SQL VARCHAR(1000)

CREATE TABLE	#Temp
		(
			VariableName VARCHAR(20),
			VariableValue VARCHAR(20)
		)

INSERT	#Temp
SELECT	'@InParam1', @InParam1 UNION ALL
SELECT	'@InParam2', @InParam2

SET	@SQL = 'SELECT VariableValue FROM #Temp WHERE VariableName = ' + QUOTENAME(@LocalVal, '''')

EXEC	(@SQL)

and call with simple

EXEC dbo.uspMyProc '@InParam1', 'Peso', 'Yak'
EXEC dbo.uspMyProc '@InParam2', 'Peso', 'Yak'

Or just do the easiest thing:

IF @InParam = '@MyLocalVariable'
  SET @MyCursor = @MyLocalVaraible
ELSE IF @InParam = '@AnotherVariable'
  SET @MyCursor = @AnotherVariable

E 12°55'05.25"
N 56°04'39.16"

dkperez
Starting Member

5 Posts

Posted - 2008-07-23 : 21:33:12

Yeah, too cumbersome.... I'm currently using the
IF...
EXEC
ELSE IF
EXEC

and I wanted a more elegant and less clumsy method...
It would work fine if I could do the equivalent of
SET @x = @(@y) but it appears SQL Server can't...

This was very normal when I worked on VMS and we could
control passing mechanism... Could use the by reference
and by value to set loop variables and get
the value of the variable it pointed to quite easily....

Ah well, the longer I work on current technology, the more
I appreciate how incredible what we had 15 years ago was.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 01:17:04

quote:
Originally posted by dkperez

Ah well, the longer I work on current technology, the more
I appreciate how incredible what we had 15 years ago was.

Well, we didn't have to be good programmers in that era.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources