IF then in Store Proc

[zishan876]

Hi I have the following:

ALTER  procedure latlong_export
@SF_GUID char(18)
,@SF_NAME char(28)
,@SF_STREET char(28)
,@SF_CITY char(21)
,@SF_STATE char(2)
,@SF_ZIP char(10)
,@SF_RECORD_TYPE varchar(18)
as

IF @SF_GUID = SF_GUID
insert into SF_Locations
(SF_GUID
,SF_NAME
,SF_STREET
,SF_CITY
,SF_STATE
,SF_ZIP
,SF_RECORD_TYPE)
values
(@SF_GUID
,@SF_NAME
,@SF_STREET
,@SF_CITY
,@SF_STATE
,@SF_ZIP
,@SF_RECORD_TYPE)

Now before it goes into the insert statement i want it to check the database to see if the record exist already if it does then do not do anything...
Can I do that in a store proc and how?
Thanks

 

[bborissov]

....
IF NOT EXISTS(SELECT * FROM SF_Locations WHERE SF_GUID = @SF_GUID)
   insert into SF_Locations
      (SF_GUID
      ,SF_NAME
      ,SF_STREET
      ,SF_CITY
      ,SF_STATE
      ,SF_ZIP
      ,SF_RECORD_TYPE)
   values
      (@SF_GUID
      ,@SF_NAME
      ,@SF_STREET
      ,@SF_CITY
      ,@SF_STATE
      ,@SF_ZIP
      ,@SF_RECORD_TYPE)
ELSE
   ...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[zishan876]

awesome thanks
I also tried this which seemed to work

select 'Map_done'=count(*) from SF_LOcations
where SF_INSERT_RECORD='n'

select 'MapNotDone'= count(*) from SF_LOcations
where SF_INSERT_RECORD='y'

select 'NA_Done' =count(*) from sfnafile
where sf_update_record='y'


Select count(*),SF_STATUS,SF_GUID,SF_ACCT from SF_LOCATIONS where SF_INSERT_RECORD='Y' GROUP BY SF_STATUS,SF_GUID,SF_ACCT

select * from SF_Locations_history where sfuploaded=0 order by datetimestamp 

SELECT SF_ACCT, Latitude, Longitude,SF_STREET
FROM SF_LOCATIONS
WHERE (((SF_ACCT) In (SELECT SF_ACCT FROM SF_LOCATIONS As Tmp GROUP BY SF_ACCT HAVING Count(*)>1 )))
Order by SF_ACCT


SELECT SF_GUID FROM SF_LOCATIONS WHERE 
SF_GUID IN (SELECT SF_GUID FROM SF_LOCATIONS GROUP BY SF_GUID HAVING COUNT(*) >1) AND SF_RECORD_TYPE IS NOT NULL


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



ALTER   procedure latlong_export
@SF_GUID char(18)
,@SF_NAME char(28)
,@SF_STREET char(28)
,@SF_CITY char(21)
,@SF_STATE char(2)
,@SF_ZIP char(10)
,@SF_RECORD_TYPE varchar(18)
as
DECLARE @SF_GUIDD char(18)
SET @SF_GUIDD=(SELECT SF_GUID FROM SF_LOCATIONS WHERE SF_GUID=@SF_GUID)
IF @SF_GUIDD IS NULL
insert into SF_Locations
(SF_GUID
,SF_NAME
,SF_STREET
,SF_CITY
,SF_STATE
,SF_ZIP
,SF_RECORD_TYPE)
values
(@SF_GUID
,@SF_NAME
,@SF_STREET
,@SF_CITY
,@SF_STATE
,@SF_ZIP
,@SF_RECORD_TYPE)










GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

but yours works fine as well...
cool

 

[bborissov]

Using EXISTS is faster because SELECT stops after the first record found.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.