Replace SQL Query

Question

I want to replace this Query

DECLARE @tmpUser TABLE( UserId INT, UserType INT )

INSERT INTO @tmpUser
SELECT
    u.UserId,
    1
FROM
    Users u (nolock)
WHERE
    u.UPIN = @AttendingDoctorID

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO @tmpUser
    SELECT
        u.UserId,
        1
    FROM
        Users u (nolock)
    WHERE
        u.FirstName = @AttendingDoctorFirstName AND
        u.LastName = @AttendingDoctorLastName
END
SELECT * FROM @tmpUser

to a single SQL SELECT statement without using @tmpUser or any other temp tables

Please consider rewriting your question. It is very unclear what you are asking - the two queries bear no relation to each other — CResults
– CResults, Commented Jul 22, 2011 at 6:34
To expand on gbn's comment... you don't need nolock in the general case. Adding this hint will tell SQL Server not to take or honour any locks on the read data. It can improve query performance, and especially concurrency. But this is at the expense of consistency, as you may end up reading dirty data, that is, data that is currently being updated (but has not yet been committed) by another transaction. This probably isn't what you want, so unless you have a compelling case for using nolock (ie. a highly concurrent environment), you should probably leave it out. — Mike Chamberlain
– Mike Chamberlain, Commented Jul 26, 2011 at 0:29
@MikeyCee : i think ...this is a highly concurrent environment...because I am calling this SP from a Thread with a time gap of 500 milli seconds. What u say ? — Sreekumar P
– Sreekumar P, Commented Jul 26, 2011 at 4:25
Highly concurrent would be like hundreds or thousands of threads all reading and updating the same data simultaneously. I suspect that, in your case, this does not happen, though I could be wrong. By the way, 500ms is an ETERNITY in CPU cycles, though it depends on how many threads you have calling your SP. If it's just a single thread then this would not count as highly concurrent, and you should forget about nolock (and forget about micro-optimizations in general, such as your original question, until you have a fully working app, AND you see that its performance is unacceptable). — Mike Chamberlain
– Mike Chamberlain, Commented Jul 26, 2011 at 4:52

Mike Chamberlain · Accepted Answer · 2011-07-22 07:50:22Z

3

The following uses an extra lookup, but it fulfills your requirements. I don't know for sure if this is any more efficient than using a temp table, though if UPIN is indexed then I suspect it would be.

IF EXISTS(
    SELECT 
        1 
    FROM 
        Users u 
    WHERE 
        u.UPIN = @AttendingDoctorID)
BEGIN
    SELECT 
        u.UserId, 1 
    FROM 
        Users u  WITH(nolock)
    WHERE 
        u.UPIN = @AttendingDoctorID
END ELSE BEGIN
    SELECT
        u.UserId,
        1
    FROM
        Users u (nolock)
    WHERE
        u.FirstName = @AttendingDoctorFirstName AND
        u.LastName = @AttendingDoctorLastName
END

answered Jul 22, 2011 at 7:50

Mike Chamberlain

43k28 gold badges113 silver badges160 bronze badges

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

Comments

Talha Ahmed Khan · Accepted Answer · 2011-07-22 06:51:53Z

0

How about Using OR instead of two separate queries.

I think it would serve the purpose.

SELECT
    u.UserId, 1
FROM
    Users u (nolock)
WHERE
    (u.UPIN = @AttendingDoctorID)
    OR
    (u.FirstName = @AttendingDoctorFirstName AND
    u.LastName = @AttendingDoctorLastName)

edited Jul 22, 2011 at 6:51

answered Jul 22, 2011 at 6:12

Talha Ahmed Khan

15.6k10 gold badges46 silver badges49 bronze badges

6 Comments

Sreekumar P Over a year ago

i don't want to use @tmpUser or any other temp tables

Talha Ahmed Khan Over a year ago

Then what do you want, what is the requirement. and how u need your result.

Mike Chamberlain Over a year ago

Your query is not equivalent because the original poster's query only selects a user based on name if the lookup by @AttendingDoctorID failed to match anything.

Talha Ahmed Khan Over a year ago

I am not sure why are you saying that, Because I am ORing between the two scenarios, and if the first scenario is successful it will give the result other wise look for the second condition.

Sreekumar P Over a year ago

@Talha Ahmed Khan ur's is wrong see this....stackoverflow.com/questions/6787540/are-both-queries-the-same/…

|

Collectives™ on Stack Overflow

Replace SQL Query

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related