| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
leonardox
Starting Member
2 Posts |
 Posted - 06/23/2008 : 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
|
Edited by - leonardox on 06/23/2008 12:48:36 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/23/2008 : 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
India
22460 Posts |
Posted - 06/23/2008 : 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 - 06/25/2008 : 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
India
22460 Posts |
Posted - 06/25/2008 : 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 Posts |
Posted - 08/19/2008 : 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
Sweden
29138 Posts |
Posted - 09/26/2008 : 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
India
22460 Posts |
Posted - 09/26/2008 : 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
India
47069 Posts |
Posted - 09/26/2008 : 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  |
|
|
| |
Topic |
|
|
|
Article: Stored Procedures: Returning Data
