September 1, 2019 at 11:18 pm
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.
September 2, 2019 at 3:11 pm
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
September 2, 2019 at 5:44 pm
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.
September 2, 2019 at 9:40 pm
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:
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 🙂
September 2, 2019 at 9:43 pm
@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 😉
September 3, 2019 at 12:33 am
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
September 3, 2019 at 12:58 am
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.
September 3, 2019 at 12:05 pm
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:
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply