i have a spx which is returning me int values as itis declated as; declare @error int

its ok to be as int, but in a case where i have to return multiple string values, i am trying to cast it to varchar so i can get the error

set @error = 'my invalid error message'

so i am trying like

set cast(@error as varchar(100)) = 'my invalid error message'

but i am getting a syntax error on the cast, am i doing anything wrong

I don't think you can cast a variable type on the fly like that... you can cast them to read to them, but you can't cast them to write to them. If you think of a variable as a table column, and you cast a column to a varchar, it still wouldn't allow you to store a string in if it's declared as an int on the table definition. I think you need to change the declaration in the procedure to make @error varchar(100). And then SQL should auto-cast all the ints you're currently setting it to to be varchar, so it won't break anything.

i did as you said, i did the declare @error varchar(100), but i am still getting it as int error, cannot convert value to int

DECLARE @error VARCHAR(100);
SET @error = 'my invalid error message';

Should work just fine, so there must be a different place that's the problem. What are you doing with @error later? Are you trying to insert it into an int column or something?

here is what i am doing

When I run that (adjusting to a table I have), it works fine. no error at all. Which also matches with what I expected.

`its strange, because the empid is an int column in my table

I tried it with empid as both an int column and a varchar column just in case it made a difference, even though it shouldn't.