T-SQL Trying to build a dynamic Query

[2xhelix]

I am trying to build a dynamic report.
I have several Stored procedure, that currently return
"ItemNumber","Information"

Every user has a different configuration for thier report. So maybe User1, uses SP1,SP4,SP5 .. and User2 uses Sp2,Sp4,Sp6

How would i join together the Stored Procedures dynamically to create the final table? I want to do this in SQL, because i already have all the user information inside SQL, it's a matter of writing up the syntax for it.

My first thought was to build a string, where i could simply put EXEC (@MyQuery)

but the issue is, i can't do that with a stored procedure.

Does anyone have any clues on how to acomplish this?

 

[ESquared]

Why can't you do that with a stored procedure?

create proc tester
as
print 'hello'
go
declare @d varchar(30)
set @d = 'tester'
exec ('exec ' + @d)
drop proc tester

-------------------------------------
A sacrifice is harder when no one knows you've made it.