Stored Procedure updating columns Dynamically
I have a stored procedure that I want to have save a value to a column based on the input I throw at it. I'm checking for existing column and datatype.
@Registration_ID NUMERIC
,@ColumnName VARCHAR(255)
,@ColumnValue VARCHAR(255)
,@ColumnDataType VARCHAR(255) OUTPUT
SELECT @ColumnDataType = [Data_Type]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Checklist'
AND COLUMN_NAME=@ColumnName
IF @ColumnDataType <> ''
----- @ColumnDataType here is set to a value of 'bit'
UPDATE [dbo].[Checklist]
SET @ColumnName =@ColumnValue
,[Modified_Timestamp] = CURRENT_TIMESTAMP
,[Modified_Action] = 'Update'
WHERE [Registration_ID] = @Registration_ID
END
However when I run the SP, it does not update the column to the value I supply.
I tried using CAST with a dynamic SQL but it ended up in misery, so I'm avoiding that route unless I have to.
The SQL I used was:
SET @ColumnName = CAST(@ColumnValue AS @ColumnDataType)
Any suggestions as to what I'm doing wrong?
@Registration_ID NUMERIC
,@ColumnName VARCHAR(255)
,@ColumnValue VARCHAR(255)
,@ColumnDataType VARCHAR(255) OUTPUT
SELECT @ColumnDataType = [Data_Type]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Checklist'
AND COLUMN_NAME=@ColumnName
IF @ColumnDataType <> ''
----- @ColumnDataType here is set to a value of 'bit'
UPDATE [dbo].[Checklist]
SET @ColumnName =@ColumnValue
,[Modified_Timestamp] = CURRENT_TIMESTAMP
,[Modified_Action] = 'Update'
WHERE [Registration_ID] = @Registration_ID
END
However when I run the SP, it does not update the column to the value I supply.
I tried using CAST with a dynamic SQL but it ended up in misery, so I'm avoiding that route unless I have to.
The SQL I used was:
SET @ColumnName = CAST(@ColumnValue AS @ColumnDataType)
Any suggestions as to what I'm doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had started going in this direction but was thinking about the string wrong.
Your example cleared up for me a lot and helped me get to a working solution!
Ended up using SP_EXECUTESQL, that way I could declare the variables I was passing and was able to type them correctly :) thankyouthankyouthankyou!!
This is what the final code ended up looking like:
SET @SQLString=N'UPDATE [dbo].[Checklist]
SET '+@ColumnName+' = @ColValue
,[Modified_Operator] = @Oper
,[Modified_Timestamp] = CURRENT_TIMESTAMP
,[Modified_Action] = ''Update''
WHERE [Registration_ID] = @Reg_ID';
SET @Params=N'@ColValue '+@ColumnDataType+'
,@Oper VARCHAR(10)
,@Reg_ID NUMERIC';
EXECUTE SP_EXECUTESQL @SQLString
,@Params
,@ColValue=@ColumnValue
,@Oper=@Operator
,@Reg_ID=@Registration_ID;