sql update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql update

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-01-08 : 08:15:42

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
)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 08:26:20

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
{ -- () use parenthesis
@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
)
AND
(
@LimRolloverDate is null or LimRolloverDate = @LimRolloverDate
)
AND
(
@LimRolloverPolicy is null or LimRolloverPolicy = @LimRolloverPolicy
)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-01-08 : 08:34:36

Thanks

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-01-08 : 08:51:52

The stored procedure now build but it does not update correctly.
Are you sure the where clause is correct? especially the OR and ANDs?
Tahnks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 08:54:47

Your complete logic is so, so, so wrong!
Your UPDATE statement only work when all parameters is either NULL or equal to old value...

This should be enough

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

update
tblData
set
UnderlyingIndexID = @UnderlyingIndexID,
Nearby = @Nearby,
RolloverTypeID = @RolloverTypeID,
Decimals = @Decimals,
LimRolloverDate = @LimRolloverDate,
LimRolloverPolicy = @LimRolloverPolicy
where
IndexID = @IndexID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources