Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unanswered: Local variables in a query

    Hi,

    I was asked to write a query that has 14 variables.
    the query should look like this:

    begin
    declare x1 int;
    declare x2 int;
    declare x3 int;
    declare x4 int;
    declare x5 int;
    declare x6 int;
    declare x7 int;
    set x1=16;
    set x2=8;
    set x3=4;
    set x4=1;
    set x5=1;
    set x6=1;
    set x7=1;

    select
    case when fieldname=value1 then val1*x1 end
    case when fieldname=value2 then val2*x2 end
    case when fieldname=value3 then val3*x3 end
    case when fieldname=value4 then val4*x4 end
    case when fieldname=value5 then val5*x5 end
    case when fieldname=value6 then val6*x6 end
    case when fieldname=value7 then val7*x7 end
    from tablename

    end

    The user would like it that way because he wants to be able to change the values only in one place.

    because of the nature of the query I have to use the case command.

    This is not working!!

    Can someone please assist?

    Thanks in advance,
    maya

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    You wish to write a query or a stored procedure?. can u please tell your requirement in detail

  3. #3
    Join Date
    Jun 2007
    Posts
    7

    I will try to be more specific

    First, thank you very much for your reply.

    This should be a query.
    I have to use this data (in variables or some other way) in my query in order to do calculations with:

    SF_UL4=16
    SF_UL8=8
    SF_UL16=4
    SF_UL32=1
    SF_UL64=1
    SF_UL128=1
    SF_UL256=1
    SF_DL4=2
    SF_DL8=8
    SF_DL16=16
    SF_DL32=5
    SF_DL64=1
    SF_DL128=1
    SF_DL256=1

    It has to be like this because the values will be changed by the user one in a while.

    After I get this data I should use it in my query to calculate with fields from a table in my database.

    Could it be done??

    Thanks in advance!
    Maya.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Variables should be prefixed with @ e.g.
    declare @x1 int
    set @x1=16
    select col1*@x1 from mytable

    But data should not be stored in code
    Store these values that the user can change in a table
    Your query can then join to this table and your code won't have to change even if they add more values

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •