Menu
I have a stored procedure with many parameters.
I want to insert (or maybe select) like this:
INSERT INTO @TEMP_TABLE
EXECUTE STORED_PROCEDURE
without defining the schema of @TEMP_TABLE.
810
When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.
You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success. If no return is explicitly set, then the stored procedure returns zero. You should use the return value for status codes only.
You can't technically return "a table", but you can return a result set and using INSERT INTO .. EXEC ... syntax, you can clearly call a PROC and store the results into a table type.
You cannot do this with a @tablevariable.
The work around in the link posted by Joe uses SELECT ... INTO.
This is not currently supported by table variables (and won't ever be from the response to this connect item) as the schema for table variables needs to be known at compile time.
137
for example:
declare @temptable2 as table
(
DatabaseName nvarchar(128),
dbsize nvarchar(128),
owner varchar(128),
dbid nvarchar(128),
created nvarchar(128),
status nvarchar(128),
compatibility_level nvarchar(128)
)
INSERT INTO @temptable2
EXEC ('sp_helpdb')
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
