none
how to save the result set of a SELECT in a variable ?

    Question

  • declare aVal int, bVal int;

    select min(d.DateValue) begindate, max(d.DateValue) enddate from dbo.dimDate d

    where d.BusinessYear = 2014 

    what I want to do is to save the result of the select statement in aVal and bVal 

    so aVal=begindate and bVal=enddate

    Friday, August 01, 2014 10:30 PM

Answers

All replies

  • Try this

    declare @aVal int, @bVal int;
    select @aVal = min(d.DateValue), @bVal = max(d.DateValue) 
    from dbo.dimDate d
    where d.BusinessYear = 2014 


    [Personal Site] [Blog] [Facebook]signature

    Friday, August 01, 2014 10:36 PM
    Moderator
  • One question though

    Why do you want to store date values in integer variables? Why not use date related datatypes like datetime,date etc?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, August 02, 2014 6:18 AM
  • VERY GOOD point!


    [Personal Site] [Blog] [Facebook]signature

    Saturday, August 02, 2014 9:01 AM
    Moderator
  • may be he is storing DateValue as INT :-)

    Thanks and regards, Rishabh K

    Saturday, August 02, 2014 4:40 PM
  • If so, then this is VERY bad idea fin most cases :-)

    [Personal Site] [Blog] [Facebook]signature

    Sunday, August 03, 2014 11:02 AM
    Moderator
  • may be he is storing DateValue as INT :-)

    Thanks and regards, Rishabh K

    Thats not recomemended approach

    There's a purpose behind having different datatypes implemented in SQLServer.So we should always try to use proper datatype for our fields/variables/parameters


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, August 03, 2014 12:58 PM
  • you are right I  should use a date time value


    Monday, August 04, 2014 2:03 PM
  • Hello,

    take a look on the below example :

    --Initialize data 
    -----------------------
    Create table  #tmp (id int,name varchar(50),marks int)
    Insert into #tmp values (1,'a' ,3)
    Insert into #tmp values (2,'b',5)
    --Decalare variable
    ------------------------
    Declare @maxid int, @minMarks int
    --Take data from table
    -------------------------
    Select @maxid=max(id),@minMarks= min (marks) from #tmp
    --Select data storead in variables
    Select @maxid,@minMarks
    Drop table #tmp


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, August 05, 2014 11:08 AM