Execute a result in SQL Server using a stored procedure

Question

I need to execute result (already in form of SQL) itself of an SQL query to get the final result.

So, it should be like, in first query I execute

Select Top(1) RequiredQuery as ReqQry from EPMaster

I'll get another query in result in ReqQry that will then be executed in form another query to get the final result.

The second query will also require some parameters to be passed at where clause, like when I do:

Select Top(1) RequiredQuery as ReqQry 
from EPMaster 
--ReqQry after its execution returns:
Select Top(1) (FirstName + ' ' + LastName) as FullName 
from DPMaster 
where DmID = @DomainID and PnID = @PersonID

I'll be passing the Params @DomainID and @PersonID from my C# project's DAL layer.

So I guess it should be done with the help of a stored procedure.

-----------------More Explanation-------------

Here, one SQL statement is executed to get the next SQL statement which will be the resultant of the former statement. When you execute 1st query, you get 2nd query in result, which you execute again to get the final result

The second query needs 2 parameters to execute that are @DomainID and @PersonID which will be passed by me from my C# Project. So, if I make a stored procedure for handeling all this and pass the required parameters, along with the 1'st query from my project, it should first execute 1st query then execute 2nd query (with parameters PersonID and DomainID) that was received as result of 1st query, after which I get the final result.

I don't understand your needs properly. But may you would like to use dynamic SQL in your stored porcedure? — András Ottó
– András Ottó, Commented Sep 12, 2012 at 11:00
Here, 1 SQL statement is executed to get the next SQL statement which will be the resultant of the former statement. When you execute 1st query, you get 2nd query in result, which you execute again to get the final result. — Cyberpks
– Cyberpks, Commented Sep 12, 2012 at 11:07

Community · Accepted Answer · 2017-05-23 12:21:17Z

2

You should use Dynamic SQL, to get running the returned nvarchar(max) query string from the first procedure / query.

Edit:

DECLARE @ResultOfTheFirstQuery nvarchar(max)

SELECT @ResultOfTheFirstQuery = (Select Top(1)RequiredQuery 
                                 as ReqQry from EPMaster)

exec sp_executeSql @ResultOfTheFirstQuery

Or if you need a complex logic, you can write an other SP, which can heve a return value:

DECLARE @ResultOfTheFirstQuery nvarchar(max)

SELECT @ResultOfTheFirstQuery = FirstStoredprocedure @params

exec sp_executeSql @ResultOfTheFirstQuery

Here is an already well answered question how to get the paramater return. You can use RETURN or OUTPUT parameter.

Here is how to use the sp_executeSql

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Sep 12, 2012 at 11:13

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PhonicUK · Accepted Answer · 2012-09-12 13:10:48Z

0

Declare @SQL as nvarchar(MAX);
SET @SQL = (Select Top(1)RequiredQuery as ReqQry from EPMaster);
EXEC (@SQL);

edited Sep 12, 2012 at 13:10

answered Sep 12, 2012 at 11:14

PhonicUK

13.9k4 gold badges47 silver badges65 bronze badges

4 Comments

Cyberpks Over a year ago

Incorrect syntax near '@SQL' is what I am getting as result

PhonicUK Over a year ago

Try using EXECUTE sp_executesql @SQL instead

Gonzalo.- Over a year ago

the error is that you're missing "set" when you assign into @sql

PhonicUK Over a year ago

Indeed it is. I was doing it off the top of my head.

Collectives™ on Stack Overflow

Execute a result in SQL Server using a stored procedure

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related