WITH statement in dynamic sql

Question

I need to store a query as a stored procedure in SQL Server.

I need also to pass parameters which define tablenames and column names.

This is the query I owuld like to have, I tried to store it in a string and then EXECUTE it but without success, ho can I solve this?

CREATE PROCEDURE sp_selectAllParents @id int, @tableid varchar(30), @tablename varchar(30)
AS BEGIN
 SET NOCOUNT ON;
 WITH ct AS (
  SELECT * FROM @tablename t WHERE @tableid = @id
  UNION ALL
  SELECT t.* FROM @tablename t JOIN ct ON t.parentId = ct.@tableid 
 )
 SELECT * FROM @tablename t WHERE @tableid NOT IN (SELECT @tableid FROM ct)
END

EDIT: my attempt was:

DECLARE @sql varchar(255)
SET @sql = 'WITH ct AS (SELECT * FROM @tablename t WHERE @tableid = @id UNION ALL SELECT t.* FROM @tablename t JOIN ct ON t.parentId = ct.@tableid) SELECT * FROM @tablename t WHERE @tableid NOT IN (SELECT @tableid FROM ct)'
EXEC(@sql)

Do you really have so many different tables with a hierarchical structure that a generic procedure for this is necessary? This is fraught with issues, and I would have thought just writing a procedure for each table would safer and simpler, and less error prone (invalid table or column names supplied). On an unrelated note you should [avoid the prefex `sp_` for your own procedures](http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix). — GarethD, Jan 19 '16 at 17:18
It is usually a bad idea to construct stored procedures that take a table name and/or column name as parameters to get results from it. You should rethink your solution. — TT., Jan 19 '16 at 17:29
I know that this is not a good practice but I'm fixing an old project and I can only act like this without modifying existing schema — frx08, Jan 19 '16 at 20:35

TT. · Answer 1 · 2016-01-19T17:53:56.803

3

As I already in the comment section, it is a bad idea to do this. You should really rethink your solution.

The Stored Procedure would have to look like this:

CREATE PROCEDURE selectAllParents @id int, @tableid sysname, @tablename sysname
AS 
BEGIN
 SET NOCOUNT ON;

 -- Guards against SQL Injection attacks (replace ' with '')
 SET @tableid=REPLACE(@tableid,'''','''''');
 SET @tablename=REPLACE(@tablename,'''','''''');

 DECLARE @stmt NVARCHAR(4000);
 SET @stmt=
   ';WITH ct AS ('+
     'SELECT * FROM ' + QUOTENAME(@tablename) + ' t WHERE ' + QUOTENAME(@tableid) + '= @id ' +
     'UNION ALL ' +
     'SELECT t.* FROM ' + QUOTENAME(@tablename) + ' t JOIN ct ON t.parentId = ct.' + QUOTENAME(@tableid) +
   ')'+
   'SELECT * FROM ' + QUOTENAME(@tablename) +' t WHERE ' + QUOTENAME(@tableid) + ' NOT IN (SELECT ' + QUOTENAME(@tableid) +' FROM ct);';

 EXEC sp_executesql
    @stmt,
    N'@id int',
    @id;
END
GO

edited Jan 19 '16 at 17:53

answered Jan 19 '16 at 17:34

TT.

15,774
6
47
88

3

I would definitely use `QUOTENAME` around the object names here, and I would probably use `SYSNAME` as the datatype for the object name parameters. Probably worth adding validation too and checking the column name and table name are valid. I know a lot of this is beyond the scope of the question, however, in providing a solution that you have already advised against, I think there comes a certain duty to at least mitigate the damage that could be caused by using it. – GarethD Jan 19 '16 at 17:42
@GarethD Guards, quotenames & sysnames added. – TT. Jan 19 '16 at 17:55
2

The replace bit of the guards isn't necessary since quotename escapes all these, and could actually make a name invalid (but only if someone has created a table like `[table'1]` and they probably deserve an error if they have). What I meant was something like - `IF OBJECT_ID(@tablename, 'U') IS NULL RETURN; -- TABLE DOES NOT EXIST SO EXIT`, and `IF COL_LENGTH(@tablename, @tableid) IS NULL RETURN; -- COLUMN DOES NOT EXIST IN TABLE SO EXIT`, should have probably been a bit more clear, Sorry. – GarethD Jan 19 '16 at 17:59
2

@GarethD Oh well, I'll leave that to the OP to apply. If he wishes to run with tables or columns that don't exist, so be it. – TT. Jan 19 '16 at 18:02

score 1 · Answer 2 · answered Jan 19 '16 at 17:22

if your are sending Table name as parameter in that case you need to create dynamic query string. may help below script

 CREATE PROCEDURE sp_selectAllParents @id int, @tableid varchar(30), @tablename varchar(30)
 AS BEGIN
 SET NOCOUNT ON;
 SET @query=N'WITH ct AS (
  SELECT * FROM @tablename t WHERE @tableid = @id
  UNION ALL
  SELECT t.* FROM @tablename t JOIN ct ON t.parentId = ct.@tableid 
  )
  SELECT * FROM @tablename t WHERE @tableid NOT IN (SELECT @tableid FROM ct)'

 EXECUTE sp_executesql @query, @id,@tablename,@tableid
 END

@GarethD Yes got it,Thanks – Jaydip Jadhav Jan 19 '16 at 17:28 — Jaydip Jadhav, Jan 19 '16 at 17:28

WITH statement in dynamic sql

2 Answers2