0

I am currently trying to create a stored procedure where it first grabs the RUId(Registered User ID) from the first table.And then uses this variable to query another table

The easiest way to explain this is by showing the a pseudo code of this request as shown below 

create procedure GetRUIdForUser
@Email nvarchar (160)

AS

SELECT RUId From RegisteredUsers 
WHERE Email = @Email

Then 

Select * From OtherTable where Ruid = @Ruid

What would the correct syntax for this be or would this need to be split up into two separate stored procedures?

Thanks!

Improve this question

3 Answers 3

Reset to default
3

Why not simply use JOIN ? instead of multiple variable & select statements :

select t.*
from RegisteredUsers ru inner join 
     table t
     on t.Ruid = ru.RUId 
where ru.Email = @Email;

However, your query variable will have only one ruid which will not help you more. So, you need a table variable to hold all ruids instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I believe that JOINING tables in this particular case will be quite more "expensive" than using nested queries.
@FDavidov I doubt that. I would guess that this query and your query will use exact same query plan. And a bonus, Yogesh query will not generate an error when email is not unique. But there is only one way to know for certain.
@SMor, though it is in principle easy to check (more/less cost), my option (the one marked as "the answer" does appear intuitively less expensive since it picks one single record from the table in the inner query and then uses the result to pick one single record from the table in the outer query. As such, it does not need to cross-check two tables to find the match (ON...). I'll see if I can simulate this condition (must be using large tables, otherwise the difference will be infinitesimal) and will post whatever I find. Cheers!!
1

Try this:

SELECT *
  FROM OtherTable
 WHERE Ruid = ( SELECT RUId
                  FROM RegisteredUsers 
                 WHERE Email = @Email ) ;
Improve this answer

3 Comments

What if RUID will be multiple based on the email? It will give the technical error.
Well my friend, if there is a problem with the uniqueness of key fields, the foundations of any program you may think of will be very fragile.
So, I called it a technical error. Anyway thanks for replying.
1

I recommend a join but you need to declare a variable to use what you are trying to do.

create procedure GetRUIdForUser @Email nvarchar (160) as

declare @ruid int

SELECT @ruid = RUId From RegisteredUsers 
WHERE Email = @Email

Select * From OtherTable where Ruid = @Ruid

My guess is that problem isn't as simple as you summarized.

Improve this answer

3 Comments

The RUId variable is not being passed through to the stored procedure so i'm not sure this one will work. Thankyou though :)
What if RUID will be multiple based on the email? It will give the technical error.
@ruid is captured from first query based on passed email. then used to get data from 2nd table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.