Dynamic SQL

Hi,

Not sure if this is possible but...

I have some sql syntax saved in a column as VARCHAR in a table that I would like to merge into a select statement as subselects in stored procedure, is this possible?

For example the code would look like this:

SELECT
Id,
Name,
(SELECT col1 FROM tbl_2 WHERE tbl2.Id = tbl1.Id),
(SELECT col2 FROM tbl_2 WHERE tbl2.Id = tbl1.Id)
FROM
tbl_1

Open in new window


Where the subselects syntax is saved in tbl_3 column SQL.

This problem has come about from creating custom fields in my application to allow the user to add additional information. I would like to allow the user to flag if they would like to show this custom field on a search form. The only way I can get the correct info out is by using  subselect. I have tried a dynamic pivot but could not join this to the main query as it would require defined column names for a table function.

Thanks, Greg
spen_langAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Please post a concise and complete example. Include table DDL and sample data INSERT statements as runnable T-SQL script.

It's a severe security issue to let users store "subselect syntay" when you mean T-SQL. That's why an example is necessary.

"Customs fields" can mean different data models. So it's hard to tell which you use. So please post some information.
spen_langAuthor Commented:
Hi,

For custom fields I mean that the user would like to store additional information about a printer that is not already available in the Printer table.

For example the Printer table consists of the following columns:

tbl_Printers

Printers_ID,
Printers_Name,
Printers_Location

But the user would like to store the printers latest toner reorder date. For this I have created 2 tables to hold this information, one table holds the metadata about each custom field and another table that contains the values

tbl_cFields

cFields_ID INT,
cFields_Name VARCHAR,
cFields_DataType VARCHAR (bit, date, decimal, string),
cFields_ShowOnSearchForm BIT,
cFields_SQL VARCHAR

The data in the SQL column will be generated by the insert Stored Procedure and will look something like this for a CustomField that has an ID of 1 and a Name of Test.
(SELECT C.cValues_String FROM tbl_cValues C WHERE C.cValues_cFieldsID = 1 AND C.cValues_PrinterID = Printer_ID) [Test]


tbl_cValues

cValues_ID INT,
cValues_CustomFieldID INT,
cValues_PrinterID INT,
cValues_Bit Bit,
cValues__Decimal Decimal,
cValues__Integer INT,
cValues__String VARCHAR

So for example if the CustomField is of data type Bit then the application will store the data in the Value_Bit column, if String it will store data in the Value_String column this is to ensure that the data types are correct and not all stored as a string.

When a user searches for a printer in the application the user is displayed with a search result form. I would like CustomField values to displayed on this form if the tbl_cFields.ShowOnSearchForm is True.

I have created a a select syntax for dynamic pivot that transposes the results from rows to columns but because it is dynamic I cannot create this as a table function as I cannot define the columns names as they may change.

Another option is to create a subselect for each CustomField but this will require manually updating the proc that is attached to the "Searh result form" each time a new custom field is added.

Below is the insert proc to create a new CustomField which also creates the cFields_SQL

ALTER PROCEDURE [dbo].[proc_cFields_Insert]
(
	@cFields_Name VARCHAR(50),
	@cFields_DataType VARCHAR(15),
	@cFields_ShowOnSearchForm BIT,
	@NewcFieldsID INT OUT
)
	
AS

BEGIN

	INSERT INTO tbl_cFields
		(
			cFields_Name,
			cFields_DataType,
			cFields_ShowOnSearchForm
		)
	VALUES
		(
			@cFields_Name,
			@cFields_DataType,
			@cFields_ShowOnSearchForm
		)	
	
END;

SET @NewcFieldsID = @@IDENTITY
DECLARE @cFields_SQL VARCHAR(255)

IF @cFields_DataType = 'bit'
	SET @cFields_SQL = '(SELECT C.cValues_Bit FROM tbl_cValues C WHERE C.cValues_cFieldsID = @cFields_ID AND C.cValues_PrinterID = Printer_ID) @cFields_Name'
ELSE IF @cFields_DataType = 'date'
	SET @cFields_SQL = '(SELECT C.cValues_Date FROM tbl_cValues C WHERE C.cValues_cFieldsID = @cFields_ID AND C.cValues_PrinterID = Printer_ID) @cFields_Name'
ELSE IF @cFields_DataType = 'datetime'
	SET @cFields_SQL = '(SELECT C.cValues_DateTime FROM tbl_cValues C WHERE C.cValues_cFieldsID = @cFields_ID AND C.cValues_PrinterID = Printer_ID) @cFields_Name'
ELSE IF @cFields_DataType = 'decimal'
	SET @cFields_SQL = '(SELECT C.cValues_Decimal FROM tbl_cValues C WHERE C.cValues_cFieldsID = @cFields_ID AND C.cValues_PrinterID = Printer_ID) @cFields_Name'
ELSE IF @cFields_DataType = 'integer'
	SET @cFields_SQL = '(SELECT C.cValues_Integer FROM tbl_cValues C WHERE C.cValues_cFieldsID = @cFields_ID AND C.cValues_PrinterID = Printer_ID) @cFields_Name'
ELSE IF @cFields_DataType = 'string'
	SET @cFields_SQL = '(SELECT C.cValues_String FROM tbl_cValues C WHERE C.cValues_cFieldsID = @cFields_ID AND C.cValues_PrinterID = Printer_ID) @cFields_Name'


SET @cFields_SQL = REPLACE(@cFields_SQL, '@cFields_ID', @NewcFieldsID)
SET @cFields_SQL = REPLACE(@cFields_SQL, '@cFields_Name', QUOTENAME(@cFields_Name))

BEGIN

	UPDATE tbl_cFields
		SET cFields_SQL = @cFields_SQL
	WHERE
		cFields_ID = @NewcFieldsID
			
END

Open in new window



Below is the query that I used for the dynamic pivot SQL that I worked perfectly but could not return result as a temp table

DECLARE	@cols AS NVARCHAR(MAX); 
DECLARE	@query  AS NVARCHAR(MAX);

SELECT @cols = STUFF(
						(
							SELECT
								',' + QUOTENAME(cFields_Label) 
							FROM
								tbl_cFields
							WHERE
								cFields_ShowOnSearchForm = 1 
							ORDER BY
								cFields_Order DESC
					
						FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')

SET @query = 'SELECT 
					cValues_PrinterID, '
					+ @cols +
				'FROM 
					(
						SELECT
							cValues_PrinterID,
							cFields_Label,
							CASE cFields_DataType
								WHEN ''bit'' THEN CAST(cValues_Bit AS VARCHAR(20))   
								WHEN ''date'' THEN CONVERT(VARCHAR(10), cValues_Date, 103)  
								WHEN ''datetime'' THEN CONVERT(VARCHAR(10), cValues_DateTime, 103) + '' '' + CONVERT(VARCHAR(8), cValues_DateTime, 108)
								WHEN ''decimal'' THEN CAST(cValues_Decimal AS VARCHAR(20))    
								WHEN ''integer'' THEN CAST(cValues_Integer AS VARCHAR(20))  
								WHEN ''string'' THEN CAST(cValues_String  AS VARCHAR(20))
							END Value
						FROM
							tbl_cFields F
							LEFT OUTER JOIN tbl_cValues V ON (F.cFields_ID = V.cValues_cFieldsID)
						WHERE
							cFields_ShowOnSearchForm = 1
					) vwUnpivot
					
					PIVOT 
					(
						MAX(Value) FOR cFields_Label IN (' + @cols + ')
					) vwPivot '

EXECUTE(@query)

Open in new window

ste5anSenior DeveloperCommented:
You need to create one dynamic SQL covering the entire query. This is imho a classic case of do it in the front-end.

btw, stuffing all data types into one table is suboptimal. It forces the front-end to parse string values into native data types. This maybe affected by wrong set locales, resulting in wrong values. And it requires two casts, which are unnecessary. It also is a violation of the design principle, that one tables holds one entity respective one (general) domain type.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

spen_langAuthor Commented:
I am not stuffing all data types into one column, each data type is inserted into it's own column data type. So a bit custom value will be inserted into the bit datatype column, the date custom value will be inserted into the date data type column etc. I did this so there was no need to cast or pasre the data in the app.

I forgot to mention that this was another reason why the dynamic pivot was a no go as it meant i had to cast all data types to string, which as you said may cause problems.

So how can I get the SQL out of the column cFields_SQL building dynamic SQL without having a problem with SQL injection.

Are you able to provide any examples on how I could proceed?
ste5anSenior DeveloperCommented:
I wasn't talking about one column, but about the table having a column for each data type. Normally in an EAV model you would have on value table per data type.

We don't have a SQL injection problem, cause it's an EAV model and the dynamic part here is run in a controlled manner.

You can avoid the casts by using on pivot per data type. Hey, now a table per data type would make also sense.

But to return one big result table you need a temporary table or you need to do it in one big dynamic SQL batch.

For the temporary table approach: Create a base temporary table with the known, fix columns. Use the dynamic approach to create a dynamic ALTER TABLE statement to append the pivot columns.

Then you can use an INSERT INTO in the single dynamic pivot queries.

Simplified:

CREATE TABLE #Test ( ID INT );

DECLARE @Columns VARCHAR(255);
DECLARE @Sql VARCHAR(MAX);

SET @Columns = '[A], [B]';
SET @Sql = 'ALTER TABLE #TEST ADD @Columns;';
SET @Sql = REPLACE(@Sql, '@Columns', REPLACE(@Columns, ',', ' INT, ') + ' INT');
EXECUTE (@Sql);

SET @Sql = 'INSERT INTO #Test VALUES ( 1, 2, 3 );';
EXECUTE (@Sql);

SELECT  *
FROM    #Test T;

DROP TABLE #Test;

Open in new window

spen_langAuthor Commented:
OK, so to follow normal EAV models I should do the following:

tbl_Printer
PrinterID INT
Name VARCHAR

tbl_Attributes
AttributeID INT
Name VARCHAR
DataType VARCHAR
SHowOnSearchForm BIT

tbl_PrinterAttribute_Bit
PrinterID INT
AttributeID INT
Value BIT

tbl_PrinterAttribute_Date
PrinterID INT
AttributeID INT
Value Date

tbl_PrinterAttribute_String
PrinterID INT
AttributeID INT
Value VARCHAR

And so on for each Data type I would like to offer...

If I did it this way how could I tie all the tables back together to show a result of side-by-side columns for each attributes ID where the SHowOnSearchForm is set to True?

I I create the temp table how could I control that the created columns are in the same order as the pivot query?

Sorry to keep asking but I am now getting a little confused on how to proceed and would rather do it right so that I can use this as a basis for EAV going forwards on other projects.
spen_langAuthor Commented:
In your example you are setting the datatypes for all of the columns to INT

SET @Sql = REPLACE(@Sql, '@Columns', REPLACE(@Columns, ',', ' INT, ') + ' INT');

This will not always be the case as some may be VARCHAR , DECIMAL, DATE or BIT. How do i set the correct datatype?
spen_langAuthor Commented:
Should I use this or could this potential cause concern for SQL injection?

SELECT
      ',' + QUOTENAME(CustomFields_Label) + ' ' + CustomFields_DataType
FROM
      tbl_CustomFields
WHERE
      CustomFields_ShowOnSearchForm = 1
ORDER BY
      CustomFields_Order DESC
ste5anSenior DeveloperCommented:
E.g.

CREATE TABLE #Printers
    (
      PrinterID INT ,
      PrinterName VARCHAR(255) ,
      UNIQUE ( PrinterName )
    );

CREATE TABLE #Attributes
    (
      AttributeID INT ,
      AttributeName VARCHAR(255) ,
      DataType VARCHAR(255) ,
      UNIQUE ( AttributeName )
    );

-- This hack is neccessary, cause no aggregate is possble on BIT.
CREATE TABLE #BitValues
    (
      PrinterID INT ,
      AttributeID INT ,
      BitValue INT CHECK ( BitValue IN ( 0, 1 ) ) ,
      UNIQUE ( PrinterID, AttributeID )
    );

INSERT  INTO #Printers
VALUES  ( 1, 'Test1' ),
        ( 2, 'Test2' );

INSERT  INTO #Attributes
VALUES  ( 1, 'Color', 'BIT' ),
        ( 2, 'HighRes', 'BIT' );

INSERT  INTO #BitValues
VALUES  ( 1, 1, 1 ),
        ( 1, 2, 0 ),
        ( 2, 1, 1 ),
        ( 2, 2, 1 );

CREATE TABLE #PrinterProperties ( PrinterID INT );

DECLARE @Columns VARCHAR(MAX);
SET @Columns = STUFF(( SELECT   ', ' + QUOTENAME(A.AttributeName)
                       FROM     #Attributes A
					   FOR XML PATH('')
                     ), 1, 2, '');

DECLARE @Sql VARCHAR(MAX);
SET @Sql = 'ALTER TABLE #PrinterProperties ADD @Columns;';
SET @Sql = REPLACE(@Sql, '@Columns', REPLACE(@Columns, ',', ' INT, ') + ' INT');
EXECUTE (@Sql);

SET @Sql = '
WITH    Data
          AS ( SELECT   BV.PrinterID ,
                        A.AttributeName ,
                        BV.BitValue
               FROM     #Attributes A
                        INNER JOIN #BitValues BV ON BV.AttributeID = A.AttributeID
             ),
        Pivoted
          AS ( SELECT   P.*
               FROM     Data D PIVOT ( MIN(D.BitValue) FOR AttributeName IN ( @Columns ) ) P
             )
    INSERT  INTO #PrinterProperties
            SELECT  P.*
            FROM    Pivoted P;
';
SET @Sql = REPLACE(@Sql, '@Columns', @Columns);
EXECUTE (@Sql);

SELECT  P.PrinterName ,
        PP.*
FROM    #PrinterProperties PP
        INNER JOIN #Printers P ON P.PrinterID = PP.PrinterID;

DROP TABLE #PrinterProperties;
DROP TABLE #Attributes;
DROP TABLE #BitValues;
DROP TABLE #Printers;

Open in new window


Column order? Just create an appropriate select in the end..
ste5anSenior DeveloperCommented:
SQL Injection: No.

Cause

a) you use QUOTENAME().
b) you verified when storing the attribute name, that they don't contain weird sequences.
spen_langAuthor Commented:
In your example you are setting the datatypes for all of the columns to INT

SET @Sql = REPLACE(@Sql, '@Columns', REPLACE(@Columns, ',', ' INT, ') + ' INT');

This will not always be the case as some may be VARCHAR , DECIMAL, DATE or BIT. How do i set the correct datatype?
ste5anSenior DeveloperCommented:
You need to create one properties temp table per data type...
spen_langAuthor Commented:
OK thank you for all your help, I am going to give this a go will let you know how i get on...
ste5anSenior DeveloperCommented:
E.g.

CREATE TABLE #Printers
    (
      PrinterID INT ,
      PrinterName VARCHAR(255) ,
      UNIQUE ( PrinterName )
    );

CREATE TABLE #Attributes
    (
      AttributeID INT ,
      AttributeName VARCHAR(255) ,
      DataType VARCHAR(255) ,
      UNIQUE ( AttributeName )
    );

-- This hack is neccessary, cause no aggregate is possble on BIT.
CREATE TABLE #BitValues
    (
      PrinterID INT ,
      AttributeID INT ,
      BitValue INT CHECK ( BitValue IN ( 0, 1 ) ) ,
      UNIQUE ( PrinterID, AttributeID )
    );

CREATE TABLE #FloatValues
    (
      PrinterID INT ,
      AttributeID INT ,
      FloatValue INT ,
      UNIQUE ( PrinterID, AttributeID )
    );

INSERT  INTO #Printers
VALUES  ( 1, 'Test1' ),
        ( 2, 'Test2' ),
        ( 3, 'Test3' );

INSERT  INTO #Attributes
VALUES  ( 1, 'Color', 'BIT' ),
        ( 2, 'HighRes', 'BIT' ),
        ( 3, 'Max Page Area', 'FLOAT' );

INSERT  INTO #BitValues
VALUES  ( 1, 1, 1 ),
        ( 1, 2, 0 ),
        ( 2, 1, 1 ),
        ( 2, 2, 1 ),
        ( 3, 1, 0 ),
        ( 3, 2, 0 );

INSERT  INTO #FloatValues
VALUES  ( 3, 3, 123.45 );

CREATE TABLE #PrinterPropertiesBit ( PrinterID INT );
CREATE TABLE #PrinterPropertiesFloat ( PrinterID INT );

DECLARE @Columns VARCHAR(MAX);
DECLARE @Sql VARCHAR(MAX);

-- Lather, rinse, repeat..
SET @Columns = STUFF(( SELECT   ', ' + QUOTENAME(A.AttributeName)
                       FROM     #Attributes A
                       WHERE    A.DataType = 'BIT'
					   FOR XML PATH('')
                     ), 1, 2, '');

SET @Sql = 'ALTER TABLE #PrinterPropertiesBit ADD @Columns;';
SET @Sql = REPLACE(@Sql, '@Columns', REPLACE(@Columns, ',', ' INT, ') + ' INT');
EXECUTE (@Sql);

SET @Sql = '
WITH    Data
          AS ( SELECT   BV.PrinterID ,
                        A.AttributeName ,
                        BV.BitValue
               FROM     #Attributes A
                        INNER JOIN #BitValues BV ON BV.AttributeID = A.AttributeID
             ),
        Pivoted
          AS ( SELECT   P.*
               FROM     Data D PIVOT ( MIN(D.BitValue) FOR AttributeName IN ( @Columns ) ) P
             )
    INSERT  INTO #PrinterPropertiesBit
            SELECT  P.*
            FROM    Pivoted P;
';
SET @Sql = REPLACE(@Sql, '@Columns', @Columns);
EXECUTE (@Sql);

SET @Columns = STUFF(( SELECT   ', ' + QUOTENAME(A.AttributeName)
                       FROM     #Attributes A
                       WHERE    A.DataType = 'FLOAT'
					   FOR XML PATH('')
                     ), 1, 2, '');

SET @Sql = 'ALTER TABLE #PrinterPropertiesFloat ADD @Columns;';
SET @Sql = REPLACE(@Sql, '@Columns', REPLACE(@Columns, ',', ' FLOAT, ') + ' FLOAT');
EXECUTE (@Sql);

SET @Sql = '
WITH    Data
          AS ( SELECT   BV.PrinterID ,
                        A.AttributeName ,
                        BV.FloatValue
               FROM     #Attributes A
                        INNER JOIN #FloatValues BV ON BV.AttributeID = A.AttributeID
             ),
        Pivoted
          AS ( SELECT   P.*
               FROM     Data D PIVOT ( MIN(D.FloatValue) FOR AttributeName IN ( @Columns ) ) P
             )
    INSERT  INTO #PrinterPropertiesFloat 
            SELECT  P.*
            FROM    Pivoted P;
';
SET @Sql = REPLACE(@Sql, '@Columns', @Columns);
EXECUTE (@Sql);

SELECT  P.PrinterName ,
        PPB.*,
        PPF.*
FROM    #Printers P 
        LEFT JOIN #PrinterPropertiesBit PPB ON P.PrinterID = PPB.PrinterID
		LEFT JOIN #PrinterPropertiesFloat PPF ON P.PrinterID = PPF.PrinterID;

DROP TABLE #PrinterPropertiesBit;
DROP TABLE #PrinterPropertiesFloat;
DROP TABLE #Attributes;
DROP TABLE #BitValues;
DROP TABLE #FloatValues;
DROP TABLE #Printers;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.