[Resolved] Check if record exists in table

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)

[Resolved] Check if record exists in table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-01-26 : 11:15:13

From my asp application I need to check if a record exists prior to insert, update and delete. I would like to call a stored procedure and return count (or may be there is a better way).

declare @name varchar(50)
Select count(*) from dbo.table where name = @name

Now, how can I return the result of count as a variable? In my application I need to grab the return value of count. Do I need to declare an "out" parameter?

Thank you.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 11:26:53

You can use RETURN @MyCount from the SProc. This is regarded as bad-form as it is normally used to indicate success / error no.

You can return it as an OUTPUT parameter from the Sproc

Or you can return it as a ResultSet (using SELECT COUNT(*) ... as you have it)

To return it as an Output parameter you need


CREATE PROCDURE MyProc @name varchar(50), @MyCount INT OUTPUT
AS
Select @MyCount = count(*) from dbo.table where name = @name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:27:18

no need of separate proc, just use check like below in your insert proc itself

IF NOT EXISTS(SELECT 1 FROM table WHERE field1=@Param1...)

--insert code

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 11:40:43

Very good point Visakh.

If you check the COUNT in your program and then find it does not exist, so you use INSERT how do you know that someone else has not added it at just that time?

This becomes more of a problem if your application has high concurrency.

We store an EditNumber column in all tables; this is incremented when the record is changed. When we display an Edit Form that has a (hidden) copy of the EditNumber. When the record is saved we check that the EditNumber has not changed (otherwise someone else changed the record

)

If the user is creating the New Record then the EditNumber=0 (when the form is saved). If the record exists by the time the user saves it then the INSERT process discovers the existing record and raises an error back to the user.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-01-26 : 11:43:20

Using the "If not exists ...." how will I be able to tell whether sucess or not so I can show a dialog box? Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:49:33

quote:
Originally posted by snufse

Using the "If not exists ...." how will I be able to tell whether sucess or not so I can show a dialog box? Thank you.

IF NOT EXISTS(SELECT 1 FROM table WHERE field1=@Param1...)
BEGIN
--insert code
RETURN 1
END
ELSE
RETURN 0

then in application check the return value and if 0 show the dialog box

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-01-26 : 12:37:01

Wonderful, thank you very much..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 12:37:24

welcome

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:48:19

Note that there is still a risk that someone adds the record between your IF NOT EXISTS test and your INSERT CODE.

If this is vanishingly small odds in your environment then it would be reasonable to ignore that scenario.

If you have concerns about that ask back and I can advise on sure-fire way of catching / preventing that.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-01-26 : 14:46:19

Kristen, thank you I will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 15:45:49

Here is one way to deal with all three; UPDATE, INSERT or DELETE
In one procedure, because doing a COUNT or an EXISTS forces SQL Server to do a scan (seek if index exists on custname).

CREATE PROCEDURE dbo.uspCustomerSave
(
	@CustID INT OUT = NULL,
	@CustName VARCHAR(40)
)
AS 

SET NOCOUNT ON

IF @CustName IS NULL
	BEGIN
		DELETE
		FROM	dbo.Customers
		WHERE	CustID = @CustID

		RETURN
	END

BEGIN TRANSACTION

UPDATE	dbo.Customers
SET	CustName = @CustName
WHERE	CustID = @CustID

IF @@ROWCOUNT = 0 AND @@ERROR = 0
	BEGIN
		INSERT	dbo.Customers
			(
				CustName
			)
		VALUES	(
				@CustName
			)

		IF @@ROWCOUNT = 1 AND @@ERROR = 0
			BEGIN
				SET	@CustID = SCOPE_IDENTITY()
				COMMIT TRANSACTION
			END
		ELSE
			ROLLBACK TRANSACTION
	END
ELSE
	ROLLBACK TRANSACTION

EDIT: Transaction as requested from Kristen

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

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 16:23:40

I think there is a risk, with this method, that UPDATE returns 0 rows and the INSERT then fails because customer has just-been-inserted

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 03:36:04

@Peso: Is that UPDATE going to lock the Key for a missing record? That's not my expectation (I could be wrong though)

I'll put my suggestion here in case snufse ever needs it. I might be too old to remember what it was when the O/P comes back to ask!!


INSERT	dbo.Customers
(
	CustName
)
SELECT	@CustName
WHERE	NOT EXISTS (SELECT * FROM dbo.Customers WHERE CustID = @CustID)
SELECT	@MyRowCount = @@ROWCOUNT, @MyErrorNo = @@ERROR, @CustID = SCOPE_IDENTITY()

IF @MyRowCount = 0 AND @MyErrorNo = 0
BEGIN
	UPDATE	dbo.Customers
	SET	CustName = @CustName
	WHERE	CustID = @CustID
	SELECT	@MyRowCount = @@ROWCOUNT, @MyErrorNo = @@ERROR
END
IF @MyRowCount <> 1
BEGIN
	... error handling here ... (Most likely record was deleted between INSERT attempt and UPDATE-if-existing)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources