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
Whats your issue? Any error?
You have the correct syntax. SET use is preferred:
SET @Maxamount = @month+@Duration-1
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
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
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
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
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
select DATEPART(MM,start_date) + fixeddDuration-1 DATEPART(MM,start_date),
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
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?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.
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
i am unable to see the table.............. what is the query for that
i am unable to see the table.............. what is the query for that
Just use
Select * From @t
as the last statement.
- Edited by Latheesh NKMicrosoft community contributor, Editor 7 minutes ago
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?