Prev: Loop through records to Update data?
Next: Resetting Identity within a temp table
From: dbuchanan on 25 Jun 2008 14:08
Wintin a new stored procedure I would like to work with data returned from
an existing stored procedrue which returns a table, but T-SQL retuns an
error message:
"EXECUTE cannot be used as a source when inserting into a table variable."

Short of using the code contained within the original procedure How can I
return one of the columns from another stored procedure that returns a
table.

And then...

How do I user tha within a query or strored procedure?

Example of what I am facing:
========================
usp_getValues takes and integer argument and returns a table with these
columns: EntID, TypeID, Nm, ud1, ud2, parent

I want to use EntID in a where clause within a second stored procedrue like
this:

Where EntID in (EXEC usp_getValues 23984)

of course that is not allowed. How do I get aroound this?
========================

Here is an additional consideration. This is in the context of Dynamic SQL,
because this is how I chose to pass multi-value integer parameters. See a
model of my stored procedrue

simplified procedure
========================
ALTER Proc [dbo].[usp_mySP]
(@EntID nvarchar(10)
,@MultiInt1 nvarchar(1000)
,@Date smalldatetime
)

declare
@SQLQuery nvarchar(4000)
,@ParamDef nvarchar(200)

set @ParamDef = N'@Date smalldatetime'

set @SQLQuery =
N' select column1, column2, column3, column4
Where EntID in (EXEC usp_getValues 23984) << Here is where I need a
solution.
and Crs in (' + @MultiInt1 + N')
and MyDate = @Date'

EXECUTE sp_executesql
@SQLQuery,
@ParamDef,
@Date
========================

How do I get these values into the query?

Thank you,
Doug


From: Aaron Bertrand [SQL Server MVP] on 25 Jun 2008 14:25
> Wintin a new stored procedure I would like to work with data returned from
> an existing stored procedrue which returns a table, but T-SQL retuns an
> error message:
> "EXECUTE cannot be used as a source when inserting into a table variable."

Why do you need a table variable? You can probably use a temporary table
instead in this case.

Anyway you should have a read of this article:
http://www.sommarskog.se/share_data.html

From: TheSQLGuru on 25 Jun 2008 14:26
You cannot insert into a table variable using exec... Try a temporary table
instead.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"dbuchanan" <dbuchanan(a)newsgroup.nospam> wrote in message
news:eSsky3u1IHA.4772(a)TK2MSFTNGP03.phx.gbl...
> Wintin a new stored procedure I would like to work with data returned from
> an existing stored procedrue which returns a table, but T-SQL retuns an
> error message:
> "EXECUTE cannot be used as a source when inserting into a table variable."
>
> Short of using the code contained within the original procedure How can I
> return one of the columns from another stored procedure that returns a
> table.
>
> And then...
>
> How do I user tha within a query or strored procedure?
>
> Example of what I am facing:
> ========================
> usp_getValues takes and integer argument and returns a table with these
> columns: EntID, TypeID, Nm, ud1, ud2, parent
>
> I want to use EntID in a where clause within a second stored procedrue
> like this:
>
> Where EntID in (EXEC usp_getValues 23984)
>
> of course that is not allowed. How do I get aroound this?
> ========================
>
> Here is an additional consideration. This is in the context of Dynamic
> SQL, because this is how I chose to pass multi-value integer parameters.
> See a model of my stored procedrue
>
> simplified procedure
> ========================
> ALTER Proc [dbo].[usp_mySP]
> (@EntID nvarchar(10)
> ,@MultiInt1 nvarchar(1000)
> ,@Date smalldatetime
> )
>
> declare
> @SQLQuery nvarchar(4000)
> ,@ParamDef nvarchar(200)
>
> set @ParamDef = N'@Date smalldatetime'
>
> set @SQLQuery =
> N' select column1, column2, column3, column4
> Where EntID in (EXEC usp_getValues 23984) << Here is where I need a
> solution.
> and Crs in (' + @MultiInt1 + N')
> and MyDate = @Date'
>
> EXECUTE sp_executesql
> @SQLQuery,
> @ParamDef,
> @Date
> ========================
>
> How do I get these values into the query?
>
> Thank you,
> Doug
>


From: Bob on 25 Jun 2008 17:38
You could do it as a join, even in dynamic SQL.

DEMO ...
USE tempdb

CREATE TABLE your_table ( id INT PRIMARY KEY )
GO
CREATE PROC usp_get_values

AS

SELECT 1 x
UNION ALL
SELECT 2 x
GO

INSERT INTO your_table VALUES ( 1 )
GO

-- Your proc
DECLARE @sql VARCHAR(MAX)

SET @sql ='DECLARE @result TABLE ( id INT PRIMARY KEY )

INSERT INTO @result
EXEC usp_get_values

SELECT *
FROM your_table t
INNER JOIN @result r ON t.id = r.id'

EXEC (@sql)

HTH
wBob

"dbuchanan" wrote:

> Wintin a new stored procedure I would like to work with data returned from
> an existing stored procedrue which returns a table, but T-SQL retuns an
> error message:
> "EXECUTE cannot be used as a source when inserting into a table variable."
>
> Short of using the code contained within the original procedure How can I
> return one of the columns from another stored procedure that returns a
> table.
>
> And then...
>
> How do I user tha within a query or strored procedure?
>
> Example of what I am facing:
> ========================
> usp_getValues takes and integer argument and returns a table with these
> columns: EntID, TypeID, Nm, ud1, ud2, parent
>
> I want to use EntID in a where clause within a second stored procedrue like
> this:
>
> Where EntID in (EXEC usp_getValues 23984)
>
> of course that is not allowed. How do I get aroound this?
> ========================
>
> Here is an additional consideration. This is in the context of Dynamic SQL,
> because this is how I chose to pass multi-value integer parameters. See a
> model of my stored procedrue
>
> simplified procedure
> ========================
> ALTER Proc [dbo].[usp_mySP]
> (@EntID nvarchar(10)
> ,@MultiInt1 nvarchar(1000)
> ,@Date smalldatetime
> )
>
> declare
> @SQLQuery nvarchar(4000)
> ,@ParamDef nvarchar(200)
>
> set @ParamDef = N'@Date smalldatetime'
>
> set @SQLQuery =
> N' select column1, column2, column3, column4
> Where EntID in (EXEC usp_getValues 23984) << Here is where I need a
> solution.
> and Crs in (' + @MultiInt1 + N')
> and MyDate = @Date'
>
> EXECUTE sp_executesql
> @SQLQuery,
> @ParamDef,
> @Date
> ========================
>
> How do I get these values into the query?
>
> Thank you,
> Doug
>
>
>
From: dbuchanan on 25 Jun 2008 17:53
Aaron,

How would I fill this temporary table from a strored procedure that returns
a table?

Thanks,
Doug


 |  Next  |  Last
Pages: 1 2
Prev: Loop through records to Update data?
Next: Resetting Identity within a temp table