locked
Dynamic Update & Insert Statement RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below code. I want this field "1" to be dynamic. with what value be in the variable say @FieldName. Vlaue is in variable

    if day(@DocDate) = 1 begin Update #tbl1 set "1" = @Amount WHERE BpName = @BpName End

    Insert into #tbl1(BpName,"1") values (@BpName,@Amount)

    Thanks

    Monday, July 20, 2020 12:31 PM

All replies

  • User-1330468790 posted

    Hi jsshivalik,

     

    Below dynamic update statement would be a simple demo so that you have to make it suitable for your database.

    I use a temp table and dynamic sql statement to achieve the target. You could refer to below codes.

    DROP TABLE IF EXISTS #TestTable
    
    CREATE TABLE #TestTable
    (
        BpName  NVARCHAR(50),
        SampleNumber INT,
        SampleDate DATETIME
    )
    
    INSERT INTO #TestTable(BpName,SampleNumber,SampleDate) VALUES ('XXX',111,'2020/07/07')
    INSERT INTO #TestTable(BpName,SampleNumber,SampleDate) VALUES ('YYY',222,'2020/07/08')
    INSERT INTO #TestTable(BpName,SampleNumber,SampleDate) VALUES ('ZZZ',333,'2020/07/09')
    
    DECLARE
    
        @SQL VARCHAR(max),
        @ColumnName VARCHAR(50),
        @BpName NVARCHAR(50),
        @Amount INT,
        @ReturnValue INT
    
    SET @ColumnName = 'SampleNumber'
    SET @BpName = 'XXX'
    SET @Amount = 123456
          
    SET @SQL = 'UPDATE #TestTable SET ' + quotename(@ColumnName)  +' = ' + CAST(@Amount AS NVARCHAR(50)) + ' WHERE BpName =' + quotename(@BpName,'''')
    
    PRINT @SQL
          
    BEGIN TRY
        EXEC (@SQL)
        SET @ReturnValue = 1 --Success
    END TRY
    
    BEGIN CATCH
        SET @ReturnValue = 0 --Failure
        PRINT 'SQL: ' + @SQL
        PRINT 'ERROR_NUMBER(): ' + CAST(ERROR_NUMBER() AS VARCHAR(50))
        PRINT 'ERROR_MESSAGE(): ' + ERROR_MESSAGE() 
    END CATCH
    
    PRINT @ReturnValue SELECT * FROM #TestTable DROP TABLE #TestTable

     Result:

     

    Apart from implementation, you may still need to think about SQL Injection risks in dynamic sql statement if you would use dynamic sql in a more complex scenario.

     

    Hope this can help you.

    Best regards,
    Sean

    Tuesday, July 21, 2020 8:05 AM