Development Help with SQL Queries and Variables

I’m trying to build some sql queries in query analyzer (eventually for use in stored procedures).. I’m not very good at explaining so I’ll try and use examples.

Question 1
I need to build a query dynamically based on some variables
So here is an example table

Code:

[Field1]      [field2]       [field3]
------------------------------------------
A		1		DATA
B		1		DATA
C		1		DATA
A		2		DATA
B		2		DATA
C		2		DATA
A		3		DATA
B		3		DATA
C		3		DATA

This works

Code:

DECLARE @myVar AS VARCHAR(50)
SET @myVar='A'

IF (SELECT @myVar)='A'
	SELECT *
	FROM test
	WHERE field1='A'
IF (SELECT @myVar)='B'
	SELECT *
	FROM test
	WHERE field2='1'

But I’m trying to do is achieve the same results using the below format.
Due to the final query having multiple conditions and variables.

Code:

DECLARE @myVar AS VARCHAR(50)
SET @myVar='A'

SELECT *
FROM test

IF (SELECT @myVar)='A'
	WHERE field1='A'
ELSEIF (SELECT @myVar)='B'
	WHERE field2='1'
ENDIF

Question 2
I want to select a field from a table (again) dynamically based on variables.
So using the above table again I’m going for

Code:

DECLARE @myVar AS VARCHAR(50)
SET @myVar='field1'

SELECT @myVar
FROM test

I don’t know if either of these are possible but any help would be much appreciated.

 

google sql CASE

Code:

SELECT   ProductNumber, Name, 'Price Range' = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber 

hope it helps
hmm looking again at your problem it might not what are you using to call you rSP