Compare a field to a variable in one table whilst querying another

[shauns1]

Hi

I hope the title explained the problem accurately. I didn't quite know the best way to describe the problem.

Basically, I am retrieving data from my contacts table using a stored procedure. The procedure is passed one parameter - '@directoryID'.

For each record returned to my ASP.Net app, I want to return an additional field called 'InDirectory'. This will contain a simple True or False boolean value.

I have another table called ContactsToDirectories that stores a contactID along with a directoryID.

What I'd like to do is, for every contact in the contacts table, I'd like to check whether it exists in the ContactsToDirectory table where directoryID = @directoryID. If it does, my new field, InDirectory, will contain True, otherwise it will contain False - obviously!

This is what I have so far:

CREATE PROCEDURE [dbo].[cms_GetContactsForDirectory]

@directoryID int = null
AS
BEGIN

SELECT c.contactFirstName, c.contactLastName, c.contactID, (@directoryID =
(SELECT cd.directoryID FROM crm_ContactsToDirectory cd WHERE cd.contactID = c.contactID)) as inDirectory
FROM crm_Contacts c

END

I thought the expression
@directoryID =
(SELECT cd.directoryID FROM crm_ContactsToDirectory cd WHERE cd.contactID = c.contactID)
would return True or False but I get the following syntax errors:

Msg 102, Level 15, State 1, Procedure cms_GetContactsForDirectory, Line 15
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure cms_GetContactsForDirectory, Line 16
Incorrect syntax near ')'.

Is it possible to do what I am trying to do? If so, could anyone point me in the right direction of the correct way to do it?

Many thanks

Shaun

 

[r937]

like this perhaps?

SELECT c.contactFirstName
     , c.contactLastName
     , c.contactID
     , case when
          ( select count(*)
              FROM crm_ContactsToDirectory  
             WHERE directoryID = @directoryID
               AND contactID = c.contactID ) > 0
            then 1 else 0 end 
         as inDirectory
  FROM crm_Contacts c

r937.com | rudy.ca

 

[PHV]

And what about this ?
SELECT c.contactFirstName, c.contactLastName, c.contactID
, CASE WHEN @directoryID = d.directoryID THEN True ELSE False END AS inDirectory
FROM crm_Contacts c LEFT JOIN crm_ContactsToDirectory d ON c.contactID = d.contactID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shauns1]

Thanks guys.

Perfect!