We help IT Professionals succeed at work.

MS SQL 2005 :: SET Variable Value Dynamically Based On Select Field's Value

2,710 Views
Last Modified: 2008-08-05
Dear Experts,

Is it possible to assign different values to a parameter in a SQL statement based on your select field's value.

Please see code snipped for a clearer understanding.
DECLARE @P INT
 
SELECT
	P2.PartNo, P2.GroupingCode, P2.Code, P2.Qty,
 
	CASE WHEN P2.Qty > 10 THEN 
		SET @P = 10000
 
	ELSE 
		SET @P = -99 
	END,
        @P AS VarValue
FROM
	tblPickingList P2

Open in new window

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you cannot SELECT at SET in the same statement.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
anyhow, do you really need the @p?
SELECT
      P2.PartNo, P2.GroupingCode, P2.Code, P2.Qty,
 
      CASE WHEN P2.Qty > 10 
           THEN  10000 
           ELSE  -99 
      END AS VarValue
FROM
      tblPickingList P2

Open in new window

Author

Commented:
Yes, I just use it in that simple example for explanation purposes.
Where I will be using it it could ease my life a lot. :)
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
What do you mean with "just a single value" ?

Thanks for quick replies.
:)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
as I wrote: just 1 single value.

you did not really explain what you actually try to achieve, so I can only give "guessings" ...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.