Indirect reference to variable

  • TL:DR;

    I have a bunch of repetitive code (error checking, raiseerror) in a stored procedure I'm writing.  I'd like to convert this to a loop.

    Is it possible to have an indirect reference to a variable in T-SQL?

    For example:

    DECLARE @foo VARCHAR(10) = 'FOO'
    DECLARE @bar VARCHAR(10) = 'foo'
    PRINT @@bar

     

    It obviously doesn't work but you get the idea.

     

    Details:

    My actual code looks like:

        IF @ProjectID IS NULL
    BEGIN
    IF @ProjectName IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'ProjectName'
    )
    RETURN
    END

    IF @ProjectDesc IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'ProjectDesc'
    )
    RETURN
    END

    IF @ProjectSpon IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'ProjectSpon'
    )
    RETURN
    END

    IF @FileTrimNum IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'FileTrimNum'
    )
    RETURN
    END

    IF @Department IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'Department'
    )
    RETURN
    END

     

    I'd like to tighten the code and get rid of the cut-and-paste repetition.

     

    Note that the parameters are only conditionally required, when @ProjectID is NULL.

  • Are you certain so much individual error checking is necessary?  Sometimes it can be less wordy to "jfdi", or just f---ing do it.  Whatever you're trying to do once all the checking is done, just try it and see what happens.  Then catch any errors.  Anyway, please post the whole proc

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I wonder if you could just do a case or something, like

    select @returnmsg = case when @projectname is null then 'projectname' when @projectdesc is null then 'projectdisc' end

    RAISERROR (

    '%s is a required parameter.'

    ,16

    ,-1

    ,@returnmsg

    )

    too lazy to try it tho %)

    Anyways, I just think that t-sql isn't exactly a hugely flexible language and some conveniences like avoiding duplication are just not worth the costs, feel free to disagree tho.

     

  • Hi @scdecade,

    Ok, here are my SP's but first some background...

    We currently have a flattened, denormalized operational table.  There is a lot of junk in that table.  I'm looking to normalize the data by splitting into 4 tables, and use a view to join the components.  The normalized data will be easier to keep clean and fix errors.

    Those components are:

    Source: Source of data (only 4 rows: Hospital, Emergency, Death, Cause of Death. Static data.)
    Project: A particular project
    Service: A service(s) run against a project
    Batch: The actual batch run(s) against the service. Captures metrics. The "fact" table.

     

    The structure is hierarchical and a bit like a file system.  A view will join the tables and "flatten" the data.

    The business process is:

    • Create a new project (including "cloning" an existing project).  A relatively rare process.
    • Create a new service (including "cloning" an existing service). A more common process - multiple services per project.
    • Find an existing project (needed when creating a new service)
    • Find an existing service (which will automatically find the existing project via the view)
    • The end user must *always* find an existing service/project before running the rest of the code.  Creating a new project/service is just the precursor to finding.

    Here are my two SP's so far.

    To create a new Project:

    ALTER PROCEDURE [dbo].[spNewProject]
    @ProjectID INT = NULL
    ,@ProjectName VARCHAR(64) = NULL
    ,@ProjectDesc VARCHAR(256) = NULL
    ,@ProjectSpon VARCHAR(50) = NULL
    ,@FileTrimNum VARCHAR(20) = NULL
    ,@Department CHAR(3) = NULL
    ,@Help BIT = 0
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Declare working variables
    DECLARE @id INT, @colname SYSNAME
    DECLARE @colnames TABLE (id INT, colname SYSNAME)
    INSERT INTO @colnames VALUES
    (1,'ProjectName'),
    (2,'ProjectDesc'),
    (3,'ProjectSpon'),
    (4,'FileTrimNum'),
    (5,'Department')

    DECLARE @helptext VARCHAR(4000) = '
    EXEC spNewProject @ProjectID = [NULL | <existing ProjectID> ]
    ,@ProjectName = [<Project Name>]
    ,@ProjectDesc = [<Project Description>]
    ,@ProjectSpon = [<Project Sponsor>]
    ,@FileTrimNum = [<FileTrimNumber>]
    ,@Department = [''EBU'' | ''DIU'']
    ,@Help = [0 | 1]

    This stored procedure creates a New Project, including cloning an existing Project.

    To create a brand new Project:
    Leave ProjectID = NULL (default).
    All other parameters must be specified.
    ProjectNum = MAX(ProjectNum)+1.

    To clone an existing Project:
    Specify an existing ProjectID. This will return exactly one row.
    The existing ProjectNum will be carried forward to the new Project.
    Specify any parameters to override the data from the cloned ProjectID.
    For example, if the ProjectSponsor changes.

    ProjectName:
    If possible, enter a short mnemonic that uniquely identifies the Project.
    For example, MLK, APEDDR, MCHR, INJDEM, THROMBO, CDMP, CRS, C.H.E.S.T, etc.
    A short mnemonic allows you to easily search for the ProjectName later.
    There must be a one-to-one match between the ProjectNum and the ProjectName.

    ProjectSponsor:
    The ProjectSponsor is the surname of the "owner" of the project.
    For example, TAYLOR for MLK and APEDDR, or the investigator''s surname.
    The ProjectSponsor will be uppercased when saved to the Project table.
    Enter ''XXX'' if the ProjectSponsor is unknown,
    but update the ProjectSponsor once it is known.

    FileTrimNumber:
    A Project should be supported by a TRIM File Number.
    You should create a TRIM File Number, even if it is a dummy (barebones) TRIM entry,
    before creating a new Project in the Project table.
    Enter ''XX/XXXX'' if you are unable to create a dummy TRIM File Number,
    but update the FileTrimNumber once the TRIM File Number has been created.

    Department:
    Enter ''EBU'' if the Project is owned by EBU (for example APEDDR)
    Enter ''DIU'' if the Project is owned by DIU (for example Data To Investigators)

    Execute this stored procedure without any parameters to list the entire Project table.
    ';

    -- Print help if requested
    IF @Help = 1
    BEGIN
    RAISERROR (
    '%s'
    ,-1
    ,-1
    ,@helptext
    )
    RETURN
    END

    -- If blank strings were specified reset to NULL to use with COALESCE
    IF @ProjectName = ''
    SET @ProjectName = NULL
    IF @ProjectDesc = ''
    SET @ProjectDesc = NULL
    IF @ProjectSpon = ''
    SET @ProjectSpon = NULL
    IF @FileTrimNum = ''
    SET @FileTrimNum = NULL
    IF @Department = ''
    SET @Department = NULL

    -- If no parameters were specified, display the Project table and return
    IF @ProjectID IS NULL
    AND @ProjectName IS NULL
    AND @ProjectDesc IS NULL
    AND @ProjectSpon IS NULL
    AND @FileTrimNum IS NULL
    AND @Department IS NULL
    BEGIN
    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    ORDER BY
    ProjectNum
    ,ProjectID
    RETURN
    END

    -- Error checking
    IF @Department IS NOT NULL AND @Department NOT IN ('EBU','DIU')
    BEGIN
    RAISERROR (
    'Invalid value for %s. Valid values are ''EBU'' or ''DIU''.'
    ,16
    ,-1
    ,'Department'
    )
    RETURN
    END

    -- ProjectID is NULL so it's a brand new Project. All parameters must be specified.
    IF @ProjectID IS NULL
    BEGIN
    --SET @id = 1
    --WHILE @id <= 5
    IF @ProjectName IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'ProjectName'
    )
    RETURN
    END

    IF @ProjectDesc IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'ProjectDesc'
    )
    RETURN
    END

    IF @ProjectSpon IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'ProjectSpon'
    )
    RETURN
    END

    IF @FileTrimNum IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'FileTrimNum'
    )
    RETURN
    END

    IF @Department IS NULL
    BEGIN
    RAISERROR (
    '%s is a required parameter.'
    ,16
    ,-1
    ,'Department'
    )
    RETURN
    END

    -- For a new project the ProjectName must be unique
    IF EXISTS (
    SELECT 1
    FROM oper.Project
    WHERE ProjectName = @ProjectName
    )
    BEGIN
    -- SAS Bug: RAISEERROR must be before SELECT statement
    RAISERROR (
    'The ProjectName must be unique for a new project.'
    ,16
    ,-1
    )

    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    WHERE ProjectName = @ProjectName

    RETURN
    END

    -- All other error checking is handled by the indexes (with less user friendly error messages)

    INSERT INTO oper.Project
    (
    SourceID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    )
    VALUES
    (
    1 -- SourceID for APDC
    ,(
    SELECT MAX(ProjectNum) + 1
    FROM oper.Project
    )
    ,@Department
    ,@ProjectName
    ,@ProjectDesc
    ,@ProjectSpon
    ,@FileTrimNum
    );
    END

    -- ProjectID is not NULL so clone existing project
    ELSE
    BEGIN
    -- the ProjectID must exist
    IF NOT EXISTS (
    SELECT 1
    FROM oper.Project
    WHERE ProjectID = @ProjectID
    )
    BEGIN
    -- SAS Bug: RAISEERROR must be before SELECT statement
    RAISERROR (
    'ProjectID %d does not exist.'
    ,16
    ,-1
    ,@ProjectID
    )

    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    ORDER BY
    ProjectID

    RETURN
    END

    -- At least one change must be specified
    IF @Department IS NULL
    AND @ProjectName IS NULL
    AND @ProjectDesc IS NULL
    AND @ProjectSpon IS NULL
    AND @FileTrimNum IS NULL
    BEGIN
    RAISERROR (
    'At least one change must be specified to create a cloned project.'
    ,16
    ,-1
    )
    RETURN
    END

    -- The change must be different from existing data.
    -- This is prevented by the AllColumns index,
    -- but handling this here gives a better error message.
    IF EXISTS (
    SELECT 1
    FROM oper.Project
    WHERE ProjectNum = (
    SELECT ProjectNum
    FROM oper.Project
    WHERE ProjectID = @ProjectID
    )
    AND Department = COALESCE(@Department,Department)
    AND ProjectName = COALESCE(@ProjectName,ProjectName)
    AND ProjectDescription = COALESCE(@ProjectDesc,ProjectDescription)
    AND ProjectSponsor = COALESCE(@ProjectSpon,ProjectSponsor)
    AND FileTrimNumber = COALESCE(@FileTrimNum,FileTrimNumber)
    )
    BEGIN
    -- SAS Bug: RAISEERROR must be before SELECT statement
    RAISERROR (
    'At least one change must be specified to create a cloned project.'
    ,16
    ,-1
    )

    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    WHERE ProjectNum = (
    SELECT ProjectNum
    FROM oper.Project
    WHERE ProjectID = @ProjectID
    )
    AND Department = COALESCE(@Department,Department)
    AND ProjectName = COALESCE(@ProjectName,ProjectName)
    AND ProjectDescription = COALESCE(@ProjectDesc,ProjectDescription)
    AND ProjectSponsor = COALESCE(@ProjectSpon,ProjectSponsor)
    AND FileTrimNumber = COALESCE(@FileTrimNum,FileTrimNumber)

    RETURN
    END

    -- If we've gotten to this point (no errors, no RETURNS), insert the new row
    INSERT INTO oper.Project
    (
    SourceID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    )
    SELECT 1 -- SourceID for APDC
    ,ProjectNum
    ,COALESCE(@Department,Department)
    ,COALESCE(@ProjectName,ProjectName)
    ,COALESCE(@ProjectDesc,ProjectDescription)
    ,COALESCE(@ProjectSpon,ProjectSponsor)
    ,COALESCE(@FileTrimNum,FileTrimNumber)
    FROM oper.Project
    WHERE ProjectID = @ProjectID
    END

    -- Return the new row to the calling code
    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,Asset
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    WHERE ProjectID = SCOPE_IDENTITY()
    END

     

    And to find an existing Project:

    ALTER PROCEDURE [dbo].[spGetProject]
    @ProjectID INT = NULL
    ,@ProjectNum INT = NULL
    ,@ProjectName VARCHAR(64) = NULL
    ,@ProjectDesc VARCHAR(256) = NULL
    ,@ProjectSpon VARCHAR(50) = NULL
    ,@FileTrimNum VARCHAR(20) = NULL
    ,@Department CHAR(3) = NULL
    ,@Help BIT = 0
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Declare working variables
    DECLARE @dummy BIT;
    DECLARE @rowcnt INT;

    DECLARE @helptext VARCHAR(4000) = '
    EXEC spGetProject @ProjectID = [NULL | <existing ProjectID> ]
    ,@ProjectNum = [<Project Number]
    ,@ProjectName = [<Project Name>]
    ,@ProjectDesc = [<Project Description>]
    ,@ProjectSpon = [<Project Sponsor>]
    ,@FileTrimNum = [<FileTrimNumber>]
    ,@Department = [''EBU'' | ''DIU'']
    ,@Help = [0 | 1]

    This stored procedure Finds (i.e. "Gets") a Project.

    The simplest and easiest way to find a Project is to specify its ProjectID.
    This is guaranteed to return one and only one Project.

    Otherwise, the combination of parameters must uniquely identify a Project.
    In other words, only one row should be returned.

    Execute this stored procedure without any parameters to list the entire Project table.
    ';

    -- Print help if requested
    IF @Help = 1
    BEGIN
    RAISERROR (
    '%s'
    ,-1
    ,-1
    ,@helptext
    )
    RETURN
    END

    -- If blank strings were specified reset to NULL
    IF @ProjectName = ''
    SET @ProjectName = NULL
    IF @ProjectDesc = ''
    SET @ProjectDesc = NULL
    IF @ProjectSpon = ''
    SET @ProjectSpon = NULL
    IF @FileTrimNum = ''
    SET @FileTrimNum = NULL
    IF @Department = ''
    SET @Department = NULL

    -- If no parameters were specified, display the Project table and return
    IF @ProjectID IS NULL
    AND @ProjectNum IS NULL
    AND @ProjectName IS NULL
    AND @ProjectDesc IS NULL
    AND @ProjectSpon IS NULL
    AND @FileTrimNum IS NULL
    AND @Department IS NULL
    BEGIN
    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    ORDER BY
    ProjectNum
    ,ProjectID
    RETURN
    END

    -- If ProjectID was specified ignore other parameters
    IF @ProjectID IS NOT NULL
    BEGIN
    SELECT @dummy = 1
    FROM oper.Project
    WHERE ProjectID = @ProjectID

    SET @rowcnt = @@rowcount;

    -- Valid ProjectID
    IF @rowcnt = 1
    BEGIN
    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    WHERE ProjectID = @ProjectID
    RETURN
    END

    -- Invalid ProjectID
    ELSE
    BEGIN
    -- SAS Bug: RAISEERROR must be before SELECT statement
    RAISERROR (
    'ProjectID %d does not exist.'
    ,16
    ,-1
    ,@ProjectID
    )

    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    ORDER BY
    ProjectID
    END
    END

    -- ProjectID was not specified so search using other other parameters
    ELSE
    BEGIN
    SELECT @dummy = 1
    FROM oper.Project
    WHERE ProjectNum = COALESCE(@ProjectNum,ProjectNum)
    AND Department LIKE COALESCE(@Department,Department)
    AND ProjectName LIKE COALESCE(@ProjectName,ProjectName)
    AND ProjectDescription LIKE COALESCE(@ProjectDesc,ProjectDescription)
    AND ProjectSponsor LIKE COALESCE(@ProjectSpon,ProjectSponsor)
    AND FileTrimNumber LIKE COALESCE(@FileTrimNum,FileTrimNumber)

    SET @rowcnt = @@rowcount;

    -- Invalid search criteria (no rows found)
    IF @rowcnt = 0
    BEGIN
    RAISERROR (
    'A project was not found with the specified search criteria.'
    ,16
    ,-1
    )
    RETURN
    END

    -- Incomplete search criteria (multiple rows found)
    IF @rowcnt > 1
    BEGIN
    -- This returns a WARNING in SAS (ErrorLevel=1)
    RAISERROR (
    'Multiple projects were found with the specified search criteria.'
    ,1
    ,-1
    )
    END

    -- For both Valid (one row) and Incomplete (multiple rows) search criteria,
    -- return the results
    SELECT ProjectID
    ,ProjectNum
    ,Department
    ,ProjectName
    ,ProjectDescription
    ,ProjectSponsor
    ,FileTrimNumber
    FROM oper.Project
    WHERE ProjectNum = COALESCE(@ProjectNum,ProjectNum)
    AND Department LIKE COALESCE(@Department,Department)
    AND ProjectName LIKE COALESCE(@ProjectName,ProjectName)
    AND ProjectDescription LIKE COALESCE(@ProjectDesc,ProjectDescription)
    AND ProjectSponsor LIKE COALESCE(@ProjectSpon,ProjectSponsor)
    AND FileTrimNumber LIKE COALESCE(@FileTrimNum,FileTrimNumber)
    END
    END

     

    The calling code is SAS Enterprise Guide.  The returned row(s) are displayed in the SAS Results window as a table.  The goal is to display data to help the end user select the correct project + service for the remaining SAS code.  The batch row and captured metrics are automatically added to the Batch table with the correct (i.e. found) ServiceID and and incrementing BatchID.

    I hope this context helps in explaining the purpose for the SP's.

    Improvements/criticism more than welcome - that's how I improve my skills 🙂

  • @SSC-Insane

    I just think that t-sql isn't exactly a hugely flexible language

    Agreed.  Hopefully it will improve over time and add some of the modern constructs from today's programming languages.  But I'll be retired by then 😉

     

  • Currently there are 2 procs:

    Proc 1 ("spNewProject") does 3 things: 1) returns all project records if all inputs are nulls, 2) if necessary creates new project and returns new record, 3) updates existing an record if the combination of inputs provided matches an existing project.

    Proc 2 ("spGetProject") does 2 things: 1) finds 1 and only 1 record if a primary key Id is specified, 2) returns 1 or more existing records if all inputs are appropriately provided (warning if 2+).

    Would it be possible to rearrange things a little so there are 3 procs instead of 2?

    Proc 1 ("spGetProjects") does 1 thing: 1) returns all project records

    Proc 2 ("spGetProjectById") does 1 thing: 1) finds 1 and only 1 record if a primary key is specified

    Proc 3("spFindProject") does 3 things: 1) returns 1 or more existing records if all inputs are appropriately provided (warning if 2+), 2) if necessary creates a new project and returns new record, 3) updates existing an record if the combination of inputs provided matches an existing project and returns update record.

    The 3 procs would always return 1 or more projects (the end goal).  With 3 procs the null checking could be reduced a lot.

     

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • @scdecade:

    1) returns all project records if all inputs are nulls

    Yes, as a convenience to the end user to review the data (the data volume is small ~ 250 projects)

    2) if necessary creates new project and returns new record

    Well it is always necessary, since it is (sp)NewProject.  The end result should be a new project row, and that row returned to the end user as confirmation of success (and it displays the ProjectID (identity column)).

    3) updates existing an record if the combination of inputs provided matches an existing project.

    Not exactly, it will "clone" an existing project, creating a new project row.  The usual business reason is an existing project gets a new sponsor.  Without a new project row the view would not reflect historically accurate information.  Not strictly SCD2 but analogous - I want the Service and Batch rows to reflect the correct Project as of the time they were generated.

    Proc 2 ("spGetProjectById") does 1 thing: 1) finds 1 and only 1 record if a primary key is specified

    Proc 3("spFindProject") does 3 things: 1) returns 1 or more existing records if all inputs are appropriately provided (warning if 2+), 2) if necessary creates a new project and returns new record, 3) updates existing an record if the combination of inputs provided matches an existing project and returns update record.

    I like the idea but not necessarily the SP name.  My end users aren't the most computer literate.  I want "New" to unambiguously mean "Create a new row", and "Get" to unambiguously mean "Find an existing row".  I chose "Get" merely because it has the same number of letters as "New".  "Find" is synonymous with "Get" and I don't really want the combined Create new row and Find existing row in a single SP.

    Perhaps my easiest solution is to force all Get/Find/Search to be by PK (<TableName>ID), with other columns as helpers to narrow the search.  But "success" is only when they search by PK.

    Thanks for the ideas...

    Back to the original topic...I guess T-SQL doesn't lend itself to "tight" coding constructs as in other languages.

    I did find this:  https://stackoverflow.com/questions/18380189/tsql-how-to-iterate-a-list-of-strings

    which would work to print the error message, but I need that indirect variable reference to check for NULL and tighten/shorten the code.

  • The procedure could be named whatever you wish.  "spFindExistingOrCreateNewProject" seems ok but a maybe little long...

    If the goal is to tighten/shorten the code then here are a few suggestions:

    1. Where the code has "raiserror and return" inside of a begin/end block, these could potentially be replaced with single-line "throw" statements.  There are about 10 occurrences of this in your code.
    2. After raiserror and before return, the code returns a row of nulls by executing queries.  These queries could be removed.
    3. After you insert into oper.Projects tables you select the row by querying on scope_identity, or id.  These could done with output clauses in the insert statement.
    4. The null checking currently done before insert could be done more compactly after the insert.  Philosophically, this makes sense too.  Checking to see if an insert is possible is not the same as actually inserting the record and the accuracy of the check is always subject to race conditions under load.

    These procedures only reference 1 table.  If you provide ddl and some sample input we could run the proc and test code changes.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply