Update 1 of 3 tables based on parameter

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  June 29, 2016 at 8:45 pm

  #1887764

  David92595 (6/28/2016)

  ---

  Hello,

  I am trying to dynamically update a table (as much as possible) based on a parameter. However, because I'm trying to avoid SQL injection I'm hard coding the table names, but still need to be able to select which one based on another parameter (@State).

  @ID int,

  @State varChar,

  @ColumnName varchar,

  @NewValue datetime

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  -- Insert statements for procedure here

  SELECT CASE @State

  Case 'AZ' THEN

  UPDATE AZ_Fees

  SET @ColumnName = @NewValue

  WHERE ID = @ID

  Case 'CA' THEN

  UPDATE CA_Fees

  SET @ColumnName = @NewValue

  WHERE ID = @ID

  Case 'HI' THEN

  UPDATE HI_Fees

  SET @ColumnName = @NewValue

  WHERE ID = @ID

  What is the best way to go about this?

  David92595

  If you're asking for the best way to go about this, then you should look in this direction:

  CREATE TABLE #CustomerFee (

  StateID CHAR(2) COLLATE DATABASE_DEFAULT NOT NULL,

  FeeType VARCHAR(30) COLLATE DATABASE_DEFAULT NOT NULL,

  ID INT NOT NULL,

  Value DATETIME -- ?? datetime?

  PRIMARY KEY CLUSTERED (StateID, FeeType,ID)

  )

  -- populate the new table from "spreadsheets"

  /*

  this code may be generated dynamically from INFORMATION_SCHEMA.COLUMNS,

  and you do not need to worry about SQL injections here, as all the parameters are coming from system views

  */

  INSERT INTO #CustomerFee ( StateID, FeeType, ID, Value )

  SELECT 'AZ', ColumnName1, ID, Value

  FROM AZ_Fees

  WHERE ColumnName1 is not null and ID is not null

  UNION ALL

  SELECT 'AZ', ColumnName2, ID, Value

  FROM AZ_Fees

  WHERE ColumnName2 is not null and ID is not null

  ......

  UNION ALL

  SELECT 'HI', ColumnNameN, ID, Value

  FROM HI_Fees

  WHERE ColumnNameN is not null and ID is not null

  --then here is the code you put in your procedure:

  CREATE PROC ......

  @ID int,

  @State varChar,

  @ColumnName varchar,

  @NewValue datetime

  AS

  SET NOCOUNT ON;

  UPDATE #CustomerFee

  SET Value = @NewValue

  WHERE StateID = @State

  AND FeeType = @ColumnName

  AND ID = @ID

  As a next step you might wish to put FeeType into a separate table leaving FeeTypeID smallint in the main table referencing TeeType table with a Foreign Key.

  _____________
  Code for TallyGenerator