Error: Conversion failed when converting the varchar value

[Richard Andrew x64]

Error: 245 "Conversion failed when converting the varchar value 'AS' to data type int." The problem is that I have no field or variable named "AS". So I am really stumped. The complete code of the stored procedure is below: (I have marked the location of the error with a comment.) Do you have any idea what it's talking about? Thank you very much for any pointers in the right direction!

ALTER PROCEDURE [dbo].[spVendorUpdateVendor]
-- Add the parameters for the stored procedure here
@VENDORID INT,
@ACTIVE BIT,
@COUNTRYID INT,
@VENDORCODE NVARCHAR(10),
@NAME NVARCHAR(50),
@WEBSITE1 NVARCHAR(50),
@WEBSITE2 NVARCHAR(50),
@VIACODE NVARCHAR(10),
@TERMS SMALLINT,
@USECOMPCODE BIT,
@DISCOUNTPERCENT SMALLINT,
@DELETEDBYID INT, -- Pass 0 if not deleting the record
@DELETEDON DATETIME,
@NONUS BIT,
@NOTES NVARCHAR(MAX)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
BEGIN TRY

    	IF EXISTS(SELECT ID FROM VENDOR\_MASTER WHERE ID = @VENDORID)
            	BEGIN                                                  --<< SQL SERVER SAYS THE ERROR IS ON THIS LINE

		        IF (@DELETEDBYID > 0) AND (NOT EXISTS(SELECT USERID FROM USERS WHERE USERID = @DELETEDBYID))
                            	BEGIN
                                    	RETURN 2; -- DeletedById does not exist in USERS table
                                    END

                            IF (NOT EXISTS(SELECT \* FROM COUNTRY\_CODES CC WHERE CC.COUNTRYCODE = @COUNTRYID))
                            	BEGIN
                                    	RETURN 3; -- Country Code does not exist in COUNTRY\_CODES table
                                    END

                            IF (NOT EXISTS(SELECT SHIPVIACODE FROM

[Jorgen Andersson]

Well, I have a guess. First, the question of the value 'AS'. You're having a status return parameter in several places. What's the name of it? Also, in the catch block you make a

SELECT ERROR_NUMBER() [ErrorNumber],
ERROR_PROCEDURE() [ErrorProcedure],
ERROR_LINE() [ErrorLine],
ERROR_MESSAGE() [ErrorMessage]

before RETURN ERROR_NUMBER(); Is that select returned? And what type would that select be?

Wrong is evil and must be defeated. - Jeff Ello

[Richard Andrew x64]

Thanks for helping. Yes, the SELECT dataset is returned containing the error information. Not sure what you mean by what type is the select. I don't know if selects have types the way scalar values do. In several places I return a status value, but I'm not using a named value, I'm using a numeric constant. Is that what you meant? EDIT: OK I think I might know what you mean. The variable that returns the return value is called "@ReturnValue".

The difficult we do right away... ...the impossible takes slightly longer.

[Mycroft Holmes]

Maybe a silly point but are there any triggers spit associated with the tables?

Never underestimate the power of human stupidity RAH

[Richard Andrew x64]

No. There are no triggers. Could it be a bug in SQL Server? How likely is that? Do you think maybe if I rearranged the code the error might go away?

The difficult we do right away... ...the impossible takes slightly longer.

[Mycroft Holmes]

Quote:

IF (NOT EXISTS(SELECT * FROM COUNTRY_CODES CC WHERE CC.COUNTRYCODE = @COUNTRYID))

Check the data type for CountryCode!

Richard Andrew x64 wrote:

Could it be a bug in SQL Server? How likely is that?

Nah something in your code is smelly, just can't spot it!

Never underestimate the power of human stupidity RAH

[Lost User]

I am not an expert in SQL, but isn't that message complaining about a value, not a variable name? That is to say, some variable contains the string "AS" and you are trying to convert it to an integer.

[Richard Andrew x64]

DING DING! I think we have a winner! Thank you so much for spotting that. I had been looking at it for so long that I couldn't see it. :doh: Thanks. :)

The difficult we do right away... ...the impossible takes slightly longer.

[Richard Andrew x64]

Yes you are absolutely correct. I'm not an expert either, so that's why I couldn't spot that. Thanks for your useful input! :)

The difficult we do right away... ...the impossible takes slightly longer.

[Mycroft Holmes]

Our naming conventions are similar, code and id are very different types ;P

Never underestimate the power of human stupidity RAH