Returning a string from a stored procedure

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 2005 Forums

Transact-SQL (2005)

Returning a string from a stored procedure

Author

Topic

andbra
Starting Member

4 Posts

Posted - 2009-07-30 : 08:43:06

Hello!

This is my first post here, so go easy on me. :)

I have serched the forum a bit for this problem, and have found some relevant posts, but none of them seem to solve my issue.

I need to return a string from one field in my table to another program executing my DB stored procedure.

I am using Microsoft SQL Server Management Studio for SQL Server 2005.

I have created the table, and it is populated, but I can't get my SP to work.

It has an Unique identifier field(ID), and some other fields which I need for this table. I pass 4 arguments into the SP, and I want to get a string value returned.

The SP looks like this:

ALTER PROCEDURE [dbo].[find_sisteagent] 
	-- Add the parameters for the stored procedure here
	@Date varchar(20) = 0,
	@Type nchar(10) = 0,
	@Value varchar(20) = 0,
	@Group nchar(10) = 0
	
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

    -- Insert statements for procedure here

	
	SELECT TOP 1 Agent FROM SisteAgent
	where Group = @Group and Date = @Date and Type = @Type and Value = @Value
	ORDER by ID DESC




END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-30 : 08:46:34

[code]
ALTER PROCEDURE [dbo].[find_sisteagent]
-- ADD the parameters FOR the stored PROCEDURE here
@Date varchar(20) = NULL,
@Type NCHAR(10) = NULL,
@Value varchar(20) = NULL,
@Group NCHAR(10) = NULL,
@Agent varchar(10) OUTPUT

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets FROM
-- interfering WITH SELECT statements.
--SET NOCOUNT ON;

-- INSERT statements FOR PROCEDURE here

SELECT TOP 1 @Agent = Agent FROM SisteAgent
WHERE Group = @Group AND Date = @Date AND Type = @Type AND Value = @Value
ORDER BY ID DESC
END

/* Example EXEC

DECLARE @Agent varchar(10)

EXEC find_sisteagent @Date = '2009-07-20', @Type = 'xxx', @Value = 'xxx', @Group = 'xxxx', @Agent = @Agent OUTPUT

SELECT @Agent

*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

andbra
Starting Member

4 Posts

Posted - 2009-07-30 : 09:15:22

Hi khtan, and thank you for your quick answer.

Unfortunately, I have no way of performing the EXEC manually, because the application that I (have to) use, only take variables as arguments once it is called, and I am not able to EXEC anything after the initial calling of the SP.

Here is what my "DBServer" log looks like. The DBServer is the Server performing the DB queries etc.:

14:10:30.493 Dbg 10739 ODBC: id='3.1' req='428' execute sp: find_sisteagent @Group='ABC',@Type='ABnr',@Dato='07/30/2009',@Value='12345',@Agent=''
 +0000  SP Fetch record ok
 +0000  SP added column for retrieve
 +0000  MSG_RETRIEVED status='DBM_SUCCESS'
 +0000  SP fetch record failure (no data)
 +0000  MSG_PROCCOMPLETED status='DBM_SUCCESS' msg='The procedure completed ok.'

Are there any ways of returning the string directly from the SP?

Anders

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-30 : 09:29:06

You can't even pass in a OUTPUT parameter ?

KH
[spoiler]Time is always against us[/spoiler]

andbra
Starting Member

4 Posts

Posted - 2009-07-31 : 03:38:35

As a matter of fact I do! :) Found out how to do it just now. So that is great.

Now I get output, but it looks like this: x7

When it should say: AB40384

When I execute the SP in Enterprise Manager the @Agent contains the correct value.

I can't see why this should happen?

Thank you very much for your help this far. :)

Anders

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-31 : 03:52:41

maybe wrong data type ?

KH
[spoiler]Time is always against us[/spoiler]

andbra
Starting Member

4 Posts

Posted - 2009-07-31 : 04:18:00

I thought so as well, but I have tried both nchar and varchar, but none of them helped, returned the same.

I then tried to change it so I selected the ID, and used @ReturnID int OUTPUT

ALtered the SP so it said SELECT @ReturnID = ID ...

But it returned the same stupid string: x7 using that as well..

When testing in Enterprise Manager both the @Agent and @REturnID contains the correct value regarding to the query.

I really have no idea what is causing this, but I suspect that this is not SQL trouble, but probably in the application calling the SP...

Anders

Edit: syntax

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources