Problem when returning value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem when returning value

Author

Topic

mattyjim
Starting Member

5 Posts

Posted - 2006-10-22 : 07:28:36

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 varchar(255)
	declare @GOTVAL 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!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-22 : 08:21:12

return @RETVAL
the return value is an int only. Use an output parameter for this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-22 : 08:24:18

A stored procedure retrun value is always of type int, and should almost only be used as a status code NOT as a result, what you need is to define an OUTPUT parameter for your sp.

... and in just a few someone more skilled that me will tell you why not to use a cursor.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

mattyjim
Starting Member

5 Posts

Posted - 2006-10-22 : 08:28:46

Thanks for that, nr!

Had a suspicion it'd be something to do with my lack of SQL knowledge.

Well, another lesson learned

Thanks a lot for your help!

mattyjim
Starting Member

5 Posts

Posted - 2006-10-22 : 08:33:11

No cursors, PSamsig!

Oh, no! I'm even more ignorant than I thought!

Thanks for your help; I'll hang around here to learn something about cursors

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-22 : 08:47:27

If you explain excatly what you are trying to achive then there may be a more efficient set based methid avialable. From what you have posted so far it seems that you intend some processing after every fetch. But to give you an idea of the a set based approach then look at this code:

SELECT O.email, convert(varchar, CP.DEPT_ID) + ',' + convert(varchar, CP.HEAD_ID) + ',' + convert(varchar, CP.SU_ID) as [USER_VALUES]
FROM officers O
INNER JOIN CustPortal CP
ON CP.email = O.email

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

mattyjim
Starting Member

5 Posts

Posted - 2006-10-22 : 11:14:26

Ok, here's my complete code.

It's slightly different in that I changed my mind about the stored procedure, which now populates a temporary table rather than returning a value (which would originally have been used to populate a table in any case, so I figured that it would be more efficient to populate the table immediately).

Here's the stored procedure:

create procedure sp_CheckOfficerCodes (@OFFICER_IN varchar(255), @EMAIL_IN varchar(255), @CHECKCODE_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	
	
	
	if @RETVAL = @CHECKCODE_IN
		begin
			insert into dbo.#officers_temp (officer, email) values(@OFFICER_IN, @EMAIL_IN)
		end


GO

The following code will be embedded in vbscript and called from an .asp:

create table dbo.#officers_temp (officer varchar(255), email varchar(255))
	


	declare @EMAIL_PASS varchar(255)
	declare @OFFICER_PASS varchar(255)
	declare property_cursor cursor for
	select officer, email
	from officers

	open property_cursor
	
		fetch next from property_cursor
		into @OFFICER_PASS, @EMAIL_PASS

		while @@fetch_status=0
		begin
			exec sp_CheckOfficerCodes
			@EMAIL_IN = @EMAIL_PASS,
			@OFFICER_IN = @OFFICER_PASS,
			@CHECKCODE_IN = '2,1,1'

			fetch next from property_cursor into @OFFICER_PASS, @EMAIL_PASS
		end
	


	close property_cursor

	deallocate property_cursor

My objective:

I have a list of about 25 officers and their email addresses (LIST A)

Somewhere on the server where I work, there exists an enormous (a few hundred thousand rows) database containing, among other things, data relating to Officers, their email addresses and their Service Unit Codes.

The Officers' names are stored in varchar fields.
The Officers' email addresses are stored in varchar fields.

An Officer's Service Unit Code is a numeric tag consisting of three different integers, each of which is stored in a seperate int field.
This numeric tag is constantly being altered and updated.

A typical row of data would look something like this, if you were to view only the fields that we're interested in:

officer (varchar (255) = John Doe
email (varchar (255) = John Doe@workplace.com
DEPT_ID (int) = 1
HEAD_ID (int) = 7
SU_ID (int) = 2

When a user logs on to my .asp-based system, I scan their login cookie for their Service Unit Code and store it as a comma-delimited string (e.g. - "1,7,2").

(NOTE: this is hard-coded as '2,1,1' in the example above, but the .asp will pass the current user's code instead)

I then need to scan through my small list of officers (LIST A), and check their email addresses against the huge database in order to find their current Service Unit Code.

The names of the officers in LIST A that have the same Service Unit Code as the current user must then be retrieved and stored in such a way that the officers' names can be used to populate drop-down menus and tables within the .asp system (hence the temporary table).

It would be great if you could let me know of a more efficient method, as the code I originally had in place was ultra slow due to the number of records I was searching through!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 11:45:52

your cursor & SP can be simplified into


create table #officers_temp
(
    officer   varchar(255),
    email     varchar(255)
)

insert into dbo.#officers_temp (officer, email)
select o.officer, o.email
from   officers o inner join CustPortal c
       on o.email = c.email
where  CustPortal.email = @EMAIL_IN)
and    convert(varchar, DEPT_ID) + ',' + convert(varchar, HEAD_ID) + ',' + convert(varchar, SU_ID) = @CHECKCODE_IN

mattyjim
Starting Member

5 Posts

Posted - 2006-10-22 : 13:06:06

That's fantastic, khtan!

Had to change the "where CustPortal.email = @EMAIL_IN)" line to " where c.email = @EMAIL_IN", but it works just great.

Thanks a lot; you saved me a great deal of head scratching there. From now on, I'll try to use
this type of solution rather than those cumbersome cursor routines.

Thanks again!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources