Stored procedure urgent - 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

Transact-SQL (2000)

Stored procedure urgent

Author

Topic

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2006-01-14 : 05:53:28

i have one stored procedure that return four columns.

how can i fire a query in stored procedure ??
means i want one one field from that Stored procedure.

Plz help me.
Its very urgent

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-14 : 06:50:32

Do you mean as a return value to the calling application ?

You can only return integer values from a SP. Else use a function

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-14 : 08:44:50

sanjay,

I guess u want to return a query results set (of 4 fields) by a stored procedure?

I don't know how far I'm correct (as afrika says cannot return anything other than int) but I did that and was successfull.

Let the stored procedure has a statement as

Select F1, F2, F3, F4 from MyTbl;

In the calling program, the data should be captured to a rec set as

rs.Open "MySP", con

Do While Not rs.EOF
Combo1.AddItem rs(0)
rs.MoveNext
Loop

Not in the standard way of return statement though.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-14 : 09:42:21

>>i want one one field from that Stored procedure.
If the question is, can you perform a query on a stored procedure? ie: "select Column2 from mySP"
no, you can't do that.

you can create a temp table with the same structure as the result set, then insert into the new table from the SP using "insert myTable exec mySP"
then select whatever you want from the table.

Or (what Srinika says) call the SP as is and only use the column you want.

Or you can create a new procedure with same logic but only return the column you need.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-14 : 09:46:16

>>You can only return integer values from a SP. Else use a function

The convention is to use RETURN value to indicate success/failure of an SP call. 0 is used to indicate success. If you want to return a single value (besides the result set) OUTPUT variables are ideally suited for this. No extra calls within the SP are needed and no additional round trips to the server are needed either.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 03:18:17

Also refer this (may or may not be useful in your case)
http://sqlteam.com/forums/topic.asp?TOPIC_ID=53384

Madhivanan

Failing to plan is Planning to fail

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2006-01-16 : 04:33:16

thx to all of u.
sorry for uncleared question.
TG u got my problem. I want one one field from that Stored procedure. I did.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources