Inserting A Value if the record doesn't exit is SP

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 2000 Forums

Transact-SQL (2000)

Inserting A Value if the record doesn't exit is SP

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2003-07-01 : 15:07:43

I have a storedproc which does a simple select on a table. I would like to, if no records exist, do an insert into the table.... I know I can do this on the page, but I would prefer to do it all in the stored proc. Is there anyway to do this? Is it a case statement? Stored Proc is as follows:

CREATE PROCEDURE CS_GetAD
@SiteID varchar(50)

AS
SELECT ADCode FROM SADS WHERE SiteID = @SiteID)
--If there is no record then Add SiteID to SADS
GO

Thanks guys!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-01 : 15:25:22

Here's one way to do it:


IF ((SELECT COUNT(*) FROM SADS WHERE SiteID = @SiteID) = 0)
BEGIN
  INSERT INTO...
END

Tara

Edited by - tduggan on 07/01/2003 15:25:46

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-01 : 15:29:24


CREATE PROCEDURE CS_GetAD 
	@SiteID varchar(50) 

AS 
	IF ((SELECT COUNT(*) FROM SADS WHERE SiteID = @SiteID) = 0) 
		BEGIN 
			INSERT INTO SADS (SiteId) SELECT @SiteId 
		END
	  ELSE
		BEGIN
			SELECT ADCode FROM SADS WHERE SiteID = @SiteID
		END
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources