Stored Procedure check for recordcount 2

[Westond]

I have a stored proc and what I would like to do is run a select query then based on the recordcount insert or edit.

SELECT * FROM Table

if recordcount = 1
UPDATE Table set x = 1
WHERE...
else
INSERT INTO Table ()VALUES()

THANKS

Wes

 

[gmmastros]

If Exists(Select * from Table Where SomeField = @SomeValue)
Begin
-- Update Record
End
Else
Begin
-- Insert Record
End



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

declare @recordcount int
SELECT @recordcount = count(*) FROM Table

if @recordcount = 1
begin
UPDATE Table set x = 1
WHERE...
end
else
begin
INSERT INTO Table ()VALUES()
end


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Be careful with that syntax. If @RecordCount = 2, You'll end up doing another insert. If that's what you want, then cool. Go for it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Yes, if you want to check it the table is empty use George's method
Ddidn't quite understand what you are trying to accomplish

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Westond]

Thanks guys that will work perfectly : D

 

[vbkris]

hi guys, i have a doubt here in effeciency,

aint count more effecient than Exists (which has a query that selects all records)???

Known is handfull, Unknown is worldfull

 

[gmmastros]

Exists is faster. Think about it. To get a count, you have to examine all the records to selectively include in the count. With exists, as soon as the first match is found, processing the records ends and it returns a true. Don't believe me. Do a little googling to see for yourself. Here's one I found.

http://weblogs.asp.net/wallym/archive/2004/12/20/327738.aspx

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vbkris]

thanx dude...

Known is handfull, Unknown is worldfull