Insert with condition

Insert with condition

• Danny Fürniß

  I tried to use dynamic sql in stored procedure like this:
  create PROCEDURE [dbo].[UpdateTable1]
  @id varchar(30),
  @col varchar(30),
  @value varchar(30)
  AS
  BEGIN
  
  update table1
  set col1 = case @col when 'col1' then @value end
  set col2 = case @col when 'col2' then @value end
  where id = @id
  
  End
  
  do you think it is right way
  
  

• A.Gharighi

  Remove your second SET statement
  
  create PROCEDURE [dbo].[UpdateTable1]
  @id varchar(30),
  @col varchar(30),
  @value varchar(30)
  AS
  BEGIN
  
  update table1
  set col1 = case @col when 'col1' then @value end,
  col2 = case @col when 'col2' then @value end
  where id = @id
  
  End
  
  
  HTH,
  
  

• Emacs

  But sql server has another idea it says there is syntax error (near '=').do you know what is wrong with it when i check CASE in books, the syntax is right..So i don't know the problem.
  
  

• GKNewYork

  you can resort to dynamic SQL, e.g.
  
  EXEC('insert into table (' + @columnName + ') VALUES (' + @value + ') WHERE ID =' + @id)
  
  HTH,
  
  

• SoopahMan

  that's just fine... but typing will be tedious if you have many columns in your table.
  
  
  

• Cim Ryan

  Thanks alot. finally works
  
  

• Tardomatic

  no prob... glad to be of help
  
  
  

• fly-007

  I am not sure about the cause of failure in your C# application, but the proc UpdateData will not give you desired result because " set @dataCol= @dataVal" will set the variable @dataCol to have the value of the @dataVal. The column is not touched at all. You can use dynamic sql inside the proc to achieve what you desire.

  Thanks

  
  

• Espens

  Thanks for your reply. But that was my mistake that i thought Insert is what i need. I should use Update instead.Anyway it is the same story. I am using that in an c# application(web service).
  I have this in my code:
  objConnect = new SqlConnection(connectionString);
  string queryString = "upadte MyTable set "+col+"= "+data+" where DeviceID = "+id+";";
  try
  {
  objConnect.Open();
  SqlCommand objCommand = new SqlCommand(queryString, objConnect);
  objCommand.ExecuteReader();
  }
  ...
  
  It doesn't work, and because it is inside a web method i get the SoapException ..
  I also tried with stored procedure,like this:
  ALTER PROCEDURE [dbo].[UpdateData]
  @devID varchar(30),
  @dataCol varchar(30),
  @dataVal varchar(30)
  AS
  BEGIN
  SET NOCOUNT ON;
  
  update DataTransmission
  set @dataCol= @dataVal
  where DeviceID = @devID
  END
  and it also doen't work when i do : exec UpdateData 'id','user','me'
  Do you know what is going wrong
  
  
• Insert with condition