output stored procedure to a table

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)

output stored procedure to a table

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-11-04 : 07:18:39

Dear Team

is possible to passing row from the output stored procedure
to a table and how?

thanks

oh

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-04 : 07:32:36

[code]create proc usp_InsertIntoTable
as

insert into table.....
[/code]

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-11-04 : 07:56:52

i have try your query and have problem, can you show me in detail(example ?)

oh

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-04 : 08:22:51

quote:
Originally posted by ehorn

create proc usp_InsertIntoTable
as

insert into table.....

This is not a query. It is demonstrating that you can run SQL statements inside of a stored procedure, including insert statements.

Here is a sample:


create table #q (n int identity(0,1),somedata char(4))
go

create proc usp_InsertIntoTable 
	@somevalue char(4)
as
	set nocount on
	insert into #q select @somevalue
GO

exec usp_InsertIntoTable 'TEST'

select * from #q

go
drop table #q
drop proc usp_InsertIntoTable

Also, if anyone would like to use this very valuable sp please feel free... But you may want to put some indexes on the table

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-04 : 14:48:42

I think they're looking for something like:


USE Northwind
GO
CREATE TABLE myTable99 (Col1 varchar(1000), Col2 varchar(1000))
GO
INSERT INTO myTable99 EXEC sp_depends Orders
GO
SELECT * FROM myTable99
GO
DROP TABLE myTable99
GO

Brett

8-)

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-11-04 : 21:08:25

i try query like:
SELECT * FROM OPENQUERY(LINKEDSERVER, 'EXEC sp_name') WHERE field1 = 'tesss' and the query is work
Regarding my question before, is possible to execute query like that in localserver ?

Thanks

oh

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-04 : 21:16:06

You can set up the local server as a linked server.
OR
Follow Brett's advice above. This is the option I would suggest.

Damian

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-11-04 : 22:23:17

it's imposible to create local server as as linked server, i have been tried this and error

oh

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-04 : 22:29:59

It's not impossible

Look up "linked servers, loopback linked servers" in Books Online

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-04 : 22:32:05

By the way, I STILL don't know why you won't insert into a table like Brett's example. Especially seeing as that is what you asked for.

Damian

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-11-04 : 23:26:27

thanks for information about loopback linked server before.
i wont inset into myTable like brett's script because in brett's query example like :
'INSERT INTO myTable99 EXEC sp_depends Orders'
The object "Orders" is a table but in my case the object is a stored procedure and the result will error if we change object from table to stored procedure

oh

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-05 : 00:32:48

oh if you look carefully, Brett is calling a stored procedure, and he is passing "Orders" as a parameter to the sp. You should be able to do this with any SP

INSERT INTO TableName EXEC spName

Just make sure that the structure of your table matches the output of the procedure.

Owais

Where there's a will, I want to be in it.

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2003-11-05 : 02:39:51

thanks Owais that is work properly

oh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources