Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Mar 2005
    Location
    greater manchester, uk
    Posts
    65
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question 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:


    Code:
    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!

  • #2
    New Coder
    Join Date
    Mar 2005
    Location
    greater manchester, uk
    Posts
    65
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Thumbs up Found the answer!

    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/colu...parameters.asp), and return the value with this.


    Hope this info will be useful for somebody else.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •