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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedures: Returning Data

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2009-07-07 : 12:37:47

I read article with same name @ [url]http://www.sqlteam.com/article/stored-procedures-returning-data[/url]

I am want to run a select query against a stored procedure or at least mimic that process. The above article explained how this might be done by creating a table which I did as suggested but get error

quote:
Msg 197, Level 15, State 1, Line 21
EXECUTE cannot be used as a source when inserting into a table variable.


DECLARE  @certaccts TABLE (
    lrsn INT,
	parcel_id varchar(30),
	map_lot varchar(16),
	area varchar(2),
	pclas varchar(3),
	last_update datetime,
	appraiser varchar(20),
	seq int,
	reason varchar(2),
	description varchar(60),
	land decimal(10,2),
	improved deciamal(10,2),
	tot_val decimal(10,2)
)

INSERT @certaccts ( lrsn, parcel_id, map_lot, area, pclas, last_update, appraiser, seq, reason, description, land, improved, tot_val )
EXEC ManatronWheelerCustom..drc_sp_certified_acct_list 20090101

SELECT distinct lrsn FROM @certaccts
GO

As far as I can see this is identical to what the article suggests. But doesn't work.

Ideas?

TIA

Education is what you have after you've forgotten everything you learned in school

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-07 : 18:02:21

Don't use a table variable and try.


Create table #certaccts (
    lrsn INT,
	parcel_id varchar(30),
	map_lot varchar(16),
	area varchar(2),
	pclas varchar(3),
	last_update datetime,
	appraiser varchar(20),
	seq int,
	reason varchar(2),
	description varchar(60),
	land decimal(10,2),
	improved deciamal(10,2),
	tot_val decimal(10,2)
)

INSERT #certaccts ( lrsn, parcel_id, map_lot, area, pclas, last_update, appraiser, seq, reason, description, land, improved, tot_val )
EXEC ManatronWheelerCustom..drc_sp_certified_acct_list 20090101

SELECT distinct lrsn FROM #certaccts

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

doco
Yak Posting Veteran

77 Posts

Posted - 2009-07-07 : 18:36:50

Delightful!

Thank you...

Education is what you have after you've forgotten everything you learned in school

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources