How to Parse and Append text to a stored procedure in SQL Server 2005 via a parameter

Question

Does anyone know of a way to append text to a stored procedure from within another stored procedure? I would like to do something like the following in SQL Server 2005:

    Declare str as Nvarchar(Max) = '' 
    set @spStr = dbo.spTest + 'Where testCol1 = ''Test'''
    exec(@spStr)

I understand this may open some discussion about SQL injection attacks. I'm simply looking to see if syntax exsists to extend a stored procedure by passing it a where clause dynamically in the above manner.

Answer 1

There are some options.

You can alter the actual SP using the metadata in INFORMATION_SCHEMA.ROUTINES (not really what I think you are wanting to be doing)

You can parameterize the SP - this should not be vulnerable to injection if the SP uses the variable directly and not to dynamically make SQL.

You might consider using a view or an inline or multi-step table-valued function instead, which can be used like a parameterized view (inline being more efficient) - SELECT * FROM udf_Test WHERE TestCol1 = 'Test'.

You can take the results of the SP and put them in a temporary table or table variable and query against that.

Answer 2

There is no syntax like this available in Sql Server any version. You've got a couple of options:

1. You could obviously modify the procedure to include a parameter that the procedure code itself would handle as a filter in the final statement(s) that returned the result set from the procedure call. Though I'd advise against it, you could certainly have a parameter that was just a varchar/nvarchar data type which included the actual 'where' clause you want to add and have the procedure code append it to these final select statement(s) as well

2. Use the insert/exec syntax to populate a temp table with the results of the stored procedure execution and then simply run a filtered select against that temp table.