Call stored procedure within SELECT statement

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

Old Forums

CLOSED - General SQL Server

Call stored procedure within SELECT statement

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-04-17 : 16:31:06

I posted this on dbForums.com as well :
http://www.dbforums.com/t994360.html
Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.

code:--------------------------------------------------------------------------------
USE NORTHWIND
GO

CREATE TABLE tbA (
Item int NOT NULL,
Value int NOT NULL
) ON [PRIMARY]

GO

INSERT INTO tbA (Item, Value)
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2
GO

CREATE PROCEDURE usp_SquareIt

@iItem int

AS

declare @iValue int
SELECT @iValue = Value FROM tbA
SELECT @iValue * @iValue AS Result

GO

SELECT Item,
EXECUTE usp_SquareIt Item AS Squared ---- can this be done
FROM tbA
GO

DROP TABLE tbA
GO

DROP PROCEDURE usp_SquareIt
GO
--------------------------------------------------------------------------------

Any thoughts?

Mike B

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-17 : 16:33:00

An alternative might be to create a user-defined function which can be called inline.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-17 : 21:24:08

I looked up in SQL Server BOOKs and you might be able to use a table variable.

Here's a section of the page...

"Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined."

Just look up table variable in SQL Server BOOKS to find the page.

Dustin Michaels

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-17 : 21:38:21

[code]create function dbo.udf_SquareIt (@input int)
returns float
as
begin
return SQUARE(@input)
end
go

select
Item,
Value,
dbo.udf_SquareIt(Value) AS Value_Squared
from
tbA
go[/code]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-18 : 05:47:28

You can't insert the execution of a stored procedure into a table variable. You can insert it into a temp table though. The solution ehorn suggested will work.

The most efficient solution would be to have a more complicated FROM clause (possibly using derived tables) that lets you get everything the stored procedure in the SELECT statement would provide you.

If you can give us ddl and some sample data, along with an example, we could provide a lot more help.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources