insert if query is not 1 - 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

insert if query is not 1

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-10 : 05:31:04

Hi i have a stored procedure, i need to find out if a record exsists then if not then insert so i have this code, just not sure of the syntax.

/****** MATCH UP ADDRESS ID TO THE F4 RENTAL ******/
SELECT COUNT(R_Web_Add_ID)AS NewR_Web_Add_ID
FROM TBL_RENTAL
WHERE R_Web_Add_ID = @R_Add_ID

/****** IF MATCH IS LT 1 THEN INSERT ADDRESS INTO RENTAL ******/
if NewR_Web_Add_ID < 1 then
insert statement here
end if

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 05:59:23

IF NOT EXISTS (SELECT * FROM TBL_RENTAL WHERE R_WEB_ADD_ID = @R_ADD_ID)
INSERT ...

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-10 : 05:59:33

It won't work that way. I would suggest to use NOT EXISTS operator instead.

IF NOT EXISTS(SELECT * FROM TBL_RENTAL WHERE R_Web_Add_ID = @R_Add_ID)
BEGIN
  INSERT....
END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-10 : 09:16:34

ok thanks, i have tried that but i get this error

The statement has been terminated.

/****** IF MATCH IS LT 1 THEN INSERT ADDRESS INTO RENTAL ******/
IF NOT EXISTS(SELECT R_Web_Add_ID
FROM TBL_RENTAL
WHERE R_DB_CODE = 'AE' AND R_Web_Add_ID = @R_Add_ID)
BEGIN
INSERT INTO TBL_RENTAL
(R_DB_Code, R_Name, R_Add_2, R_Add_3, R_Add_4, R_Add_5, R_Add_6, R_PostCode, R_Visable, R_Add_1)
VALUES
(@R_Code, @R_Name, @R_Add_1, @R_Add_2, @R_Add_3, @R_Add_4, @R_Add_5, @R_PostCode, -1, @R_Contact)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 09:19:13

Can you post the full error message?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-10 : 09:19:20

Are you using SQL server? what's the error text?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 09:19:48

If you want our help, why don't you post the actual error message?