How to display? - 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

General SQL Server Forums

New to SQL Server Programming

How to display?

Author

Topic

sudip_dg77
Starting Member

2 Posts

Posted - 2007-09-18 : 16:14:47

Hi,

I have the following stored procedure

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

as
select @number = * 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

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 16:25:18

Again with the coffee spew on th monitor

What do you think

select @number = * from fix_lvts..fix_exec_reports

is doing?

How long have you been doing sql?

And if you very closely, they are not the same

A variable assignment is really meant (even though we have all of the undocumeted "features") to assign one column of one row of data.

You've got all columns for all rows in a table based on a predicate...which happens to be over 99k rows

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-09-22 : 09:07:24

If your intention is just to alias the column , or do you want to return all the records or do you just want the top 1?

Jack Vamvas
--------------------
Need an IT job ? http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources