HOW TO PASS UPDATE QUERY WITH EXECUTE COMMAND

  • HI,

    I have small confusion send the "update" query in execute statement.

    My requirement is:

    Without using either variable or set quoted_identifier off

    how can i sent the query through procedure.

    my usage as below. its not working for two queries below.

    Exec proce_Name 'update table set column='TEST' where id=1'

    or

    Exec proce_Name "update table set column='TEST' where id=1"

    -------Giri

  • What does the procedure proce_Name do?

    btw, I strongly recommend against any architecture or design that has pieces of queries being passed around as parameters, it gets hugely complex, there are almost always security vulnerabilities as a result and it's a pain to work with them,

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The procedre limit the records based on sending query.

    Suppose if we need to update 10 records ,we need to pass the update query and limit value so it will be updated 10 records...and here query is not only update and also work for. SELECT AND INSERT etc ...IS

    There any default server level configuration to allow double quotes for string values

  • This works

    Exec proce_Name 'update table set column=''TEST'' where id=1'

    Those aren't double quotes, they're escaped single quotes.

    I still strongly recommend against any design that requires the passing of queries or parts of queries around.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks lot

  • I agree with Gail here. Passing TSQL through as input is just asking for trouble.

    Better would be to move the code into a procedure something like this:

    OLD METHOD

    Exec proce_Name 'update table set column=''TEST'' where id=1'

    NEW METHOD

    EXEC dbo.SampleFoo 'MyTable','MyColumn',1,'TEST','UPDATE'

    CREATE PROCEDURE dbo.SampleFoo

    @TableName SYSNAME

    ,@ColumnName SYSNAME

    ,@ID INT

    ,@NewValue VARCHAR(50)

    ,@ActionType VARCHAR(50)

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @strSQL NVARCHAR(4000)

    IF @ActionType = 'UPDATE'

    BEGIN

    SET @strSQL =

    @ActionType + ' '

    + @TableName

    + ' SET '

    + @ColumnName + ' = ''' + @NewValue + ''

    + 'WHERE ID = ' + @ID

    EXEC sp_executeSQL @strSQL

    END

    ELSE IF @ActionType = 'INSERT'

    BEGIN

    SET @strSQL =

    @ActionType + ' INTO ' + @Tablename +

    + '(' + @ColumnName + ')'

    + VALUES +

    + '(' + @NewValue + ')'

    EXEC sp_executeSQL @strSQL

    SELECT @ID = SCOPE_IDENTITY() --this gets the new ID after insertion

    END

  • To be honest, I wouldn't recommend that either. It's vulnerable to SQL injection and it completely violates the software engineering principal of single responsibility. In front end development no one would consider writing a function that can update properties of an employee object, vehicle object, movie object or accounting collection depending on parameter values, so why do it in a stored procedure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply