SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Stored Procedures: Returning Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/14/2001 :  12:24:25  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 06/23/2008 :  12:44:43  Show Profile  Reply with Quote
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


Edited by - leonardox on 06/23/2008 12:48:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/23/2008 :  13:00:30  Show Profile  Reply with Quote
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 ...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 06/23/2008 :  13:15:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

leonardox
Starting Member

2 Posts

Posted - 06/25/2008 :  06:54:24  Show Profile  Reply with Quote

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)



Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 06/25/2008 :  10:04:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sujeetji
Starting Member

1 Posts

Posted - 08/19/2008 :  03:48:28  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/26/2008 :  10:44:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 09/26/2008 :  11:34:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/26/2008 :  13:01:48  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000