CODEWITHSUNDEEP
c#.netsql-serverdatabaset-sql
Carl Weis
Carl Weis

Reputation: 7062

How to return a field from MSSQL based on the value of two other fields

I'm a MySQL guy these days and have a .NET project that I'm working on and can't figure out how to dynamically generate and return a field(column) from a table, based on two fields in the table.

There are two fields bRentable and bRented in the Units table and I need to return a field called reserved if bRentable and bRented are equal to zero.

Here is the SQL I have so far

/* Units Query */
SELECT Units.UnitID, Units.dcWidth, Units.dcLength, Units.dcPushRate, 
       Units.dcStdRate, Units.UnitTypeID, UnitTypes.sTypeName, Units.bRentable     
FROM Units
INNER JOIN UnitTypes ON Units.UnitTypeID = UnitTypes.UnitTypeID

Any help would be greatly appreciated.

Upvotes: 1

Views: 117

Answers (3)

danish
danish

Reputation: 5600

You can add the following as another column to your select statement:

(cast case 
when (bRentable = 0 and bRented = 0) then 1 
else 0 
end as bit) as reserved

Edit: Updated based on OPs comment:

SELECT Units.UnitID, Units.dcWidth, Units.dcLength, Units.dcPushRate, 
       Units.dcStdRate, Units.UnitTypeID, UnitTypes.sTypeName, Units.bRentable,(cast case 
    when (bRentable = 0 and bRented = 0) then 1 
    else 0 
    end as bit) as reserved   
FROM Units
INNER JOIN UnitTypes ON Units.UnitTypeID = UnitTypes.UnitTypeID

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13506

SELECT Units.UnitID, Units.dcWidth, Units.dcLength, Units.dcPushRate, 
       Units.dcStdRate, Units.UnitTypeID, UnitTypes.sTypeName, Units.bRentable,
       CASE When (Units.bRentable = 0 and Units.bRented = 0) then 1 else 0 end as reserved
FROM Units
INNER JOIN UnitTypes ON Units.UnitTypeID = UnitTypes.UnitTypeID

After this query in your function or sproc,you can just check the values of the field reserved.If its 1 return.

Upvotes: 1

Vignesh.N
Vignesh.N

Reputation: 2666

if you'd like a stored proc or user defined function, the below query will help.

DECLARE @Rentable BIT
DECLARE @Rented BIT
DECLARE @Reserved BIT

SELECT @Rentable = U.bRentable, @Rented = U.bRented
    FROM UNITS U INNER JOIN UnitTypes UT ON U.UnitTypeId = UT.UnitTypeId

IF(@Rentable = 0 AND @Rented = 0)
 BEGIN
    SET @Reserved = 0
 END
ELSE
 BEGIN
    SET @Reserved = 1
 END

RETURN @Reserved // OR SELECT @Reserved

Search for ADO.NET & using SqlCommand with parameters, you can pass SqlParameters after you frame the command with your query.

SqlCommand.Parameters.Add();

Upvotes: 0

Related Questions