incorrect syntax : including results of stored procedure in select statement

Question

I have a stored procedure that does this...

CREATE PROCEDURE spGetRegion @postcode nvarchar(10)
AS 
SELECT capt FROM REGION_DATA
where POSTCODE = @postcode
go

This means I can run

exec spGetCounty @postcode = 'rh12'

and it returns an area, such as "south east'

Is it possible to include the results of a stored procedure like this in a select statement. for example

SELECT 
  FIRST_NAME
, LAST_NAME
, POSTCODE
, << NEED TO INCLUDE RESULTS OF STORED PROCEDURE HERE PASSING IN POSTCODE
FROM PEOPLE

So something like

SELECT 
  FIRST_NAME
, LAST_NAME
, POSTCODE
, exec spGetCounty @postcode = 'rh12' << THIS BIT DOESN'T WORK !!
FROM PEOPLE

any ideas?

Check this http://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query — Flying_Machine, Oct 29 '14 at 10:30

Gordon Linoff · Accepted Answer · 2014-10-29T10:50:54.103

2

Instead of a stored procedure, consider using a scalar function:

CREATE FUNCTION spGetRegion (
    @postcode nvarchar(10)
)
RETURNS varchar(255)
BEGIN
    DECLARE @capt varchar(255);

    SELECT @capt = capt
    FROM REGION_DATA
    where POSTCODE = @postcode;

    RETURN(@capt);
END;

Then you can call it as:

SELECT FIRST_NAME, LAST_NAME, POSTCODE,
       dbo.spGetRegion('rh12');
FROM PEOPLE;

edited Oct 29 '14 at 10:50

answered Oct 29 '14 at 10:29

Gordon Linoff

1,242,037
58
646
786

you have mentioned `returns INT` and you have returned a varchar – Hitesh Oct 29 '14 at 10:33

score 0 · Answer 2 · answered Oct 29 '14 at 10:27

You need to create a function

CREATE function spGetRegion 
(
    @postcode nvarchar(10)
)
RETURNS  varchar(100)
as
begin

    SELECT capt FROM REGION_DATA
    where POSTCODE = @postcode

end

and use it in your query like

SELECT 
FIRST_NAME
, LAST_NAME
, POSTCODE
, dbo.spGetCounty('rh12')
FROM PEOPLE

score 0 · Answer 3 · answered Oct 29 '14 at 10:28

Nope, you can't use stored procedure in select statement.

You have to use user defined function - scaler function.

CREATE FUNCTION [dbo].[ufnGetCounty] 
(
     @postcode nvarchar(10)
)
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @capt VARCHAR(200)

    SELECT @capt = capt FROM REGION_DATA
    where POSTCODE = @postcode

    RETURN @capt
END

and then you can use it in select statement like this -

SELECT 
  FIRST_NAME
, LAST_NAME
, POSTCODE
, dbo.ufnGetCounty(postcode)
FROM PEOPLE

score 0 · Answer 4 · answered Oct 29 '14 at 10:30

Use view or user defined function by referring below links

using view, http://msdn.microsoft.com/en-us/library/aa258253%28SQL.80%29.aspx

using user defined function http://msdn.microsoft.com/en-us/library/aa175085%28SQL.80%29.aspx

A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.

or alternatively, try something like below

    SELECT  *  FROM    
    OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
    WHERE somefield = anyvalue

incorrect syntax : including results of stored procedure in select statement

4 Answers4