SQL Server Data type problem. Syntax error...

[Guest_imported]

Here is my error copied out of the Event Viewer.

ErrDescription : [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the varchar value '<FACIL_PMT_TYPES><PMT_TYP_CD>' to a column of data type smallint. :

And my SP:

CREATE PROCEDURE sp_GetAvailablePaymentTypeInfoAsXML
@Payment_Types_XML varchar(1000) OUTPUT
AS
Declare @PaymentTypeCode smallint
Declare @PaymentTypeDescription varchar(50)
Declare PaymentTypes_Cursor CURSOR For

select PMT_TYP_CD, PMT_TYP_DESC from REPR_FACIL_PMT_TYP

Open PaymentTypes_Cursor
FETCH NEXT FROM PaymentTypes_Cursor Into @PaymentTypeCode, @PaymentTypeDescription

SET @Payment_Types_XML = &quot;<FACIL_PMT_TYPES>&quot;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Payment_Types_XML = @Payment_Types_XML + &quot;<PMT_TYP_CD>&quot; + @PaymentTypeCode + &quot;</PMT_TYP_CD>&quot; + &quot;<PMT_TYP_DESC>&quot; + @PaymentTypeDescription + &quot;</PMT_TYP_DESC>&quot;
FETCH NEXT FROM PaymentTypes_Cursor Into @PaymentTypeCode, @PaymentTypeDescription
END

SET @Payment_Types_XML = @Payment_Types_XML + &quot;</FACIL_PMT_TYPES>&quot;

Close PaymentTypes_Cursor
Deallocate PaymentTypes_Cursor

 

[grahamm]

Looks like @PaymentTypeCode is the problem.
Change:

SET @Payment_Types_XML = @Payment_Types_XML + &quot;<PMT_TYP_CD>&quot; + @PaymentTypeCode + &quot;</PMT_TYP_CD>&quot; + &quot;<PMT_TYP_DESC>&quot; + @PaymentTypeDescription + &quot;</PMT_TYP_DESC>&quot;

to:

SET @Payment_Types_XML = @Payment_Types_XML + &quot;<PMT_TYP_CD>&quot;
+ convert(varchar(12), @PaymentTypeCode)+ &quot;</PMT_TYP_CD>&quot; + &quot;<PMT_TYP_DESC>&quot;
+ @PaymentTypeDescription
+ &quot;</PMT_TYP_DESC>&quot;

 

[Guest_imported]

Can you not assign (concat) a small int or other data type to a string like that?

 

[grahamm]

In this situation implicit conversion is not performed. In other circumstances such as passing a smallint variable to a stored procedure integer parameter, implicit conversion is performed. Take a look at &quot;Data Type Conversion&quot; in the BOL.

Also you can use either CONVERT or CAST.