-2

Lets say I want to write a stored prod

For SELECT

I found the above link for SELECT, but I want to do is for UPDATE:

SpUpdate @TableName varchar(50), @ColumnName varchar(50), @Value int, @Condition int

AS
BEGIN
 UPDATE @Tablename
 SET @ColumnName = @Value
 Where PrimaryColName = @Condition
END

I know above code wont run. I know you can write a Dynamic code but I am not interested in Dynamic code. Can anyone help with different approch. Maybe using case statement or If statemens.

Community
  • 1
  • 1
Matt S
  • 33
  • 2

1 Answers1

0
CREATE PROCEDURE SpUpdate 
  @TableName  SYSNAME
, @ColumnName SYSNAME
, @Value      INT
, @Condition  INT
AS
BEGIN
  SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(MAX);

SET @Sql =  N' UPDATE ' + QUOTENAME(@TableName)
          + N' SET ' + QUOTENAME(@ColumnName) + N' = @Value '
          + N' WHERE PrimaryColName = @Condition '

EXECUTE sp_executesql @Sql
                     ,N'@Value INT, @Condition INT'
                     ,@Value
                     ,@Condition
END
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • you cannot do this without dynamic sql. by the way why you dont want to use dynamic sql ? any particular reason ? – M.Ali Oct 16 '14 at 19:18
  • party of company policy. Thanks – Matt S Oct 16 '14 at 19:20
  • is policy maker a SQL Server DBA ? if he is show him this code and his mind will be at peace, if he is not a DBA dont bother listening to him . – M.Ali Oct 16 '14 at 19:21
  • hehe. I need to survive. – Matt S Oct 16 '14 at 19:26
  • I reckon whoever has made this policy, have their doubts about sql injection, this piece of code I have written for you is clean and protected against sql injection attack. show it to your dba and it should be ok. – M.Ali Oct 16 '14 at 19:27
  • Can you explain the N' for me. That is kind of new to me. Once again, thanks a lot for all your help. – Matt S Oct 17 '14 at 18:24