Results 1 to 4 of 4

Thread: sql update

  1. #1
    Join Date
    Aug 2006
    Posts
    39

    Unanswered: sql update

    I am building a stored procedure (sp) as below to update a table.
    Some of the parameters may or may not be provided hence null values...
    Is what I am doing correct please?
    I get this error when running it:
    [Microsoft][ODBC SQL Server Driver]Syntax error or access violation

    Thanks


    Create PROCEDURE uspUpdate

    @IndexID int,
    @UnderlyingIndexID int = null,
    @Nearby int = null,
    @RolloverTypeID int = null,
    @Decimals tinyint = null,
    @LimRolloverDate datetime null,
    @LimRolloverPolicy varchar(50) = null

    AS

    declare @Error int
    declare @Msg varchar(128)

    declare @sql varchar(1000)

    update
    tblData
    set
    UnderlyingIndexID = @UnderlyingIndexID,
    Nearby = @Nearby,
    RolloverTypeID = @RolloverTypeID,
    Decimals = @Decimals,
    LimRolloverDate = @LimRolloverDate,
    LimRolloverPolicy = @LimRolloverPolicy
    where
    IndexID = @IndexID
    AND
    {
    @UnderlyingIndexID is null or UnderlyingIndexID = @UnderlyingIndexID
    }
    AND
    (
    @Nearby is null or Nearby = @Nearby
    )
    AND
    (
    @RolloverTypeID is null or RolloverTypeID = @RolloverTypeID
    )
    AND
    (
    @Decimals is null or Decimals = @Decimals
    )
    (
    @LimRolloverDate is null or LimRolloverDate = @LimRolloverDate
    )
    AND
    (
    @LimRolloverPolicy is null or LimRolloverPolicy = @LimRolloverPolicy
    )

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's with the brackets?

    {}
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2006
    Posts
    39
    Yes, I have now taken out the brackets and the stored procedure compiles.
    Not sure why the update does not update the fields. I think there is a problem with the where clause.
    Thanks

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also I think you need another AND before this part, no?

    (
    @LimRolloverDate is null or LimRolloverDate = @LimRolloverDate
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •