Stored Procedure - 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 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

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_

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?

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

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

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'

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources