how to add the scalar varibles and store the result in another scalar variable

declare @Maxamount int,
        @month varchar,
        @Duration int,
        @iReturn int
        
   set     @month        = (select DATEPART(MM,start_date) from TC_Project_Referal where id_tc_projuser = @id_tc_projuser )
   set     @Duration     = (select fixeddDuration from TC_Project_Referal where id_tc_projuser = @id_tc_projuser)
   select  @Maxamount    = @month+@Duration-1
March 3rd, 2014 2:02am

Whats your issue? Any error?


Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 2:17am

You have the correct syntax. SET use is preferred:

SET  @Maxamount    = @month+@Duration-1
March 3rd, 2014 2:18am

Adding to Kalman's point, You need to do lots of these to understand the difference between these two:

If you have multiple values return for a criteria, SELECT will assign only one value, whereas SET will throw you an exception. 

create Table TC_Project_Referal(id_tc_projuser int, fixeddDuration int)
Insert into TC_Project_Referal Select 1,100
Insert into TC_Project_Referal Select 1,200
Insert into TC_Project_Referal Select 2,500

Declare @id_tc_projuser int = 1

declare @Maxamount int,
        @month varchar,
        @Duration int,
        @iReturn int
        
   Select     @month    = DATEPART(MM,GETDATE()) from TC_Project_Referal where id_tc_projuser = @id_tc_projuser 
   --The below would throw error
 set     @month        = (select DATEPART(MM,GETDATE()) from TC_Project_Referal where id_tc_projuser = @id_tc_projuser )
 
 Drop table TC_Project_Referal

Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 2:44am

am unable to store the value select  @Maxamount    = @month+@Duration-1 in this statment 

set     @month        = (select DATEPART(MM,start_date) from TC_Project_Referal where id_tc_projuser = @id_tc_projuser )it will select the date from the database.for your understand.. i will display the month number  = 2 
   set     @Duration     = (select fixeddDuration from TC_Project_Referal where id_tc_projuser = @id_tc_projuser)it will select the duration from database for your understand.. i    i will display the duration as =  6
   select  @Maxamount    = @month+@Duration-1   i want  the result should be 2+6-1 =7 it has to be stored in @Maxamount variable .. 

thanx in advance

 

March 3rd, 2014 4:43am

am unable to store the value select  @Maxamount    = @month+@Duration-1 in this statment 

set     @month        = (select DATEPART(MM,start_date) from TC_Project_Referal where id_tc_projuser = @id_tc_projuser )it will select the date from the database.for your understand.. i will display the month number  = 2 
   set     @Duration     = (select fixeddDuration from TC_Project_Referal where id_tc_projuser = @id_tc_projuser)it will select the duration from database for your understand.. i    i will display the duration as =  6
   select  @Maxamount    = @month+@Duration-1   i want  the result should be 2+6-1 =7 it has to be stored in @Maxamount variable .. 

thanx in advance

 

It is storing. If you want to see the data stored, you need to SELECT the variable.

create Table TC_Project_Referal(id_tc_projuser int, fixeddDuration int)
Insert into TC_Project_Referal Select 1,100
Insert into TC_Project_Referal Select 1,200
Insert into TC_Project_Referal Select 2,500

Declare @id_tc_projuser int = 2

declare @Maxamount int,
        @month varchar,
        @Duration int,
        @iReturn int
        
  set     @month        = (select 2 from TC_Project_Referal where id_tc_projuser = @id_tc_projuser )--it will select the date from the database.for your understand.. i will display the month number  = 2 
   set     @Duration     = (select 6 from TC_Project_Referal where id_tc_projuser = @id_tc_projuser)--it will select the duration from database for your understand.. i    i will display the duration as =  6
   select  @Maxamount    = @month+@Duration-1 
   Select @Maxamount
 Drop table TC_Project_Referal

Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 4:47am

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

error am getting when am execuitng the same query 

i have more than 300 records are there in the database ..... selection is based on the id_tc_projuser . we can show mutiple records like id =14. based on the id 14 we have 12 records 
March 3rd, 2014 5:27am

then you need to use a table variable if you've multiple records returned by the query

ie like below

declare @t table
(
Maxamount int,
month varchar,
Duration int,
iReturn int
)
insert @t (Maxamount,month,Duration)
select DATEPART(MM,start_date) + fixeddDuration-1
DATEPART(MM,start_date),
fixeddDuration 
from TC_Project_Referal 
where id_tc_projuser = @id_tc_projuser    

I didnt understand purpose of iReturn though
Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 5:42am

it show incorrect syntax at
select DATEPART(MM,start_date) + fixeddDuration-1
DATEPART(MM,start_date),
March 3rd, 2014 5:59am

have you executed the query in your ssms
Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 6:04am

have you executed the query in your ssms

tht was a copy paste typo

it should be

declare @t table
(
Maxamount int,
month int,
Duration int,
iReturn int
)
insert @t (Maxamount,month,Duration)
select DATEPART(mm,start_date) + fixeddDuration-1,
DATEPART(mm,start_date),
fixeddDuration 
from TC_Project_Referal 
where id_tc_projuser = @id_tc_projuser    

Also please explain whats the purpose o
March 3rd, 2014 6:07am

remove the ireturn .man 

am getting the error

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 6:15am

remove the ireturn .man 

am getting the error

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Did you try Visakh's code? Can you show us your code please that you tried?
March 3rd, 2014 6:19am

remove the ireturn .man 

am getting the error

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

you're not using it as specified

the reason for the error is you didnt use column list in insert

see this modified suggestion without ireturn. see column list used with insert

Also if you've some other columns also which you've not showed us in sample posted please include that also in column list if you want to send values for them

declare @t table
(
Maxamount int,
month int,
Duration int
)
insert @t (Maxamount,month,Duration)
select DATEPART(mm,start_date) + fixeddDuration-1,
DATEPART(mm,start_date),
fixeddDuration 
from TC_Project_Referal 
where id_tc_projuser = @id_tc_projuser    

Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 6:20am

i am unable to see the table.............. what is the query for that

March 3rd, 2014 6:21am

i am unable to see the table.............. what is the query for that

Just use

Select * From @t

as the last statement.

Free Windows Admin Tool Kit Click here and download it now
March 3rd, 2014 6:23am

The code I gave was just for populating (storing) your query values. If you want to retrieve them you need a select.

ie

select * from @t

Also whats the purpose of storing these values? is it just for front end display or do you use these values for something else?

March 3rd, 2014 6:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics