insert or update with stored procedure

I'm doing this more as a learning exercise than anything else. I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key. If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.

for starters can this even be done with a stored procedure?

if so, can someone provide some guidance as to how?

thanks in advance,

Burr

Yes, this can easily be done. You'll obviously need to do other things with it, though, such as pass in the data that may need to be inserted/updated.

Here's an example, assuming that KeyValue would be either the Primary Key or a Unique constraint field:

 
1CREATE PROCEDURE [dbo].[InsertUpdateProcedure]2(3@.KeyValueint,4@.NewFNamevarchar(30)5)67AS8910IFEXISTS(SELECT KeyValuefrom KeyTableWhere KeyValue = @.KeyValue)11BEGIN12--The row exists. put your update code here13UPDATE KeyTable14SET FName = @.NewFName15WHERE KeyValue = @.KeyValue16END17ELSE18BEGIN19--The row doesn't exist. Insert code goes here20INSERT INTO KeyTable (KeyValue, FName)VALUES (@.KeyValue, @.NewFName)21END222324
 

--ps2goat

|||awesome! tyvm!|||Your Welcome!|||

it's working very well.

I'm actually parsing a csv file to perform my inserts / updates and have run into a new issue. I'm getting an error that says: String or binary data would be truncated. My fields in the database are varchar(8000) and I'm certainly passing a lessor length than that. My first 100 rows were inserted perfecty but it dies with that error. When I look at the values being passed, as I said above, they are significantly less than 8000 chars in length.

any ideas?

|||nevermind...I missed one of the field lenghts...thanks again!