help with query - SQL Server Forums

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)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-05 : 16:04:42

Hi,

I have the following query I am trying to optimize..

Is there a way I can insert the "PARTNERID" that is retreived in the first query, into the tblLogins_Log , more efficiently than adding a second SELECT statement ?

I still want to be sure that 1 row is brought back from the query if the record is found.

Thanks once again!
mike123

SELECT partnerID FROM [tblPartners] WHERE (emailAddress = @emailAddress and password = @password)

IF @@ROWCOUNT > 0
BEGIN

DECLARE @lastLoggedIn datetime
SELECT @lastLoggedIn = getDate()

INSERT INTO [tblLogins_Log] (partnerID,IP,loginDate,countryCode) VALUES ('VALUENEEDEDHERE',@loginFromIP,@lastLoggedIn,@loginFromIP_CountryShort)
UPDATE [tblPartners] SET lastLoggedIn = @lastLoggedIn

END

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-05 : 16:12:34

[CODE]
INSERT INTO [tblLogins_Log] (partnerID,IP,loginDate,countryCode)
SELECT partnerID, @loginFromIP,GETDATE(),@loginFromIP_CountryShort
FROM [tblPartners] WHERE (emailAddress = @emailAddress and password = @password)

IF @@ROWCOUNT > 0
BEGIN
UPDATE [tblPartners] SET lastLoggedIn = GETDATE() WHERE....
END
[/CODE]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-05 : 16:20:41

Hi rohitkumar,

I only want to do the INSERT & UPDATE statments if the @@ROWCOUNT > 0

This version we are doing the insert regardless, so the logic has changed.

Any help much appreciated.

thanks!
mike123

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-05 : 16:27:51

quote:
I only want to do the INSERT & UPDATE statments if the @@ROWCOUNT > 0
This version we are doing the insert regardless, so the logic has changed.

that is not the case...your @@ROWCOUNT > 0 only when this statement

quote:

SELECT partnerID FROM [tblPartners] WHERE (emailAddress = @emailAddress and password = @password)

returns a resultset, in other words when its where condition is met.

same will happen if you combine INSERT with a SELECT with same WHERE condition.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 16:31:38

Mike, you are using sql 2008 right?

Use MERGE if so.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 16:33:15

[code]
DECLARE @lastLoggedIn datetime,@PartnerID int
SELECT @lastLoggedIn = getDate()

INSERT INTO [tblLogins_Log] (partnerID,IP,loginDate,countryCode)
select @PartnerID = a.PartnerID,@loginFromIP,@lastloggedIn,@loginFromIP_CountryShort
FROM [tblPartners] a
WHERE a.emailAddress = @emailAddress and a.password = @password

if not @PartnerID is null
begin
UPDATE a
SET lastLoggedIn = @lastLoggedIn
from TblPartners a where a.PartnerID = @PartnerID
end
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-05 : 17:14:33

quote:
Originally posted by hanbingl

Mike, you are using sql 2008 right?

Use MERGE if so.

working on moving to it shortly here, unfortunately not using it yet.. good memory tho :) I will look into MERGE when I get to sql2k8 :)

thx!

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-05 : 17:16:31

[quote]Originally posted by Vinnie881


DECLARE @lastLoggedIn datetime,@PartnerID int
SELECT @lastLoggedIn = getDate()

INSERT INTO [tblLogins_Log] (partnerID,IP,loginDate,countryCode) 
select @PartnerID = a.PartnerID,@loginFromIP,@lastloggedIn,@loginFromIP_CountryShort
FROM [tblPartners]  a
WHERE a.emailAddress = @emailAddress and a.password = @password

if not @PartnerID is null
begin
	UPDATE a 
	SET lastLoggedIn = @lastLoggedIn
	from TblPartners a where a.PartnerID = @PartnerID
end

Hey Vinnie,

Appreciated, but I think this was taken from the second query and not my original ..

in your version the first thing I do is an insert into the log table

I only want to insert into the log table, and do the update statement, if the login is successful

if its not successful I wont do anything except return an empty recordset

thx again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 17:46:07

[code]DECLARE @partnerID INT,
@lastLoggedIn DATETIME

SELECT @partnerID = partnerID,
@lastLoggedIn = GETDATE()
FROM tblPartners
WHERE emailAddress = @emailAddress
AND password = @password

IF @partnerID IS NOT NULL
BEGIN
INSERT tblLogins_Log
(
partnerID,
IP,
loginDate,
countryCode
)
VALUES (
@partnerID,
@loginFromIP,
@lastLoggedIn,
@loginFromIP_CountryShort
)

UPDATE tblPartners
SET lastLoggedIn = @lastLoggedIn
WHERE partnerID = @partnerID
END[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 17:53:28

Or try the OUTPUT operator...

UPDATE	tblPartners
SET	lastLoggedIn = GETDATE()
OUTPUT	inserted.partnerID,
	@loginFromIP,
	inserted.lastLoggedIn,
	@loginFromIP_CountryShort
INTO	tblLogins_Log
WHERE	emailAddress = @emailAddress
	AND password = @password

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-06 : 03:32:32

Hi Peso,

Exactly what I was looking for thank you very much. I ended up using your first suggestion.

I'm not fully able to figure out #2. Are you returning an output parameter of INT instead of a recordset ?

So that in the web application, we only grab the output param ? (0 or NULL if the login fails, otherwise we bring back the partnerID)
It doesnt appear that the UPDATE statement is still conditional and thats whats confusing me.

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 03:57:48

[code]UPDATE tblPartners
SET lastLoggedIn = GETDATE()
OUTPUT inserted.partnerID,
@loginFromIP,
inserted.lastLoggedIn,
@loginFromIP_CountryShort
INTO tblLogins_Log
WHERE emailAddress = @emailAddress
AND password = @password

RETURN @@ROWCOUNT[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources