CODEWITHSUNDEEP
sql-server
Dean.DePue
Dean.DePue

Reputation: 1013

How to update SQL Server column with table name, id, and column name

I have a procedure that reads a column value when I enter the table name, record ID, and field name:

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

 ALTER PROCEDURE [dbo].[GET_TEXT_DETAIL]
    @id uniqueidentifier,
    @table varchar(255),
    @field varchar(max)
 AS
 BEGIN
     SET NOCOUNT ON;

     DECLARE @sql VARCHAR(200)

     SET @sql = 'select ' + QUOTENAME(@field) + ' from ' + QUOTENAME(@table) + ' where ID = ''' + cast(@id as varchar(36)) + ''''

     EXEC(@sql)
 END

Now I am trying to update that column with the same approach:

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

 ALTER PROCEDURE [dbo].[UPDATE_TEXT_DETAIL]
     @id uniqueidentifier,
     @table varchar(255),
     @field varchar(max),
     @fieldvalue varchar(max)
 AS
 BEGIN
     SET NOCOUNT ON;

     DECLARE @sql varchar(200)

     SET @sql = 'update ' + QUOTENAME(@table) + ' set ' + QUOTENAME(@field) + ' = ''' + QUOTENAME(@fieldvalue) + ''' where ID = ''' + cast(@id as varchar(36)) + ''''

     EXEC(@sql)
 END

But I get multiple errors depending upon what I do. The record ID is a uniqueidentifier. Plus, the column value sometimes updates with the [] characters around it. I know this is the default for QUOTENAME but I want to get around that. When I use the single quote I get errors about the ID.

Is there any way to do this?

Upvotes: 0

Views: 473

Answers (1)

Sujeet Sinha
Sujeet Sinha

Reputation: 2433

Try this ->

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UPDATE_TEXT_DETAIL]
@id         uniqueidentifier,
@table      varchar(255),
@field      varchar(max),
@fieldvalue varchar(max)
AS

BEGIN

    SET NOCOUNT ON;

    declare @sql varchar(200)
    --set @sql = 'select ' + QUOTENAME(@field) + ' from ' + QUOTENAME(@table) + ' where ID = ''' + cast(@id as varchar(36)) + ''''
    set @sql = 'update ' + QUOTENAME(@table) + ' set ' + QUOTENAME(@field) + ' = ' + QUOTENAME(@fieldvalue, '''') + ' where ID = ''' + cast(@id as varchar(36)) + ''''
    EXEC(@sql)


END

Upvotes: 1

Related Questions