SQL Query

[sangramkp]

I have a table tblSummarySells which have columns column1, column2, column3, column4, column5, column6,column7. I want a stored procedure which will take parameters @c1,@c2,@c3,@c4,@c5,@c6,@c7 for each column respectively.... it will check for the @c1,@c2,@c3,@c4 already present in the tblSummarySells or not 1. If present then it will update the table values of column5, column 6, column7... 2. If not present it will insert the values into the table.... Need help urgent:confused:

[andyharman]

For SQL-Server, your code should be something like:

create procedure MyProcedure
@c1 int,
@c2 int,
@c3 int,
@c4 int,
@c5 int,
@c6 int,
@c7 int
as begin
  set nocount off
 
  update MyTable set c5 = @c5, c6 = @c6, c7 = @c7
     where c1 = @c1
     and c2 = @c2
     and c3 = @c3
     and c4 = @c4
 
  if (@@ROWCOUNT = 0) begin
     insert into MyTable (c1, c2, c3, c4, c5, c6, c7)
       values (@c1, @c2, @c3, @c4, @c5, @c6, @c7)
  end

  return (0)
end

I have assumed that all of your variables are integers, and that none of your primary key columns can be null. Regards Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

[sangramkp]

Thanks But I have data already in table so is @@rowcount=0 going to work

[andyharman]

The @@ROWCOUNT variable holds the number of records hit by the previous update statement.

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

[Pete OHanlon]

In Sql Server from version 2000 onwards there is a feature called an INSTEAD OF trigger that you can use. To use it, you create a copy of your table as a view and use this as the target of your inserts. Behind this view, create a trigger with the signature INSTEAD OF INSERT where you would normally put the FOR INSERT. The trigger would look something like this: CREATE TRIGGER InsertIntoSummarySells INSTEAD OF INSERT AS BEGIN DECLARE @C1 INT -- or whatever type it is DECLARE @C2 INT -- ... ... -- And so on, with the parameters SET NOCOUNT ON UPDATE tblSummarySells SET C5 = @C5, C6 = @C6, C7 = @C7 WHERE C1 = @C1 AND C2 = @C2 AND C3 = @C3 AND C4 = @C4 IF @@ROWCOUNT = 0 BEGIN INSERT INTO tblSummarySells .... END END Now, the advantage of this approach is that anytime you do an insert onto the view, this code will run so it should always behave consistently - rather than having to remember to call the stored procedure to perform the update.

Deja View - the feeling that you've seen this post before.