Error converting data type nvarchar to int.

microsoft.public.sqlserver.programming

13 May 2005 3:33 AM

Patrick.O.Ige

With the stored procedure below if i do
DECLARE @ProductName nvarchar(40),@ProductID int
EXEC updateProduct
@ProductName = ProductName,
@ProductID = ProductID

why do i get error:- Error converting data type nvarchar to int.

--------------------------------------
ALTER PROCEDURE updateProduct
@ProductID int,
@ProductName nvarchar(40),
@LastUpdate datetime
AS
UPDATE
Products
SET
ProductName = @ProductName
WHERE
ProductID = @ProductID AND
LastUpdate = @LastUpdate

IF @@ROWCOUNT > 0
-- This statement is used to update the DataSet if changes are done on the
updated record (identities, timestamps or triggers )
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
FROM Products
WHERE ProductID = @ProductID
GO

13 May 2005 3:41 AM

Chandra

Hi Patrick,
The order in which you pass the parameters looks wrong.
Just try it this way:
EXEC updateProduct
@ProductID = ProductID,
@ProductName = ProductName,
@LastUpdate = LastUpdate

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---------------------------------------

Show quote

"Patrick.O.Ige" wrote:

> With the stored procedure below if i do
> DECLARE @ProductName nvarchar(40),@ProductID int
> EXEC updateProduct
> @ProductName = ProductName,
> @ProductID = ProductID
>
> why do i get error:- Error converting data type nvarchar to int.
>
>
> --------------------------------------
> ALTER   PROCEDURE updateProduct
> @ProductID int,
> @ProductName nvarchar(40),
> @LastUpdate datetime
> AS
> UPDATE
>   Products
> SET
>   ProductName = @ProductName
> WHERE
>   ProductID = @ProductID AND
>   LastUpdate = @LastUpdate
>
> IF @@ROWCOUNT > 0
>   -- This statement is used to update the DataSet if changes are done on the
> updated record (identities, timestamps or triggers )
>   SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
>   FROM Products
>   WHERE ProductID = @ProductID
> GO

13 May 2005 4:18 AM

Patrick.O.Ige

Thx but if i do :-
DECLARE @ProductID int,
@ProductName nvarchar(40),
@LastUpdate datetime

EXEC updateProduct
@ProductID = ProductID,
@ProductName = ProductName,
@LastUpdate = LastUpdate

It still gives the error....
I want results in the Query Analyzer!

Show quote

"Chandra" wrote:

> Hi Patrick,
> The order in which you pass the parameters looks wrong.
> Just try it this way:
> EXEC updateProduct
> @ProductID = ProductID,
> @ProductName = ProductName,
> @LastUpdate = LastUpdate
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.developersdex.com/gurus/default.asp?p=4223
> ---------------------------------------
>
>
>
> "Patrick.O.Ige" wrote:
>
> > With the stored procedure below if i do
> > DECLARE @ProductName nvarchar(40),@ProductID int
> > EXEC updateProduct
> > @ProductName = ProductName,
> > @ProductID = ProductID
> >
> > why do i get error:- Error converting data type nvarchar to int.
> >
> >
> > --------------------------------------
> > ALTER   PROCEDURE updateProduct
> > @ProductID int,
> > @ProductName nvarchar(40),
> > @LastUpdate datetime
> > AS
> > UPDATE
> >   Products
> > SET
> >   ProductName = @ProductName
> > WHERE
> >   ProductID = @ProductID AND
> >   LastUpdate = @LastUpdate
> >
> > IF @@ROWCOUNT > 0
> >   -- This statement is used to update the DataSet if changes are done on the
> > updated record (identities, timestamps or triggers )
> >   SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
> >   FROM Products
> >   WHERE ProductID = @ProductID
> > GO

13 May 2005 4:23 AM

Chandra

Then try this way

EXEC updateProduct
@ProductID = CAST(ProductID AS INTEGER),
@ProductName = ProductName,
@LastUpdate = LastUpdate

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---------------------------------------

Show quote

"Patrick.O.Ige" wrote:

> Thx but if i do :-
> DECLARE @ProductID int,
> @ProductName nvarchar(40),
> @LastUpdate datetime
>
> EXEC updateProduct
> @ProductID = ProductID,
> @ProductName = ProductName,
> @LastUpdate = LastUpdate
>
> It still gives the error....
> I want results in the Query Analyzer!
>
> "Chandra" wrote:
>
> > Hi Patrick,
> > The order in which you pass the parameters looks wrong.
> > Just try it this way:
> > EXEC updateProduct
> > @ProductID = ProductID,
> > @ProductName = ProductName,
> > @LastUpdate = LastUpdate
> >
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://www.developersdex.com/gurus/default.asp?p=4223
> > ---------------------------------------
> >
> >
> >
> > "Patrick.O.Ige" wrote:
> >
> > > With the stored procedure below if i do
> > > DECLARE @ProductName nvarchar(40),@ProductID int
> > > EXEC updateProduct
> > > @ProductName = ProductName,
> > > @ProductID = ProductID
> > >
> > > why do i get error:- Error converting data type nvarchar to int.
> > >
> > >
> > > --------------------------------------
> > > ALTER   PROCEDURE updateProduct
> > > @ProductID int,
> > > @ProductName nvarchar(40),
> > > @LastUpdate datetime
> > > AS
> > > UPDATE
> > >   Products
> > > SET
> > >   ProductName = @ProductName
> > > WHERE
> > >   ProductID = @ProductID AND
> > >   LastUpdate = @LastUpdate
> > >
> > > IF @@ROWCOUNT > 0
> > >   -- This statement is used to update the DataSet if changes are done on the
> > > updated record (identities, timestamps or triggers )
> > >   SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
> > >   FROM Products
> > >   WHERE ProductID = @ProductID
> > > GO

13 May 2005 2:26 PM

Scott Morris

This is not only pointless, it also will not work. Both you and OP have
failed to declare the variables ProductID, ProductName, and LastUpdate; all
three of these names are not valid variable names. Assuming the variables
were declared and used correctly, both @ProductIDs (the variable and the
argument) are already defined as integer - the cast is pointless.

Lastly, the order of the arguments in the execute statement is **only**
important when the argument names are not used.

Show quote

"Chandra" <Chan***@discussions.microsoft.com> wrote in message
news:B7FD86EC-8A71-473A-A817-1CBB7B351EAA@microsoft.com...
> Then try this way
>
> EXEC updateProduct
> @ProductID = CAST(ProductID AS INTEGER),
> @ProductName = ProductName,
> @LastUpdate = LastUpdate
>
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.developersdex.com/gurus/default.asp?p=4223
> ---------------------------------------
>
>
>
> "Patrick.O.Ige" wrote:
>
> > Thx but if i do :-
> > DECLARE @ProductID int,
> > @ProductName nvarchar(40),
> > @LastUpdate datetime
> >
> > EXEC updateProduct
> > @ProductID = ProductID,
> > @ProductName = ProductName,
> > @LastUpdate = LastUpdate
> >
> > It still gives the error....
> > I want results in the Query Analyzer!
> >
> > "Chandra" wrote:
> >
> > > Hi Patrick,
> > > The order in which you pass the parameters looks wrong.
> > > Just try it this way:
> > > EXEC updateProduct
> > > @ProductID = ProductID,
> > > @ProductName = ProductName,
> > > @LastUpdate = LastUpdate
> > >
> > >
> > > --
> > > best Regards,
> > > Chandra
> > > http://chanduas.blogspot.com/
> > > http://www.developersdex.com/gurus/default.asp?p=4223
> > > ---------------------------------------
> > >
> > >
> > >
> > > "Patrick.O.Ige" wrote:
> > >
> > > > With the stored procedure below if i do
> > > > DECLARE @ProductName nvarchar(40),@ProductID int
> > > > EXEC updateProduct
> > > > @ProductName = ProductName,
> > > > @ProductID = ProductID
> > > >
> > > > why do i get error:- Error converting data type nvarchar to int.
> > > >
> > > >
> > > > --------------------------------------
> > > > ALTER   PROCEDURE updateProduct
> > > > @ProductID int,
> > > > @ProductName nvarchar(40),
> > > > @LastUpdate datetime
> > > > AS
> > > > UPDATE
> > > >   Products
> > > > SET
> > > >   ProductName = @ProductName
> > > > WHERE
> > > >   ProductID = @ProductID AND
> > > >   LastUpdate = @LastUpdate
> > > >
> > > > IF @@ROWCOUNT > 0
> > > >   -- This statement is used to update the DataSet if changes are

done on the
Show quote

> > > > updated record (identities, timestamps or triggers )
> > > >   SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
> > > >   FROM Products
> > > >   WHERE ProductID = @ProductID
> > > > GO

13 May 2005 2:19 PM

Scott Morris

There are a number of problems with your script.   The primary problem is
that you are confused about how to declare and use local variables as the
arguments for a stored procedure, as well as the correct syntax to use for
executing a stored procedure. The second
problem is that you must supply values for all stored procedure arguments
that do not have defaults. Try the script below.

set nocount on
go
use Northwind
go

create PROCEDURE mytest
@ProductID int,
@ProductName nvarchar(40),
@LastUpdate datetime
AS
if @ProductID is not null
   select * from Products where ProductID = @ProductID
else
   select * from Products where ProductName = @ProductName
go

DECLARE @ProductName nvarchar(40),@ProductID int

EXEC mytest
@ProductName = ProductName,
@ProductID = ProductID

EXEC mytest
@ProductName = @ProductName,
@ProductID = @ProductID

EXEC mytest
@ProductName = @ProductName,
@ProductID = @ProductID ,
@LastUpdate = null

set @ProductID = 2
EXEC mytest
@ProductName = @ProductName,
@ProductID = @ProductID ,
@LastUpdate = null
go

drop PROCEDURE mytest
go

13 May 2005 11:47 PM

Hugo Kornelis

On Thu, 12 May 2005 20:33:02 -0700, Patrick.O.Ige wrote:

>With the stored procedure below if i do
>DECLARE @ProductName nvarchar(40),@ProductID int
>EXEC updateProduct
>@ProductName = ProductName,
>@ProductID = ProductID
>
>why do i get error:- Error converting data type nvarchar to int.
(snip)

Hi Patrick,

In most cases, things like ProductID and ProdcutName (without preceding
@ and not enclosed in quotation marks) would refer to either table or
column names. But since you can't use either in an EXEC command, SQL
Server simply assumes that you omitted the quotation marks, but wanted
to write a string constant nonetheless. Run the following code to prove
this:

create proc test (@a varchar(20))
as
select @a
go
-- With quotes
exec test 'This is a test'
go
-- No quotes - but they are "assumed"
exec test SecondTest
go
-- It only works for single words
exec test Third try
go
drop proc test
go

So obviously, the value 'ProductID' is passed as value for the
@ProductID parameter to your proc - and since @ProductID is declared as
integer, SQL Server will attempt to conert it, and fail.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

11 Oct 2005 9:43 AM

naijacoder naijacoder

Thx guys for the replies

*** Sent via Developersdex http://www.developersdex.com ***