2

Assume I have the following SQL snippet in SQL Server 2012:

DECLARE @fname varchar(20), @strVarName varchar(50)
SET @fname = 'cronus'

SET @strVarName = COVERT_VARIABLE_TO_STRING_NAME ( @fname)

--this should return '@fname'. this is not a value conversion this is converting a variable name to a string name 
SELECT @strVarName

How do I do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dore.Ad
  • 163
  • 2
  • 10
  • 7
    Just out of curiosity why do you want to do it in the first place? – usamazf Sep 03 '17 at 08:07
  • @UsamaZafar i want to store variable name and its value in a table – Dore.Ad Sep 03 '17 at 08:26
  • Possible duplicate of [Reflection in SQL Server 2008?](https://stackoverflow.com/questions/8974358/reflection-in-sql-server-2008) – shA.t Sep 03 '17 at 09:40
  • 3
    I think you have another issue you want to solve, to which you think converting a variable name to string is a solution. I'm pretty sure (as in 99.999999999999%) you are looking at the issue the wrong way. You have not told us what your real issue is, just your attempted solution. This is what is referred to as an [XY problem](http://xyproblem.info/): asking about your attempted solution rather than your actual problem. – TT. Sep 03 '17 at 12:00

5 Answers5

1

SQL Server does not support reflection. You may be able to retrieve column or table names from its catalog views but with variables you're out of luck. Maybe you'll find another way to solve this issue with dynamic SQL.

yacc
  • 2,915
  • 4
  • 19
  • 33
0

Use dynamic sql query

DECLARE @fname varchar(20), @sql varchar(MAX)
SET @fname = 'cronus'

SET @sql = 'SELECT ' + @fname

EXEC (@sql)
Ferdinand Gaspar
  • 2,043
  • 1
  • 8
  • 17
0

There are following Character data types used to store character strings:

char,
varchar,
nvarchar,
text,

If u already used variable as String then why need to convert as a string

    DECLARE @fname varchar(20), @strVarName varchar(50)
    SET @fname = 'cronus'

    SET @strVarName =  @fname

    SELECT @strVarName

if needed use CAST and CONVERT function

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
The beginner
  • 624
  • 4
  • 17
0

This is such a bizarre question, sounds like something I'd try to do.

Hmm, SQL is not supposed to do this but I guess, it doesn't mean you can't make it.

I think you would effectively have to write your own process to pull this off, something along the lines of:

Create dbo.sProcInserts stored procedure to insert values into a table:

  • Takes VariableName, Value and possibly table name to insert into as parameters

Create dbo.sProcExec stored procedure to execute stored procedure:

  • Before execute, read stored procedure into a variable
  • Find all variables that are SET (i.e. they have a SET @Var = OR SELECT @Var =)
  • After each variable set, add to your string a line that calls dbo.sProcInserts with the name of the variable and a select @Variable
  • Execute your newly written stored procedure

That way you don't have to actually make any modifications to your sProcs and it should catch the flow of variables and their changes through your procedure

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
PreQL
  • 358
  • 2
  • 6
-1

However the requirement itself is a bit strange for me, but here is a way that could be a good start point for you:

declare @var1 int
Set @var1= 1
--some code here
declare @var2 nvarchar(max)
set @var2 = 10
--some other code here
declare @var3 bit

print @@VERSION
print 'this is fake @value inside a string'

--$ This is a Hint to help me find the Query that should parsed
declare @sql varbinary(max)
select @sql=sql_handle 
from sys.sysprocesses
where spid=56

declare @q nvarchar(max)
select @q=  substring(text,1,charindex('$',text)-3) from sys.dm_exec_sql_text(@sql)

Select distinct rtrim(ltrim(substring(Name,1,charindex(' ',Name)))) as Name from(
    Select substring(replace(Name,'=',' '),8, Len(Name)) as Name from dbo.SplitString(@q,'declare ')
    ) as K
    where Name like '@[^@]%'

By running the above query you will get the variables name. Output:

@var1
@var2
@var3

You can find the source code for SplitString function Here Note: If you are using SQL Server 2016 and your database's compatibility level is equal or greater than 130, you can also use SPLIT_STRING introduced by Microsoft it self. Learn more Here

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62