how to run stored procedure inside select statement

Question

how to run stored procedure inside select statement

mizan700

SSC Enthusiast

Points: 193
More actions
August 3, 2016 at 2:47 am

#320332

Hi Experts,
How can i run my stored procedure inside select statement?
example:
Select Username, exec @myproc from userinfo
Regards,
Mizan

Viewing 4 posts - 1 through 3 (of 3 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can't do it like that, for two reasons. First, you can't use a variable to represent the name of the stored procedure. If you don't know at design time which stored procedure you'll be running then you'll need to use dynamic SQL. Suppose you do know which stored procedure you need, though - you now need to insert the result set of the stored procedure into a table (or table variable) and then join to that in your query. Have a go at that, and post back if there's anything you don't understand. Stored procedure and table definitions and sample data (in the form of INSERT statements) will help if you require more detailed assistance.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

mizan700 (8/3/2016)
Hi Experts,
How can i run my stored procedure inside select statement?
example:
Select Username, exec @myproc from userinfo
Regards,
Mizan

Can't you add UserName to the output of the stored procedure?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

durga.palepu Hall of Fame Points: 3530 More actions · Answer 3

strictly this usage is not possible in SQL Server as the other expert suggested.

For better help, you need to post the associated DDL and DML statements with sample data.

Thanks,

Durga Prasad