question

dlbridgeman avatar image
dlbridgeman asked

Conditionally change returned datatypes in stored procedure

I have a database whose stored procedures need to be used by two different applications. The first one is an ASP.NET MVC application we developed inhouse, which recognizes the DATETIMEOFFSET datatype (which we chose because we have operations in more than one time zone). The second one is a Java-based application, which does not. (It appears there exists a JDBC which supports DATETIMEOFFSET, but this application doesn't use it.) It is simple enough to turn a DATETIMEOFFSET into a DATETIME in UTC and let the application convert it to the local timezone. But I have been racking my brain trying to figure out a way to let each application get the data in the format it requires without having to duplicate my stored procedures.

The first thing I tried to do was use an additional parameter in the stored procedures to specify which application was making the call, and then use a CASE statement to determine whether to return the DATETIMEOFFSET, or the DATETIME. That's when I learned how CASE statements handle differing datatypes. So no good there.


The next thing I tried was to create a "wrapper" stored procedure which would

  • call the desired stored procedure
  • put the results into a temp table
  • find the DATETIMEOFFSET columns
  • make a temporary DATETIME column for each
  • write the original value into the new column, after converting to UTC
  • drop the original column
  • recreate it as a DATETIME
  • put the value from the temporary column back into the new one
  • and finally, drop the temporary column

Just writing that out hammers home how convoluted this is. To make it worse, I had to use OPENROWSET, and to get that to accept a variable as the name of the stored procedure, it had to be called EXEC(@sql). The code snippet is below.


But this all failed when I called it from my application with this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Access to the remote server is denied because no login-mapping exists.

At this point I'm almost ready to throw my hands up in defeat and maintain two separate sets of stored procedures, identical except for how they handle time. But I'd really rather not, because it sounds like a maintenance nightmare. Another solution would be to refactor the whole database to use DATETIMEs in UTC, but that is a daunting task, and will also require retesting and/or fixing the .NET application.

Can anyone think of another way to conditionally return different datatypes from the same stored procedure? Or can anyone help me figure out why using OPENROWSET with dynamic SQL would cause the remote server access error that I'm getting with my "wrapper" stored procedure?

    SET @sql =
        N'
SELECT
    *
INTO
    ' + @globalTempTable
        + N'
FROM
    OPENROWSET(''SQLNCLI''
             , ''Server=' + @@SERVERNAME + ';Trusted_Connection=yes;''
             , ''' + @fullStatement + N'''
              );
'   ;
    EXEC (@sql);

    DECLARE @col NVARCHAR(128);
    DECLARE @newCol NVARCHAR(128);

    CREATE TABLE #cols (col sysname);
    INSERT INTO #cols (col)
    SELECT
        cols.name
    FROM tempdb.sys.columns cols
    JOIN sys.types          ty ON cols.user_type_id = ty.user_type_id
    WHERE cols.object_id = OBJECT_ID('tempdb..' + @globalTempTable)
        AND ty.name      = 'datetimeoffset';

    WHILE EXISTS (SELECT 1 FROM #cols)
    BEGIN
        SELECT TOP 1
               @col = col
        FROM #cols
        ORDER BY col;

        SELECT @newCol = N'__' + @col + N'__';

        SET @sql = N'ALTER TABLE ' + @globalTempTable + N' ADD ' + @newCol + N' DATETIME';
        EXEC (@sql);

        SET @sql =
            N'UPDATE ' + @globalTempTable + N' SET ' + @newCol + N' = CAST (' + @col
            + N' AT TIME ZONE ''UTC'' AS DATETIME)';
        EXEC (@sql);

        SET @sql = N'ALTER TABLE ' + @globalTempTable + N' ALTER COLUMN ' + @col + N' DATETIME';
        EXEC (@sql);

        SET @sql = N'UPDATE ' + @globalTempTable + N' SET ' + @col + N' = ' + @newCol;
        EXEC (@sql);

        SET @sql = N'ALTER TABLE ' + @globalTempTable + N' DROP COLUMN ' + @newCol;
        EXEC (@sql);

        DELETE #cols WHERE col = @col;
    END;
    DROP TABLE #cols;

    SET @sql = N'SELECT * FROM ' + @globalTempTable;
    EXEC (@sql);

    SET @sql = N'DROP TABLE ' + @globalTempTable;
    EXEC (@sql);
datatypesstored proceduresopenrowsetdynamic sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

If you are able to change the applications to pass in an additional parameter to identify the application, then why not have the SP return both datatypes as 2 separate columns, and have each application only use the columns it needs?

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Kev Riley, I can change the parameters that I send from the application to the stored procedure, but I can't prevent it trying to interpret the results. I can ignore them in the code I write to work with the results; but the part where it translatesSQL data types into Java data types or objects happens before then, and is mostly a black box.

0 Likes 0 ·

OK. I'd be tempted to have the 2 procs then, or one proc with conditional logic, but essentially 2 procs into one. I know you said that seems like a maintenance headache, but it seems less worse than that wrapper.

0 Likes 0 ·

If the 'base' query is complex and you don't want to duplicate it, then either use a CTE in the proc, or a view, or a inline-derived table - all of which give you the ability to produce all the columns and then the final select just chooses the columns based on the application id.

0 Likes 0 ·
dlbridgeman avatar image
dlbridgeman answered

I played around with it a bit after posting, and it seemed like the least overhead I could come up with was to put the results into a temp table, and then have two separate queries on the temp table in an if/else. It will still require changing the query on that temp table in two places, but at least it keeps it to one section of one stored procedure.

The wrapper does appear to work as desired when I execute it in SSMS, but if it doesn't work when called from the application, it's not useful. And it is a bit of a beast.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.