CODEWITHSUNDEEP
sqlteradata
Filip Eriksson
Filip Eriksson

Reputation: 977

Can't set column name equal to variable which is determined by the user

I am creating a result set where I want the column name to be equal to a variable name that is et during run time. Is that possible ? How do I do that?

In the example below the user choses the date (myDate) before running the query (e.g 2015-06-11). The I want the column name to be that date (2015-06-11). How do I do that? FYI: I'm using Teradata.

    SELECT 
    table_A.Cnt as ?myDate 
/* I can't write ?myDate like that. I also tried to convert it to a string */

    FROM
    (
    SELECT COUNT(*) AS Cnt FROM A
       WHERE theDate=?myDate 
     ) AS table_A

Upvotes: 1

Views: 1069

Answers (1)

JNevill
JNevill

Reputation: 50200

What you are trying to do is parameterize an object (or the name of an object) rather than parameterize a value, which seems straight forward when you think up the idea, but it's a bit more difficult to pull off.

First off, only an SP allows you to write and execute SQL dynamically, which is what you are doing here. Second, it's a little verbose. Third, it opens you up to SQL injection issues since you are slipping a parameter from a user into SQL then executing it, so proceed cautiously and do what you can to prevent a-holes from mucking up your system.

CREATE PROCEDURE paramMyField
(
    IN myDate Date, 

    --This has to be less than 30 otherwise Teradata will be angry. 
    --I would set it low just to keep injection possibilities to minimum
    IN fieldName VARCHAR(10) 
)

--Tell it how many result sets this thing is going to return:
DYNAMIC RESULT SETS 1

--Set the security (using the security of the bloke that sets this thing off, if you don't trust them, neither do I)
SQL SECURITY INVOKER

BEGIN

    --We'll need a variable to hold the dynamically generated sql statement
    DECLARE dynSQL VARCHAR(5000);

    --And we'll need a cursor and a statement
    DECLARE dynCursor CURSOR WITH RETURN ONLY FOR dynStatement;

    SET dynSQL = '  
        SELECT 
            table_A.Cnt as ' || fieldName || '
        FROM
            (
            SELECT COUNT(*) AS Cnt FROM A
               WHERE theDate = DATE ''' || myDate || '''
             ) AS table_A;';

    --Now to prep the statement
    PREPARE dynStatement FROM dynSQL;

    --And open the cursor (we will open and not close it so it's sent back as a resultset
    OPEN dynCursor;

END;

There's a lot happening there, but basically it's a stored procedure that takes in two parameters (the date and the name of the field) and spits back a record set that is the results of the SQL statement with a dynamically named field. It does this by using a dynamic SQL statement.

This is executed by running something like:

CALL paramMyField(DATE '2015-06-15', 'Whatever');

Upvotes: 2

Related Questions