Error converting data type nvarchar to int.

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
GOHi 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.examnotes.net/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 t
he
> updated record (identities, timestamps or triggers )
> SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice
> FROM Products
> WHERE ProductID = @.ProductID
> GO|||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.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Patrick.O.Ige" wrote:
>|||Then try this way
EXEC updateProduct
@.ProductID = CAST(ProductID AS INTEGER),
@.ProductName = ProductName,
@.LastUpdate = LastUpdate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/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:
>|||There are a number of problems with your script. The primary problem is
that you are 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|||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.
"Chandra" <Chandra@.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.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Patrick.O.Ige" wrote:
>
done on the|||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)|||Thx guys for the replies
*** Sent via Developersdex http://www.examnotes.net ***