Stored Procedure help - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure help

Author

Topic

Darkmatter5
Starting Member

17 Posts

Posted - 2012-05-10 : 14:53:22

I'm trying to write an stored procedure, that inserts, but checks that the input parameter doesn't already exist. How should I go about doing this? Here's what I have so far.


USE byrndb
GO

IF OBJECT_ID('spInsertCounty') IS NOT NULL DROP PROCEDURE spInsertCounty
GO
CREATE PROC spInsertCounty
     @Name varchar(50)
AS
IF (SELECT COUNT(*) FROM counties WHERE counties.CountyName = @Name) > 0
     INSERT INTO counties VALUES (UPPER(@Name))
ELSE
     PRINT 'County name selected already exists.'
END
GO

Thanks for your help in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-10 : 14:59:35

What you have should work. You can make it slightly more efficient by getting rid of the COUNT(*) function like this:

IF NOT EXISTS( SELECT * FROM counties WHERE CountyName = UPPER(@Name))
BEGIN
	INSERT INTO counties VALUES (UPPER(@Name));
END
ELSE
	BEGIN
    PRINT 'County name selected already exists.'
	END

Darkmatter5
Starting Member

17 Posts

Posted - 2012-05-10 : 15:16:52

Worked, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:37:39

are you using case sensitive collation? if not you shouldnt be worried on case of data stored in your table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources