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
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);
Answer by Kev Riley ·
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?
Answer by dlbridgeman ·
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.