Translating to t-sql

[Joe]

We have a process that generates a unique table name and stores intermediate data in it as a # table. To manage the # table we have two Fox functions:

BackupTempTable()
RestoreTempTable()

text to lcSql textmerge noshow
    IF OBJECT_ID('TempDb..#TempTableBackup') IS NOT NULL DROP TABLE #TempTableBackup
    select *
        into #TempTableBackup    && Create a backup of the original temp table and then drop it
        from <<This.cTempTable>>
    DROP TABLE <<This.cTempTable>>
endtext

This.oConn.ExecuteUpdate(lcSql)

text to lcSql textmerge noshow
    IF OBJECT_ID('TempDb..<<This.cTempTable>>') IS NOT NULL DROP TABLE <<This.cTempTable>>
    select *
        into <<This.cTempTable>>    && Restore the original temp file and drop the backup
        from #TempTableBackup
    DROP TABLE #TempTableBackup
endtext

This.oConn.ExecuteUpdate(lcSql)

The problem is that # table name is always different when these two routines are called. So in a translation to t-sql, dynamic sql needs to be used. This means the sql in our lcSql string must be called through "EXEC @lcSql" or "EXEC sp_executesql @lcSql". The problem there is EXEC uses a unique SPID, so the # table is no longer visible when we come back into the calling environment. We were able to go to a ## table, but coming back from that to a single # table does not work. Here's a code snippet from the routine that fails testing. This is in the Restore routine.

SET @lcSelect = 'SELECT * INTO ' + @tcTempTable + ' FROM ##TempTableBackup '
EXEC sp_executesql @lcSelect         -- Create destination from source

IF OBJECT_ID(@tcTempTable) IS NULL   -- Destination does not exist, return error
    RETURN 2

The above routine always returns a 2.

 

[Joe]

Here's one that we were able to translate:

*-------------------------------------------------------------------------------
*- CheckTempTable | Checks to see if there are any records in the temp table
*-------------------------------------------------------------------------------
*- PARAMETERS:
*- tcErrorMessage    [required]    Name of temp table to pass

*- RETURNS:
*- .F. if tcTempTableName does not exist or has no rows
*- .F. if tcTempTableName not supplied
*- .T. if tcTempTableName exists and has at least 1 row

function CheckTempTable
lparameters tcTempTableName, tcErrorMessage
local llReturn

    llReturn = Iif(Empty(tcTempTableName) or vartype(tcTempTableName) <> 'C', .F., .T.)
    with this
        text to lcSql textmerge noshow
            if object_id('tempdb..<<tcTempTableName>>') is not null
                select COUNT(*) as num_rows from <<tcTempTableName>>
            else
                select 0 as num_rows
        endtext
        if not .oConn.ExecuteSelect(lcSql, 'csrRowCheck') or !Used("csrRowCheck") or csrRowCheck.Num_Rows = 0
            llReturn = .F.
        endif
    endwith

    if Used("csrRowCheck")
        select csrRowCheck
        use
    endif
 
    return llReturn
endfunc    &&    CheckTempTable

And the full replacement as a stored proc (see lines 25-28):

CREATE PROCEDURE [dbo].[SpMc_CheckTempTable]
    @tcTempTableName varchar(30)
    , @tcErrorMessage varchar(max) = '' OUTPUT
AS

SET NOCOUNT ON

    DECLARE @lnReturn INT
    SET @lnReturn = 0

    IF @tcTempTableName = '' RETURN 1                            -- name not given, return 1

    IF substring(ltrim(@tcTempTableName), 1, 1) = '#'            -- #
        IF OBJECT_ID('TempDb..' + @tcTempTableName) IS NULL    -- Does not exist, return 2
            RETURN 2

    IF substring(ltrim(@tcTempTableName), 1, 1) != '#'        -- non #
        IF OBJECT_ID(@tcTempTableName) IS NULL                    -- Does not exist, return 2
            RETURN 2

    DECLARE @lcParmDefinition NVARCHAR(500)
    DECLARE @lnNumRows INT
    DECLARE @lcSelect NVARCHAR(500)

    SET @lcSelect = 'SELECT @OutputNumRows = COUNT(*) FROM ' + @tcTempTableName                        
    SET @lcParmDefinition = '@OutputNumRows INT OUTPUT'

    EXEC sp_executesql @lcSelect, @lcParmDefinition, @OutputNumRows = @lnNumRows OUTPUT

    IF @lnNumRows = 0                                            -- no rows, return 3
        SET @lnReturn = 3

    RETURN @lnReturn

GO

And all the tests:

--1. parm blank
--2. parm not blank, but does not exist
--3. Parm not blank, exists, rowcount = 0
--4. Parm not blank, exists, rowcount > 0

declare @lnRetVal as INT
declare @lnRetValExpected as INT
declare @lcTempTable as varchar(max)
declare @lcError as varchar(max)

--1. parm blank
    SET @lcTempTable = ''
    SET @lnRetValExpected = 1

    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end

--2. parm not blank, but does not exist
    SET @lcTempTable = 'NotExist'
    SET @lnRetValExpected = 2

    if object_id(@lcTempTable) is not null drop table NotExist
    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end

--2#. parm not blank, but does not exist
    SET @lcTempTable = '#NotExist'
    SET @lnRetValExpected = 2

    if object_id('tempdb..#NotExist') is not null drop table #NotExist
    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end

--3. Parm not blank, exists, rowcount = 0
    SET @lcTempTable = 'TableExistsNoRows'
    SET @lnRetValExpected = 3

    if object_id(@lcTempTable) is not null drop table TableExistsNoRows
    select 13 f1 into TableExistsNoRows
    delete from TableExistsNoRows
   
    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end

--3#. Parm not blank, exists, rowcount = 0
    SET @lcTempTable = '#TableExistsNoRows'
    SET @lnRetValExpected = 3

    if object_id('tempdb..#TableExistsNoRows') is not null drop table #TableExistsNoRows
    select 13 f1 into #TableExistsNoRows
    delete from #TableExistsNoRows
   
    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end

--4. Parm not blank, exists, rowcount > 0
    SET @lcTempTable = 'TableExistsHasOneRow'
    SET @lnRetValExpected = 0

    if object_id(@lcTempTable) is not null drop table TableExistsHasOneRow
    select 13 f1 into TableExistsHasOneRow
   
    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end

--4#. Parm not blank, exists, rowcount > 0
    SET @lcTempTable = '#TableExistsHasOneRow'
    SET @lnRetValExpected = 0

    if object_id('tempdb..#TableExistsHasOneRow') is not null drop table #TableExistsHasOneRow
    select 13 f1 into #TableExistsHasOneRow
   
    exec @lnRetVal = spmc_checkTempTable @lcTempTable

    IF @lnRetVal != @lnRetValExpected
    BEGIN
        SET @lcError = '@lnRetVal should be ' + convert(char(5), @lnRetValExpected) + ' for parms = (' + @lcTempTable + ').  Got: ' + convert(char(5), @lnRetVal)
        RAISERROR(@lcError, 16, 1)
    end
    --select * from #TableExistsHasOneRow