Calling Stored Procedure in Select Statement

Question

Calling Stored Procedure in Select Statement

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
January 4, 2012 at 12:29 am

#254280

Dear All;
I am doubt for calling the SP with in the select Query
This is my sample procedure
EXEC [NmStudent] '07UF','10','100','0707'
Here will return the list of result List Like as
Rno Rname
01128001 John
01128002 Smith
01128003 Steve
This is my select Stmt
SELECT * FROM TBL_NAME WHERE RNO NOT IN (
EXEC [NmStudent] '07UF','10','100','0707' )
Here the Query not working kindly replay How can i run the sp with in select command.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Although a procedure can return result set(s), you cannot use them directly that way. It is possible to use INSERT...EXECUTE to store the results of a procedure in a (usually temporary) table, but that's not generally a good design. More usually, the logic in the procedure would be written as a function (preferably the in-line version) or as a view instead. Which is best for your circumstances rather depends on what the procedure does. As an example:

CREATE TABLE dbo.Students

(

student_id INTEGER PRIMARY KEY,

given_name NVARCHAR(30) NOT NULL,

some_value INTEGER NOT NULL

)

GO

INSERT dbo.Students

(student_id, given_name, some_value)

VALUES

(01128001, N'John', 70),

(01128002, N'Smith', 75),

(01128003, N'Steve', 70)

GO

CREATE FUNCTION dbo.GetStudentsByValue (@some_value INTEGER)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

SELECT

s.student_id,

s.given_name

FROM dbo.Students AS s

WHERE

s.some_value = @some_value

GO

SELECT

*

FROM dbo.GetStudentsByValue(70) AS gsbv

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 2

Hey rookie

may i know what kind of result do your sp retuns. Is it one column result of multicolumn result because you using select not in which means it need to have one column result is it so...

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 3

Rookie

This is one of the way you can try for your desire result but for this you need to know what your sp is returning it.

-- create temp table to store your sp output this table should be equall number of colum that sp result

CREATE TABLE #TEMP1

(

ID INT

)

-- this statment put your sp result into your temp

INSERT INTO #TEMP1table

EXEC TEST

--This will test your sp data with you define query

SELECT * FROM CHART WHERE CHARTID NOT IN (SELECT * FROM #TEMP1)

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

faijurrahuman17 (1/4/2012)
Dear All;
I am doubt for calling the SP with in the select Query
This is my sample procedure
EXEC [NmStudent] '07UF','10','100','0707'
Here will return the list of result List Like as
Rno Rname
01128001 John
01128002 Smith
01128003 Steve
This is my select Stmt
SELECT * FROM TBL_NAME WHERE RNO NOT IN (
EXEC [NmStudent] '07UF','10','100','0707' )
Here the Query not working kindly replay How can i run the sp with in select command.

It could be done using OPENROWSET and Dynamic SQL. Seems like a lot of extra work, though. The creation of a Temp Table and the use of INSERT/EXEC would seem the best way to go... unless you have the time to change the stored procedure to a multi-line table valued function (mTVF) or, if you pull it off in a single query, and inline table valued function (iTVF).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

You could use the linked server trick I used here: http://spaghettidba.com/2011/11/16/discovering-the-output-of-dbcc-commands/

I don't recommend it, though.

-- Gianluca Sartori

sqlfriends@sql.com SSCommitted Points: 1504 More actions · Answer 6

Dear All;

Thanks for all your valuable information ...

I am using Temp Table to sole my problems.

Thanks

Faijurrahuman.A