Article: Stored Procedures: Returning Data

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

Site Related Forums

Article Discussion

Article: Stored Procedures: Returning Data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-02-14 : 12:24:25

This article discusses three common ways to return data from stored procedures: OUTPUTing variables, temp tables and the RETURN statement.

Article Link.

leonardox
Starting Member

2 Posts

Posted - 2008-06-23 : 12:44:43

Hi Bill, I've tryed the example for capture the result set using T-SQL in SQL Server 2000 but return then error
"Can't use EXECUTE instruction as source for insert in a TABLE variable" (spanish translated to english, sorry)

DECLARE @People TABLE (
ContactID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
)

INSERT @People (ContactID, FirstName, LastName)
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

SELECT COUNT(*) FROM @People
GO

mybe is this sintax for SQL Server 2005?

I was hardly looking for in the net some ways to return system stored procedures data (like sp_lock) in a CURSOR or TABLE variable to continue managing the result like a normal SELECT from table in T-SQL. Do you know how can I do it please?

Thank you

Best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 13:00:30

quote:
Originally posted by leonardox

Hi Bill, I've tryed the example for capture the result set using T-SQL in SQL Server 2000 but return then error
"Can't use EXECUTE instruction as source for insert in a TABLE variable" (spanish translated to english, sorry)

DECLARE @People TABLE (
ContactID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
)

INSERT @People (ContactID, FirstName, LastName)
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

SELECT COUNT(*) FROM @People
GO

mybe is this sintax for SQL Server 2005?

I was hardly looking for in the net some ways to return system stored procedures data (like sp_lock) in a CURSOR or TABLE variable to continue managing the result like a normal SELECT from table in T-SQL. Do you know how can I do it please?

Thank you

Best regards

You cant use table variables with EXEC statement
cahnge @table to #table in above and it will work
use CREATE TABLE #People ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 13:15:00

If your goal is finding the number of rows returned, use

EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'
SELECT @@ROWCOUNT as rows

Madhivanan

Failing to plan is Planning to fail

leonardox
Starting Member

2 Posts

Posted - 2008-06-25 : 06:54:24

Ok, It works great!, Thank you all

But now, have other question

How can I do the same with
DBCC SQLPERF (LOGSPACE)

I tryed the same, but doesn't works

Regards

CREATE TABLE #Tabla_tmp (
c1 varchar,
c2 varchar,
c3 varchar,
c4 varchar
)

INSERT #Tabla_tmp (c1, c2, c3, c4)
exec DBCC SQLPERF (LOGSPACE)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 10:04:07

quote:
Originally posted by leonardox

Ok, It works great!, Thank you all

But now, have other question

How can I do the same with
DBCC SQLPERF (LOGSPACE)

I tryed the same, but doesn't works

Regards

CREATE TABLE #Tabla_tmp (
c1 varchar,
c2 varchar,
c3 varchar,
c4 varchar
)

INSERT #Tabla_tmp (c1, c2, c3, c4)
exec DBCC SQLPERF (LOGSPACE)

Post your question as a new topic so that you would get better answers

Madhivanan

Failing to plan is Planning to fail

sujeetji
Starting Member

1 Post

Posted - 2008-08-19 : 03:48:28

Hi
Thanks for a gr8! job.
But I have one question, U have mention three different way to get result from SP, I just want to know which one is best as per preformance & security.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:44:34

quote:
Originally posted by visakh16

You cant use table variables with EXEC statement
cahnge @table to #table in above and it will work
use CREATE TABLE #People ...

Yes you can in SQL Server 2005.

DECLARE	@Sample TABLE
	(
		spid INT,
		ecid INT,
		status VARCHAR(200),
		login VARCHAR(200),
		hostname VARCHAR(200),
		blk INT,
		dbname VARCHAR(200),
		cmd VARCHAR(200),
		request_id INT
	)

INSERT	@Sample
EXEC	sp_who

SELECT	*
FROM	@Sample
WHERE	spid >= 50

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-26 : 11:34:44

quote:
Originally posted by leonardox

Ok, It works great!, Thank you all

But now, have other question

How can I do the same with
DBCC SQLPERF (LOGSPACE)

I tryed the same, but doesn't works

Regards

CREATE TABLE #Tabla_tmp (
c1 varchar,
c2 varchar,
c3 varchar,
c4 varchar
)

INSERT #Tabla_tmp (c1, c2, c3, c4)
exec DBCC SQLPERF (LOGSPACE)

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/26/outputting-dbcc-results.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 13:01:48

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

You cant use table variables with EXEC statement
cahnge @table to #table in above and it will work
use CREATE TABLE #People ...

Yes you can in SQL Server 2005.

DECLARE @Sample TABLE
(
spid INT,
ecid INT,
status VARCHAR(200),
login VARCHAR(200),
hostname VARCHAR(200),
blk INT,
dbname VARCHAR(200),
cmd VARCHAR(200),
request_id INT
)

INSERT @Sample
EXEC sp_who

SELECT *
FROM @Sample
WHERE spid >= 50

E 12°55'05.63"
N 56°04'39.26"

I know that. but OP clearly suggested he was using it in sql 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources