Sql Stored Procedure Updates

  • Thread starter Thread starter Masahiro Ito
  • Start date Start date
M

Masahiro Ito

I have fairly typical Stored Procedure that should be updating a table,
but is not. It isn't giving any errors, and is quite mystery. It
doesn't change the row in the database (db currently has null values in
the OCN and Switch, this Primary Key NPANXX 416402 should be having all
these values updated??

Stored Procedure created by SQL 2000 Wizard:
ALTER PROCEDURE update_NpaNxx
(@NpaNxx_1 [nvarchar],
@NpaNxx_2 [nvarchar](6),
@RateCenterID_3 [int],
@NPA_4 [int],
@NXX_5 [int],
@OCN_6 [nvarchar](10),
@Switch_7 [nvarchar](12))

AS UPDATE [Telecom].[dbo].[NpaNxx]

SET [NpaNxx] = @NpaNxx_2,
[RateCenterID] = @RateCenterID_3,
[NPA] = @NPA_4,
[NXX] = @NXX_5,
[OCN] = @OCN_6,
[Switch] = @Switch_7

WHERE
( [NpaNxx] = @NpaNxx_1)

exec update_NpaNxx @NpaNxx_1 = N'416402', @NpaNxx_2 = N'416402',
@RateCenterID_3 = 2057, @NPA_4 = 416, @NXX_5 = 402, @OCN_6 = N'8821',
@Switch_7 = N'TOROON061MD'


My VB.NET code (I have filled in the parameters with the 'raw' values as
troubleshooting...
Dim sqlConn As New SqlConnection(sqlDSTel)
Dim sqlCmd As New SqlCommand("update_NpaNxx", sqlConn)

sqlCmd.CommandType = CommandType.StoredProcedure

sqlCmd.Parameters.Add("@NpaNxx_1", "416402")
sqlCmd.Parameters.Add("@NpaNxx_2", "416402")
sqlCmd.Parameters.Add("@RateCenterID_3", 2057)
sqlCmd.Parameters.Add("@NPA_4", 416)
sqlCmd.Parameters.Add("@NXX_5", 402)
sqlCmd.Parameters.Add("@OCN_6", "8821")
sqlCmd.Parameters.Add("@Switch_7", "TOROON061MD")

Try
sqlConn.Open()
Dim i As Integer = sqlCmd.ExecuteNonQuery()
Select Case i
*** OMMITTED FOR BREVITY ***
End Select
Catch ex As Exception
*** OMMITTED FOR BREVITY ***
Finally
sqlConn.Close()
End Try

I also 'captured' the SQL Profiler as it is executing the code for the
stored procedure - which also looks good?:
exec update_NpaNxx @NpaNxx_1 = N'416402', @NpaNxx_2 = N'416402',
@RateCenterID_3 = 2057, @NPA_4 = 416, @NXX_5 = 402, @OCN_6 = N'8821',
@Switch_7 = N'TOROON061MD'

Anyone have any ideas? I can use manual code like:
Update NpaNxx
Set OCN = N'8051', Switch = N'TOROON43DS0'
WHERE NpaNxx = '416401'

But I have hundreds of thousands of updates, and need to use a stored
procedure.

Thanks,
Masa
 
Masahiro Ito said:
I have fairly typical Stored Procedure that should be updating a table,
but is not. It isn't giving any errors, and is quite mystery. It
doesn't change the row in the database (db currently has null values in
the OCN and Switch, this Primary Key NPANXX 416402 should be having all
these values updated??

Stored Procedure created by SQL 2000 Wizard:
ALTER PROCEDURE update_NpaNxx
(@NpaNxx_1 [nvarchar],
@NpaNxx_2 [nvarchar](6),
@RateCenterID_3 [int],
@NPA_4 [int],
@NXX_5 [int],
@OCN_6 [nvarchar](10),
@Switch_7 [nvarchar](12))

AS UPDATE [Telecom].[dbo].[NpaNxx]

SET [NpaNxx] = @NpaNxx_2,
[RateCenterID] = @RateCenterID_3,
[NPA] = @NPA_4,
[NXX] = @NXX_5,
[OCN] = @OCN_6,
[Switch] = @Switch_7

WHERE
( [NpaNxx] = @NpaNxx_1)
Click to expand...


I suspect you already have the answer, but the fact that you didn't get an
exception in VB is another problem. SQL Server will not raise an error when
an update statement affects 0 rows. So when you code an update statement
that YOU know should affect exactly one row, you need to check @@rowcount
and raise an error yourself after the update. Like this

UPDATE T ...
if @@rowcount = 0
begin
raiserror('No matching rows found',16,1)
return 1
end

David
 
I suspect you already have the answer, but the fact that you didn't
get an exception in VB is another problem. SQL Server will not raise
an error when an update statement affects 0 rows. So when you code an
update statement that YOU know should affect exactly one row, you need
to check @@rowcount and raise an error yourself after the update.
Like this

UPDATE T ...
if @@rowcount = 0
begin
raiserror('No matching rows found',16,1)
return 1
end
David
Click to expand...

Thanks for the tip. I thought I was doing a similar thing with:
Try
sqlConn.Open()
Dim i As Integer = sqlCmd.ExecuteNonQuery()
Select Case i
*** OMMITTED FOR BREVITY ***
End Select
Catch ex As Exception
*** OMMITTED FOR BREVITY ***
Finally
sqlConn.Close()
End Try

Is your way more accurate?

Also - I do not have an answer to my problem - the table will not update
with this stored procedure. I can't understand why not.

Masa
 
"=?Utf-8?B?UGFibG8gQmxhbWlyZXo=?=" <Pablo
(e-mail address removed)> wrote in
Have you tried modifying the definition of @NpaNxx_1
from [nvarchar]
to [nvarchar](6)?
Click to expand...

Wow - thanks Pablo - that did it!! I am surprised that the Sql Wizard that
created this did not put that there if it was necessary.


Masa
 
Back
Top