CODEWITHSUNDEEP
sql-serverstored-procedures
Frison Alexander
Frison Alexander

Reputation: 3256

Pass result of a query into stored procedure

I have a stored procedure that accepts three parameters.

Is it possible to call this stored procedure with a query as input?

As an example, I have tableA. 

SELECT * FROM TABLEA 

| A | B | C |
|---+---+---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |

Now, is there a way that I can call 

EXEC sp_name (SELECT * FROM TABLEA)

so that the stored procedure will execute for each row?

My reasoning behind this is I have a stored procedure that needs to be called for multiple rows. I can write a script to do this, but want to know if its possible to do it with TSQL.

Upvotes: 3

Views: 10758

Answers (3)

Rama
Rama

Reputation: 11

This solution is assuming that the T-SQL is running on SQL SERVER 2008 and above

  1. Create a Table Type variable , This needs to be done only once USE YOURDBNAME; Go Create TYPE TableAType as Table (A int null,B int null, C int null); This creates a table type variable in user-defined table types in the DB that you are currently using.

  2. In your Stored proc, Declare a variable of this table type. and Populate it with the contents from table A. Example: 

    Create Procedure MyStoredProcedure1 AS Declare @TableB as TableAType; 
Insert into @TableB(A,B,C) Select A,B,C from TableA;

  3. Create your Main Stored Proc 

    Create Procedure MyStoredProcedure2
@TVP TableAType READONLY
as 
EXEC sp_Name @TVP;

This should do the work for you, For further reference , please see the link below# http://msdn.microsoft.com/en-us/library/bb510489%28v=sql.105%29.aspx

Upvotes: 1

d89761
d89761

Reputation: 1434

The best way to accomplish this is to create your 2nd stored procedure as a user-defined function instead. Then you can call it in this way:

SELECT * 
FROM TableA
CROSS APPLY dbo.yourUserFunction(column1, column2, etc)

Or, there may be a way to combine both stored procedures into one set, but I'd have to see what was in the 2nd one you are calling.

Upvotes: 1

NicVerAZ
NicVerAZ

Reputation: 407

Technically, yes, but your call to the sproc is badly formatted.

Exec sp_name 'SELECT * FROM TABLEA'

or

Exec sp_name @sql1 = 'SELECT * FROM TABLEA'

That said, what you are trying to do may not be a good idea.

Upvotes: 0

Related Questions