How to assign value to a variable in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi,

SQL

Declare @M201305 numeric(12,2)= 2000
Declare @Var1 Varchar(10),@mth varchar(6)
Set @mth='201305'
select @Var1= '@M'+@mth
select @Var1

For @var1 I neeed the value of 2000.
How to get this?

Posted 27-May-14 20:43pm

kirthiga S

Add a Solution

Comments

Thomas Nielsen - getCore 28-May-14 3:01am

i don't entirely understand. The @var1 is of type varchar, so it can never be 2000, perhaps '2000' but you're setting it to '@M201305' and selecting it here.
Can you write what you want to do in pseudocode or simply english?
To my mind the point of what you're trying to is somewhat muddy by some mis-step in how to do it which of cause is why you're posting at all :) perhaps it's just me being dull witted but i don't get it yet :)

kirthiga S 28-May-14 3:08am

Hi need this for a requirement

Declare @Var1 Varchar(10),
@M201304 numeric(12,2),@M201305 numeric(12,2),@mth varchar(6)
Set @M201305 = 2000
Set @M201304 = 1000

Set @mth='201305'
Set @Var1= '@M'+@mth
Select @Var1

I have to get the numeric data as output for @var1
How to modify my query to achieve this

OriginalGriff 28-May-14 3:34am

Repeating the same thing when asked for clarification is not helpful: you need to explain what you are trying to achieve in ways we can understand

***without seeing your screen, accessing your HDD, or reading your mind***

Just telling us the same thing again doesn't help that.

4 solutions

Solution 1

Ah I see what you are trying to do, mix variable names and values together. You can do this using dynamic sql (basically a string concatenation) and an OUT parameter.

There is no other way of doing this!

Posted 27-May-14 21:40pm

Mycroft Holmes

Solution 2

If I got you right you want to set @Var1 to the value of @M201305 while creating the variable name using string concatenation...
That is impossible in SQL, but only with dynamic SQL...

Posted 27-May-14 21:50pm

Kornfeld Eliyahu Peter

Updated 27-May-14 21:51pm

v2

Solution 3

Write Down Dynamic Query ...

SQL

DECLARE @M201305 NUMERIC(12,2) = 2000

DECLARE @Var1 VARCHAR(10), @Mth VARCHAR(6)

SET @Mth = '201305'

SELECT @Var1 = '@M' + @Mth

EXECUTE('DECLARE @M201305 NUMERIC(12,2) = 2000 SELECT + ' + @Var1)

We get output using this Query ....

Posted 27-May-14 23:18pm

Jay1902

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Nielsen - getCore · Accepted Answer · 2014-05-27T23:22:00

I see, yes you'll be using a temp table to do that and keep the values of the variables.
Personally i would propably do this thing somewhere else then the database but if you have to ..

SQL

Declare @Var1 Varchar(10), @mth varchar(6)

declare @MyValues table(
    Variable nvarchar(8),
    Value numeric(12,2))

insert into @MyValues(Variable, Value)
values('@M201304', 1000)
insert into @MyValues(Variable, Value)
values('@M201305', 2000)

Set @mth='201305'
select top 1 Value from @MyValues where Variable = ('@M'+@mth)