SQL select data into a variable

First declare the variable the will hold the data:

declare @ref as nvarchar(50)

The following code will insert the field REF from the table DATA into the newly created variable:

SELECT top 1 @ref = ref
from  table.dbo.DATA
where date = @date

 

This value can now be used anywhere in the scode of the query.

Advertisements

4 thoughts on “SQL select data into a variable

  1. Hi. I am looking for help on selecting counts into a variable. Can you help with this?

    I need to get 3 sets of data from the same table.

    1) How many Invoices we received in a month.
    2) How many of those Invoices we had to touch more than once.
    3) The percentage of Invoices we had to touch.

    1) select distinct count(*)

    from INV

    where
    INV.STAT in (1, 2, 10, 11)
    and INV_DATE >= ’04/01/05′
    and INV_DATE = ’04/01/05′
    and INV_DATE <= ’04/03/05′ and
    REC_CREATE_DATE REC_UPDATE_DATE

    3) This is where I am at a loss. I don’t know how to get the code from #1 and #2 to play together to get #3.

    Expected results:

    # of Invoices | # of Changed | Invoices Percentage
    ——————————————7900 | 395 | 5%

    I know I need to declare some variables and then get the data into the variables, but all I get is errors about there being incorrect syntax ‘near the variable.’ What I am trying to do is:

    DECLARE @R1 FLOAT

    select distinct count(*)
    from INV
    where
    INV.STAT in (1, 2, 10, 11)
    and INV_DATE >= ’04/01/05′
    and INV_DATE = ’04/01/05′
    and INV_DATE <= ’04/03/05′

    I’ve tried a couple of other variations – putting things in quotes, parantheses, selecting in different ways, etc. But no luck.

    Got any suggestions for me?

    Thanks.
    SReid

    • Hi

      I would suggest running 2 select statements, each into a variable.

      Statement 1 – to calculate the number of invoices in a month:

      Declare @VAL1 FLOAT

      Select @VAL1 = count(*)
      From INV
      Where
      [where statements to select the given month]

      Statement 2 – to calculate the number of invoices changed more than once:

      Declare @VAL2 FLOAT

      Select @VAL1 = count(*)
      From INV
      Where
      [where statements to select the given month and changed invoices]

      Statement 3 – to calculate the percentage:

      @VAL1 now contains the total number of invoices.
      @VAL2 now contains the total number of changed changed.

      //Declare variable to hold percentage
      Declare @VAL3 FLOAT

      //Set the percentage
      Set @VAL3 = (@VAL2 / @VAL1) * 100

      You can now print the values. You will need to cast the float values as a char to concatenate them with a string:

      print ‘Invoices Total: ‘ + cast(@VAL1 as char)
      print ‘Invoices Changed: ‘ + cast(@VAL2 as char)
      print ‘Invoices Percentage Changed: ‘ + cast(@VAL3 as char)

      hope this helps.

  2. Dude! You so totally rock! I tried something very similar to that (in the last example where I was declaring @R1, I probably should have specified that I was trying to do the same thing with @R2 and @R3. It just kept telling me there was a problem near my select statement and I couldn’t figure out what the heck was wrong because if I took the declare out, it ran.) Anyway. I really appreciate your help. I owe you one! -SReid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s