Call a stored procedure in the 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

General SQL Server Forums

New to SQL Server Programming

Call a stored procedure in the select statement

Author

Topic

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 02:18:06

Is it possible to call a stored procedure in a select statement?

For example:
SELECT Buyer, Country, (exec the sp_test*) as ItemList.....

*sp_test is a stored procedure with buyer and country as the input parameters and output a concatenated item numbers (example: F12345,A1023,C40165).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-21 : 02:21:08

That isn't possible, however you can put the results of the stored procedure into a temp table and then use that temp table in your select statement:

CREATE TABLE #Temp (...)

INSERT INTO #Temp (...)
EXEC usp_test...

SELECT ..., (SELECT * FROM #Temp)
FROM ...

Or

SELECT ...
FROM ...
INNER JOIN #Temp
ON ...

Tara Kizer

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 02:21:23

No, but you can store the results from an SProc into a table (or #TempTable) and then include that in your SELECT statement - if that helps.

Alternatively, and probably more appropriate, would be to use a User Defined Function which returns the concatenated item numbers

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 02:23:30

Or you can rewrite the logic in to stored procedure as an user defined function, it all it does is a select.

Peter Larsson
Helsingborg, Sweden

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 02:33:01

Thanks guys for all your tips!

Let me try and see if an user defined function works for my case

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 03:20:32

I tried to create a UDF, but got the following error:
Error 170: Line 1: Incorrect syntax near 'FUNCTION'.
A RETURN statement with a return value cannot be used in this context.

I check on the syntax using the simplest statement as below but still failed. Can anybody please help

CREATE FUNCTION uf_GenItmList (@buyer  varchar(30),
                               @ctycod varchar(3))  
RETURNS varchar AS  
BEGIN 
  DECLARE @myItem varchar(500)
  SET @myItem = 'A123,B223'
  RETURN @myItem
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 03:23:52

your function looks fine to me except you should define the size of the varchar that returns.


CREATE FUNCTION uf_GenItmList (@buyer  varchar(30),
                               @ctycod varchar(3))  
RETURNS varchar(500) AS  
BEGIN 
  DECLARE @myItem varchar(500)
  SET @myItem = 'A123,B223'
  RETURN @myItem
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-21 : 03:24:44

quote:
Originally posted by nicole

I tried to create a UDF, but got the following error:
Error 170: Line 1: Incorrect syntax near 'FUNCTION'.
A RETURN statement with a return value cannot be used in this context.

I check on the syntax using the simplest statement as below but still failed. Can anybody please help
CREATE FUNCTION uf_GenItmList (@buyer varchar(30),
@ctycod varchar(3))
RETURNS varchar(500) AS
BEGIN
DECLARE @myItem varchar(500)
SET @myItem = 'A123,B223'
RETURN @myItem
END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 03:35:06

oops! Same error after adding the size for the return variable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 03:38:09

quote:
Originally posted by nicole

oops! Same error after adding the size for the return variable

how do you create the function ? Run the CREATE FUNCTION .. in Query Analyzer ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 03:45:33

You also need dbo as owner in front of the function.
This is a "feature" of sql server. When returning scalar values you need dbo, when returning tables you don't.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 03:53:39

But omitting the dbo when referencing the function will not give the error message as posted.

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 04:08:11

KH: I create the function in Enterprise Manager.

Peso: Still got the error after putting the "dbo" in front of the function name....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 04:11:31

try doing it in Query Analyser. I tried to create the function via EM and does not encounter the error you describe

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 04:21:14

KH: I copied the scripts and run it in Analyser but got the same error

What else I can take out from the function to test the syntax?

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 178, Level 15, State 1, Line 8
A RETURN statement with a return value cannot be used in this context.

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 04:23:29

Even I copied the sample from another web site to try, same error.....
I connect as "sa", it should be no problem to create a function. am I correct?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 04:25:14

Activate the correct database and copy and paste this code to Query Analyzer.

CREATE FUNCTION dbo.uf_GenItmList
(
	@buyer varchar(30),
	@ctycod varchar(3)
)
RETURNS varchar(500)
AS  
BEGIN 
	DECLARE @myItem varchar(500)

	SET	@myItem = 'A123,B223'

	RETURN	@myItem
END

Peter Larsson
Helsingborg, Sweden

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 04:29:26

KH: you tried to create the function as of the scripts I posted and got no error?

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 04:36:44

Peso, I do exactly what you told me but got same error. Why "A RETURN statement with a return value cannot be used" for a function??

nicole
Yak Posting Veteran

97 Posts

Posted - 2007-02-21 : 04:41:54

I tried to use PRINT @myItem instead of RETURN @myItem, the error of return value gone (of course) but still have "Incorrect syntax near 'FUNCTION'"....

I really don't know what else I can do.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 04:42:28

Are you really doing a COPY & PASTE operation from this web page?
I have copied and pasted the code to both SQL Server 2005 SSMS and SQL Server 2000 Query Analyzer and run them both with no errors.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources