SQL Server : Dynamic functions or reuse of stored procedures

Question

I want to use some dynamic SELECT statements. The name of the table or column could be different, that's why I use dynamic stored procedures like this:

CREATE PROCEDURE [dbo].[test_sp](
     @database nvarchar(70)= ''
    , @table_name nvarchar(70)= ''
    , @column nvarchar(70)= ''
) 
AS
    DECLARE @sql nvarchar(max)= 'SELECT ' + @column + 
                                'FROM [' + @database + 
                                '].[dbo].[' + @table_name + ']  ' 

    EXEC(@sql);
GO

This works very well. Now I got more and more store procedure with similar code snippets. Concerning the maintenance I want to reuse the snippets or use dynamic functions.

An example: I have another stored procedure which should "filter" the result set of this presented example (an additional requirement is not to add a parameter to filter this result set directly)

If this SQL would be static i would use a function. In my cases I need that dynamic. UDF doesn't allow that "EXEC"-stuff and the result set of stored procedures aren't reusable.

I would expect that many people got the problem like me. I googled a lot and tried several things, but nothing works...

I need a way to reuse stored procedure code snippets or reuse the result set of a store procedure or a function that is dynamic like a stored procedure.

So can anybody help me and give me another idea?

Thanks for your help or solution ideas :)

V

Edit/Solution:
First of all everybody should read the comment of "Joel Coehoorn" and " Panagiotis Kanavos". They are absolutely right and this code snippets aren't recommended in a web application or something like this. In my special case it's a local application and injections and other security aspects aren't relevant.

After that I have to thank you "Abdul Rehman Sayed". The following solution based on his idea.

To realize that requirement in SQL you have to create two stored procedures. The first on is create a result set and write it into a temporary table.

The second one execute the first stored procedure, filter the result and drop the temporary table.

First one:

ALTER PROCEDURE [dbo].[test_SP_FXN](
 @database nvarchar(70)= ''
) AS

DECLARE @create nvarchar(max)= '
    CREATE TABLE ['+@database+'].[dbo].[temp_result](
    [name] [nvarchar](150) NULL
  ,[id] [nvarchar](150) NULL
  ,[desc] [nvarchar](450) NULL )  ';

DECLARE @sql nvarchar(max)= '
INSERT INTO ['+@database+'].[dbo].[temp_result]
    SELECT TOP 1000 name, id, desc
    FROM ['+@database+'].[dbo].[important_table] ';

EXEC(@create);
EXEC(@sql);

The second one:

ALTER PROCEDURE [dbo].[test_SP_2](
 @database nvarchar(70)= ''
) AS
-- create the temp table with the result of the store procedure
EXEC ('['+@database+'].[dbo].[test_SP_FXN] '+@database )

-- Execute the real sql
DECLARE @sql nvarchar(max)= 'select * FROM ['+@database+'].[dbo].[temp_result] WHERE ID > 5' 
EXEC(@sql);

-- drop the temp table because there is no use any more
EXEC ('DROP TABLE ['+@database+'].[dbo].[temp_result] ')

This is only an example but i think the principle will be clear.

Thanks for your help and comments.

That's not how SQL (the language) works. You don't "reuse" the statements on multiple tables. A table is equivalent to a type, you can't have methods that work on arbitrary types. This stored procedure isn't "dynamic", it's just a string created using concatenation. In fact, a client-generated SQL statement (eg from an ORM) would perform better and be safer. As it is, it opens security holes (requirement to access different databases), opens the database to injection attacks, negates any benefit from execution plan caching, and can escalate to distributed transactions — Panagiotis Kanavos, Apr 28 '15 at 15:22
It's a local-application. That's why the security aspects aren't that important. But you're right in a normal web application it's terrible! @Abdul it could be possible that i use a temp table to get the result-set of the store procedure. I will try out a possible solution, thank's for that hint. — Der_V, Apr 28 '15 at 15:24

score 0 · Answer 1 · answered Apr 28 '15 at 16:51

In another way you can change your stored procedure to something like this:

CREATE PROCEDURE [dbo].[test_sp](
    , @Select nvarchar(70)= ''
    , @From nvarchar(70)= ''
    , @Where nvarchar(max) = ''
    , @GroupByColumn nvarchar(max) = ''
    , @Having nvarchar(max) = ''
    , @OrderBy nvarchar(max) = ''
) 
AS
    DECLARE @sql nvarchar(max)= 'SELECT ' + @Select + 
                                'FROM ' + @From + 
                                CASE WHEN @Where = '' THEN '' ELSE ' WHERE ' + @Where END +
                                CASE WHEN @GroupByColumn = '' THEN '' ELSE ' GROUP BY ' + @GroupByColumn END +
                                CASE WHEN @Having = '' THEN '' ELSE ' HAVING ' + @Having END +
                                CASE WHEN @OrderBy = '' THEN '' ELSE ' ORDER BY ' + @OrderBy END

    EXEC(@sql);
GO

Now you can test it like this:

EXEC test_sp @Select = 'Column1, Column2', @From = 'Table1'

EXEC test_sp @Select = 'DISTINCT Table1.Column1, Table2.Column1', @FROM = 'Table1 INNER JOIN TABLE2 ON Table1.PK = Table2.FK'

EXEC test_sp  @Select = 't1.[datetime]', @From = '[TestDB].[dbo].[Table1] t1', @Where = 't1.NullableColumn IS NOT NULL'

EXEC test_sp @Select = 'Column1, Column2', @From = 'Table1', @OrderBy = 'Column2'

And so on ...

score -1 · Answer 2 · edited May 23 '17 at 11:58

-1

I think you are searching for something like this:

Declare @table Table(/* list of columns */)

insert into @table
EXEC test_sp 'aDbName','aTable','somecolumns'

select *
from @table
where /* some filters */

Source

edited May 23 '17 at 11:58

Community

1
1

answered Apr 28 '15 at 15:13

shA.t

16,580
5
54
111

@Der_V you can use Table variable instead of creating a table in your first one, and you don't need to drop it ;). – shA.t Apr 28 '15 at 16:59

SQL Server : Dynamic functions or reuse of stored procedures

2 Answers2