correlated subqueries with additional Variable

  • Thread starter Thread starter Masahiro Ito
  • Start date Start date
M

Masahiro Ito

I am creating a Stored Procedure that is very similar to the the Help file
(Sql Server Books Online - SELECT Examples, section E).

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'popular_comp'))

My application uses the nested SELECT statement, but needs to add one more
variable. I have declared the variable successfully, then SET it
separately. ie:

@MyRcID [int] = 0

SET @MyRcID = (SELECT RCID FROM NpaNxx WHERE (NpaNxx = @npaNxx))

So, I need to add this value to the IN statement. I thought it would be
easiest at the start:

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id = @MyRcID OR IN
(SELECT au_id ...

This didn't work, so I thought that the 'IN' might be a list that I could
add my variable to, like this:

WHERE title_id IN
(@MyRcID, SELECT title_id
FROM titles
WHERE type = 'popular_comp'))

But this didn't pass either. Does anyone know how I might get this?

Thank you everyone. You have been great assistance to me.

Masa
 
Masa,

Looking at this bit of code from your original email I think it may just be the syntax that is wrong.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id = @MyRcID OR IN
(SELECT au_id ...

try

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id = @MyRcID OR au_id IN
(SELECT au_id ...

Hope this helps..
Pablo
 
Back
Top