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 2012 Forums
 Transact-SQL (2012)
 Stored Procedure

Author  Topic 

yolibeth
Starting Member

5 Posts

Posted - 2014-09-18 : 17:05:38
Hi, I'm Yolibeth and i'm working on SQL Server and i'm new at this. I make a stored procedure to delete rows from a table that is running from XML, but I get the following error:

SERVER: Error converting data type NVARCHAR to INT

This´s my code:

ALTER PROCEDURE [dbo].[fn_cn_sp_forXML_DEL_Banks]

@pvbank_desc VARCHAR(30),
@piprovedor_id NVARCHAR (5),
@picompany_id NVARCHAR(5)

AS

BEGIN
IF (SELECT COUNT(provedor_id) FROM cn_cat_bank WHERE provedor_id = @piprovedor_id) = 1 AND
(SELECT COUNT(company_id) FROM cn_cat_bank WHERE company_id = @picompany_id) = 1
BEGIN
DELETE FROM cn_cat_bank
WHERE bank_desc = @pvbank_desc
END
ELSE
RAISERROR('No puedes borrar este registro, ya que esta información es importante para otras bases de datos', 16, 1)
END


try to correct it by changing the int data to nvarchar, on XML code company_id and provedor_id are integer

Please, I need help!

Note: Excuse me my anglish.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-18 : 17:27:50
Is the column company_id or provedor_id defined as type int? Then put in an explicit cast


IF (SELECT COUNT(provedor_id) FROM cn_cat_bank WHERE provedor_id = cast(@piprovedor_id as int)) = 1 AND
(SELECT COUNT(company_id) FROM cn_cat_bank WHERE company_id = cast(@picompany_id as int)) = 1
Go to Top of Page

yolibeth
Starting Member

5 Posts

Posted - 2014-09-18 : 18:43:31
thanks for your answer!

Both are type int. i made the changes whit CAST but yet i have the error. In the XML code exists the param type integer, in the SQL code exists the int params

The error is the following

Server: Conversion failed when converting the nvarchar value '_param_1_' to data type int.
EXEC fn_cn_sp_forXML_DEL_Banks _param_1_
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-18 : 20:07:45
So the data you're comparing to is not an integer. How do you expect them to compare?
Go to Top of Page

yolibeth
Starting Member

5 Posts

Posted - 2014-09-18 : 20:35:20
Realy I don´t know, that is the problem. I work with sql/xml for create a view this may be shown in a web site.


ALTER PROCEDURE [dbo].[fn_cn_sp_forXML_DEL_Banks]

@pibank_id NVARCHAR(30)

AS

BEGIN

DECLARE @li_bank_id INT
SET @li_bank_id = CAST(@pibank_id AS INT)
SELECT @li_bank_id

DELETE FROM cn_cat_bank
WHERE bank_id = @li_bank_id


END

This is the code, the message shows the error in the query (SQL) but the params are geting for XML code
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-19 : 08:07:31
I'm confused. I don't see XML anywhere in your code or in the data.

Please post some sample data from cn_cat_bank and some sample calls to your proc
Go to Top of Page

yolibeth
Starting Member

5 Posts

Posted - 2014-09-19 : 17:21:48
Sorry!

This is the SQL/XML code where I take the data:

.
.
.
UNION
SELECT

'<delete>
<delete_def>
<![CDATA[
EXEC fn_cn_sp_forXML_DEL_Banks _param_1_
]]>
</delete_def>

<params>

<param id = "1">field.bank_id</param>

</params>

</delete>', 'A006'
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-19 : 17:31:28
OK so the XML contains the call to the process but uses an invalid parameter. That's causing the error
Go to Top of Page

yolibeth
Starting Member

5 Posts

Posted - 2014-09-21 : 10:52:58
That´s true! And thank you for you help.

The problem was the params; in the XML code had a VARCHAR param but in the Stored Procedure an INT param.

OMG
Go to Top of Page
   

- Advertisement -