Stored Procedure output problem. - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » Stored Procedure output problem.

Stored Procedure output problem.

Options

Previous Topic · Next Topic

sudip_dg77

Posted: Wednesday, September 19, 2007 5:27:10 AM

Rank: Newbie

Joined: 9/18/2007
Posts: 1
Points: 3
Location: Norway

Hi,

I have the following stored procedure

CREATE PROCEDURE dbo.test(
@number varchar(128) output
)

as
select @number = gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
return 0

Then I go in and execute it like this:

declare @number varchar(128)
exec dbo.test @number output
select @number

I get the following result:

T_PORTWARE_GBL

(Only one row returns).

However when I execute the sql statement below:

select gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'

which is the same one as in the stored procedure except the variable declaration it reurns me the following:

gw_instance
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL

(99028 rows of data)

Why is this hapenning?

Can anyone help me?

Thanks and Regards,
Sudip

Scott Whigham

Posted: Thursday, September 20, 2007 10:11:18 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Variables are scalar meaning that they can only store one value. If you need to return more than one value, don't use an OUTPUT param; use a SELECT statement in your stored proc instead.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.