Link to home
Start Free TrialLog in
Avatar of Xencor
XencorFlag for United States of America

asked on

Comparing two variables in an SQL query.

Here is my problem :

  Two tables called Certifications & Employees
            e.g.
                   Select * from Certifications C, Employees E
                   where C. Employee_ID = E. Employee_ID
                   and Certification_Prefix = 'MCSE'

            I am trying to convert Certification_Prefix into a variable & MCSE into another variable
             So I am trying to do something like this....same as above except

                Select * from Certifications C, Employees E
                   where C. Employee_ID = E. Employee_ID
                   and variable1 = variable2

   What is the correct syntax to in an SQL server query to say : variable1 = variable2
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what kind of variable?
also, should variable1 refer to the column name?
how do you run that code?
so, do I take it that you want to pass the NAME of a field as variable1, and the Value to test for in THAT field as variable2?

In this case, you would create what is known as 'dynamic' SQL.  

Would you be doing this in a Stored procedure?  or entirely in code?

AW
Avatar of Xencor

ASKER

Arthur, You are correct variable 1 is the name of the field in the table and variable 2 is the value.  What is the syntax to make variable1 = variable2 in the above query?
Are you using a Stored Procedure in SQL Server for this?

if so, then the SP would be something like this:


<Air Code>
 
IF OBJECT_ID ( 'GetDataFromTable', 'P' ) IS NOT NULL 
    DROP PROCEDURE GetDataFromTable;
GO
 
Create  Procedure GetDataFromTable 
    @FieldName  varchar(20), 
    @Value varchar(20)
AS
    DECLARE @SQL varchar(100);
    BEGIN
         SET @SQL = " Select * from Certifications C, Employees E
                   where C. Employee_ID = E. Employee_ID
                   and " +@FieldName +" = '" + @Value +"'";
         Exec @SQL;
    End
 
</AIR CODE>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Xencor

ASKER

Thank You Arthur, that lost code snippet worked like a charm.
Glad to be of assistance.

AW