SQL Server (problem with returned value)

Hello there!

I think this will probably be an easy one for somebody with a bit more SQL experience than myself, so here goes:


I am using two datatables ('CustPortal' and 'Officers', both of which are located in the same database).


They are structured as follows:

CustPortal: -

DEPT_ID (int)
HEAD_ID (int)
SU_ID (int)
email (varchar 255)


Officers:

officer (varchar 255)
email (varchar 255)




My sql code is as follows:


create procedure sp_CheckOfficerCodes (@EMAIL_IN varchar(255))
as
declare @RETVAL varchar(255)

begin
select @RETVAL = (select convert(varchar, DEPT_ID) + ',' + convert(varchar, HEAD_ID) + ',' + convert(varchar, SU_ID)
as [USER_VALUES]
from CustPortal
where CustPortal.email = @EMAIL_IN)


end


return @RETVAL


GO



declare @EMAIL_PASS as varchar(255)
declare @GOTVAL as varchar(255)
declare property_cursor cursor for
select email
from officers

open property_cursor

fetch next from property_cursor
into @EMAIL_PASS

while @@fetch_status=0
begin
exec @GOTVAL = sp_CheckOfficerCodes
@EMAIL_IN = @EMAIL_PASS

fetch next from property_cursor into @EMAIL_PASS
end
close property_cursor

deallocate property_cursor



Here is a breakdown of what the code is supposed to do:


1. create the 'sp_CheckOfficerCodes' procedure

2. read through the 'officers' table line by line, passing data from the 'amail' field into 'sp_CheckOfficerCodes'

3. 'sp_CheckOfficerCodes' then compares the data passed in from officers.email with the data present in custportal.email, and returns a string built from the corresponding custportal.DEPT_ID, custportal.HEAD_ID and custportal.SU_ID fields.

The string should look something like: "1,1,1" (three comma-delimited integers)



4. The data finally ends up in the @GOTVAL variable (i'll eventually extend the program in order to use this data)




Although the stored procedure is created without any problem , every time I try to run the rest of the code in 'Query Analyser', I get the following error:

Server: Msg 245, Level 16, State 1, Procedure sp_CheckOfficerCodes, Line 15
Syntax error converting the varchar value '1,1,1' to a column of data type int.



Does anybody know what the problem might be here?

I can't see why Query Analyser even thinks I'm trying to perform a conversion!


It's quite important that I get this sorted, and so any help would be greatly appreciated.


Thanks! :)

Well, I found the answer from a couple of people over on SQLTeam forum, just in case anyone's interested.


Apparently, a return value is always of type int, and shouldn't ever be used to retrurn anything else.

The best method is to set an output parameter (http://www.sqlservercentral.com/columnists/rmarda/outputparameters.asp), and return the value with this.


Hope this info will be useful for somebody else. :)