entering store procedure result to table

Hello there
is there a way to enter store procedure result to table?insert into tblname
exec stored_proc1
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Roy Goldhammer" wrote:

> Hello there
> is there a way to enter store procedure result to table?
>
>|||Thankes A lot
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:DB2F9A89-FFC1-4B17-ABBD-3A45C8B24867@.microsoft.com...
> insert into tblname
> exec stored_proc1
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Roy Goldhammer" wrote:
>|||Whell Omnibuzz.
Now i need to enter it to function in order to use it as generic function
My function looks like this:
create function fnDir(@.Path as varchar(8000))
returns @.ret table(FileName varchar(1000))
as
begin
DECLARE @.dir varchar(1000)
set @.dir = 'dir ' + @.Path + '/b'
insert @.ret
exec master..xp_cmdshell @.DIR
end
and it gives me error:
EXECUTE cannot be used as a source when inserting into a table variable.
how can i solve it?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:DB2F9A89-FFC1-4B17-ABBD-3A45C8B24867@.microsoft.com...
> insert into tblname
> exec stored_proc1
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Roy Goldhammer" wrote:
>|||Roy,shalom
The error message is pretty clear. You cannot use EXEC command within a UDF.
Instead , create a stored procedure
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ehGpAc5kGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Whell Omnibuzz.
> Now i need to enter it to function in order to use it as generic function
> My function looks like this:
> create function fnDir(@.Path as varchar(8000))
> returns @.ret table(FileName varchar(1000))
> as
> begin
> DECLARE @.dir varchar(1000)
> set @.dir = 'dir ' + @.Path + '/b'
> insert @.ret
> exec master..xp_cmdshell @.DIR
> end
> and it gives me error:
> EXECUTE cannot be used as a source when inserting into a table variable.
> how can i solve it?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:DB2F9A89-FFC1-4B17-ABBD-3A45C8B24867@.microsoft.com...
>|||Whell Uri.
I need it because other wants to use it as select *
from [process]
the only way i know to use it is by that
do you have any idea?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ui9Sbh5kGHA.896@.TK2MSFTNGP04.phx.gbl...
> Roy,shalom
> The error message is pretty clear. You cannot use EXEC command within a
> UDF. Instead , create a stored procedure
>
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:ehGpAc5kGHA.3816@.TK2MSFTNGP02.phx.gbl...
>|||> Whell Uri.
> I need it because other wants to use it as select *
> from [process]
> the only way i know to use it is by that
> do you have any idea?
Yes, teach them to use EXEC [process];|||Roy
I'm a little bit . What is prevent you from using this code inside
the SP, ahhh I see because someone wants to run SELECT *, so
you can run EXEC sp and that will returt the same data , does it matter to
him?
And as you probably know that using SELECT * in the production is considered
a bad practice
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23yHLBo5kGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Whell Uri.
> I need it because other wants to use it as select *
> from [process]
> the only way i know to use it is by that
> do you have any idea?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ui9Sbh5kGHA.896@.TK2MSFTNGP04.phx.gbl...
>|||In SQL 2005 you could use a CLR function in SQL 2000, however, you could use
sp_OA* system procedures to instantiate a file system object
("Scripting.FileSystemObject") then traverse the selected folder to get a
list of file names to return as the result of the function.
What's your version? Anyway, there are many good examples in Books Online
(and elsewhere on MSDN).
ML
http://milambda.blogspot.com/|||Whell Aaron
they need it for anoter sql in order to add it to join.
how can i do it with store procedue?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2354Ptv5kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>
> Yes, teach them to use EXEC [process];
>
>