Menu
Is it possible to execute store procedure like a "table" for SELECT operator (MS SQL SERVER)?
Something like
SELECT TotalSum FROM exec MyStoreProcedure '2011/11/01', '2011/11/01' I mean somehow integrate it into the SELECT operator?
Thank you!
Thanks guys!
The solution what I did is based on your answers:
declare @result table (f1 varchar(20),f2 varchar(20), CodProducto int, NomProducto varchar(1000), Costo decimal, Cantidat int, Total decimal) INSERT INTO @result exec MyStoreProcedure '20111201', '20111201' select * from @result 
912
We can not directly use stored procedures in a SELECT statement.
How to create a SELECT stored procedure? Click on your Database and expand “Programmability” item and right click on “Stored Procedures” or press CTRL + N to get new query window. In the query area between BEGIN and END, type your SELECT statement to select records from the table.
You should look at functions, you cannot call a stored procedure from within a select query.
You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; MySQL creates new columns for all elements in the SELECT .
I supposed your proc returns several columns and you just want one, right?
small workaround is to add the result of the proc to a table variable and then select from it
create proc proc1 as select 1 as one, 2 as two declare @result table (one int, two int) insert into @result exec proc1 select one from @result This would be better as a function rather than a stored procedure.
create function dbo.TestTable (@var1 bit) returns table AS RETURN ( select * from INFORMATION_SCHEMA.TABLES where @var1 = 1 ); select * from dbo.TestTable(1) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
