Having trouble creating a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Having trouble creating a stored procedure

Author

Topic

NRaf
Starting Member

2 Posts

Posted - 2010-05-15 : 09:04:43

I have a table whose primary key is a foreign key in another table. I need a sp that will select the fields in the table but I also want a field that indicates whether the record in question is being used in that table.

Example:

Rental Table
------------
ID | OwnerID (FK) | CarID (FK ->Car.ID) | StartDate | EndDate,etc

Car Table
---------
ID | Make | Price | etc

Let's say I have a SP called IsCarAvailable. I want my SELECT statement to be something like:

SELECT c.Make, C.Price, IF (car.ID IN Rental) RETURN TRUE ELSE RETURN FALSE

Obviously that won't work, though. Any ideas?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-15 : 09:24:12

Try this:


SELECT C.Make, C.Price, CASE WHEN R.CarID IS NULL
                                  THEN FALSE
                             ELSE TRUE
                        END AS IsInRental
  FROM Car AS C
       OUTER APPLY
       (SELECT TOP(1) R.CarID
          FROM Rental AS R
         WHERE R.CarID = C.ID) AS R

NRaf
Starting Member

2 Posts

Posted - 2010-05-15 : 10:41:07

Thanks, that worked quite well. I couldn't use TRUE or FALSE though, because they aren't defined literals in SQL Server so I just substituted them with 'YES' and 'NO' (which is all I really needed).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-16 : 07:50:06

quote:
Originally posted by NRaf

Thanks, that worked quite well. I couldn't use TRUE or FALSE though, because they aren't defined literals in SQL Server so I just substituted them with 'YES' and 'NO' (which is all I really needed).

you could just use bit datatype and return 0 & 1 which can be interpreted as False and True at front end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources