View Full Version : SQL Server (problem with returned value)

Oct 22nd, 2006, 02:24 AM
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)


officer (varchar 255)
email (varchar 255)

My sql code is as follows:

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

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


return @RETVAL


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

while @@fetch_status=0
exec @GOTVAL = sp_CheckOfficerCodes

fetch next from property_cursor into @EMAIL_PASS
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! :)

Oct 22nd, 2006, 02:39 PM
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. :)