IF/THEN/ELSE

[Guest]

I'm working on my first SQL Server application using Access as the front-end.
As I do understand the most simple stored procedures and use defined
functions, I have some things I'm not quite sure how to handle. List below is
an example of what I need to do and my goal is to handle the IF/THEN clause
within a single Stored Procedure, as I think this would be best.

I have a table called, tblTableSub, which may or may not have a record in
it. This table will contain one record based on the "TableID" value. If a
current record does exist, then I need to update that record, ELSE, if no
record exists, I need to ADD a record. Can someone give me the basic syntax
to build the IF/THEN structure?

 

[Sylvain Lafontaine]

Something like:

if Exists (Select * From tblTableSub Where tblTableSub.TableID = @Value)
Begin
update tblTableSub Set ....
End
Else
Begin
insert tblTableSub ....
end

The Begin ... End statements are used to group multiple statements into a
single block and are not needed if you have only one statement to be
executed by the IF or the Else clause.

A properly written SP will also look for any error by testing the @@error
and @@rowcount values and might also use transactions and set the proper
isolation level but I don't think you need this level of complexity right
now.

 

[aaron.kempf]

in SQL 2005; and the next version of Access Data Projects; you can use
TRY / CATCH within a sql statement; it's a pretty darn slick option


-Aaron