November 17, 2021 at 10:18 pm
I would like to create one stored procedure that can handle from one to 10 parameters. Using the sledge hammer approach, I created 10 stored procedures; each one handles the number of parameters requested. For example, if I wanted to look at the values for 1 tank, I would call the stored procedure that only used one parameter:
[Code]
ALTER PROCEDURE [dbo].[sp_TankCounts_01]
-- Add the parameters for the stored procedure here
@Tank01 as Varchar(2),
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Drop table if exists Tbl_Multi_TankCount
Select
@Tank01 as Tank1,
Count(Substring (CheckDate, 1,10)) as Keno_Date_Count
into Tbl_Multi_TankCount
from RawData
where (
@Tank01 in (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)
)
Order by CheckDate
[/Code]
If I wanted to look at 5 tanks, I would call this stored procedure:
ALTER PROCEDURE [dbo].[sp_TankCounts_05]
-- Add the parameters for the stored procedure here
@Tank01 as Varchar(2),
@Tank02 as Varchar(2),
@Tank03 as Varchar(2),
@Tank04 as Varchar(2),
@Tank05 as Varchar(2)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Drop table if exists Tbl_Multi_TankCount
Select
@Tank01 as Tank1,
@Tank02 as Tank2,
@Tank03 as Tank3,
@Tank04 as Tank4,
@Tank05 as Tank5,
Count(Substring (CheckDate, 1,10)) as Keno_Date_Count
into Tbl_Multi_TankCount
from RawData
where (
@Tank01 in (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)
and @Tank02 in (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)
and @Tank03 in (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)
and @Tank04 in (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)
and @Tank05 in (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)
)
Order by CheckDate
[/Code]
I have searched the internet for my solution, but I'm not sure how to look for it. Any guidance would be helpful. Thanks
November 17, 2021 at 11:07 pm
Are all the KEN_N## tanks or something? Sounds like a design flaw. What does the underlying table look like?
November 17, 2021 at 11:07 pm
I think parameterized dynamic SQL is going to be you best bet.
Here are a few links
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/
https://www.sqlshack.com/dynamic-sql-in-sql-server/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 18, 2021 at 2:13 pm
Actually it sounds like you could put a tally table, or a table of numbers to work and simply pass in your 1-10 tanks (which really does sound like a design flaw) into the query. Here's an example of a tally table in action. Here's Jeff's main article too. Based on what you're asking, I think this is what you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2021 at 5:31 pm
A possible solution would be to unpivot the KEN_N# columns and use a string splitter on a delimited input parameter. This would allow for identifying not only the row that matched - but the column that was matched, including any duplicates if they exist.
If you can provide some sample data (in the form of create table and insert statement) - then we can actually provide some possible solutions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 18, 2021 at 5:51 pm
And remember to put in some clearly written comments so the next person looking at the code understands what's happening.
November 18, 2021 at 7:11 pm
The code below generates SQL for from 1 to 20 @Tank entries, based on which ones were actually provided in the call to the proc, and then EXECs the code. Naturally adjust the code as you need to.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
/*
EXEC dbo.sp_TankCounts 'aa', 'bb', 'cc', 'dd', 'ee'
*/GO
CREATE PROCEDURE dbo.sp_TankCounts
@Tank01 char(2),
@Tank02 char(2) = NULL,
@Tank03 char(2) = NULL,
@Tank04 char(2) = NULL,
@Tank05 char(2) = NULL,
@Tank06 char(2) = NULL,
@Tank07 char(2) = NULL,
@Tank08 char(2) = NULL,
@Tank09 char(2) = NULL,
@Tank10 char(2) = NULL,
@Tank11 char(2) = NULL,
@Tank12 char(2) = NULL,
@Tank13 char(2) = NULL,
@Tank14 char(2) = NULL,
@Tank15 char(2) = NULL,
@Tank16 char(2) = NULL,
@Tank17 char(2) = NULL,
@Tank18 char(2) = NULL,
@Tank19 char(2) = NULL,
@Tank20 char(2) = NULL
AS
SET NOCOUNT ON;
DECLARE @sql1 nvarchar(max);
DECLARE @sql2 nvarchar(max);
DECLARE @TankParamCount tinyint;
DROP TABLE IF EXISTS dbo.Tbl_Multi_TankCount;
--**********************************************************************************************************************
SET @sql1 = '';
SET @sql2 = '';
SET @TankParamCount = 0;
IF @Tank01 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank01 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank01 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank02 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank02 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank02 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank03 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank03 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank03 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank04 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank04 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank04 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank05 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank05 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank05 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank06 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank06 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank06 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank07 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank07 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank07 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank08 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank08 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank08 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank09 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank09 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank09 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank10 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank10 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank10 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank11 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank11 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank11 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank12 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank12 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank12 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank13 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank13 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank13 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank14 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank14 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank14 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank15 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank15 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank15 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank16 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank16 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank16 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank17 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank17 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank17 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank18 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank18 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank18 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank19 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank19 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank19 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
IF @Tank20 IS NOT NULL
BEGIN
SET @sql1 = @sql1 + ', [' + @Tank20 + ']'
SET @sql2 = @sql2 + ' AND ''' + @Tank20 + ''' IN (KEN_N01, KEN_N02, KEN_N03, KEN_N04, KEN_N05, KEN_N06, KEN_N07, KEN_N08, KEN_N09, KEN_N10, KEN_N11, KEN_N12, KEN_N13, KEN_N14, KEN_N15, KEN_N16, KEN_N17, KEN_N18, KEN_N19, KEN_N20)'
SET @TankParamCount = @TankParamCount + 1
END /*IF*/
--**********************************************************************************************************************
SET @sql1 =
'/* A total of ' + CAST(@TankParamCount AS varchar(3)) + ' non-NULL Tank parameters were passed to the proc. */ ' +
CHAR(13) + CHAR(10) +
'SELECT ' + STUFF(@sql1, 1, 2, '') + ', CAST(CheckDate AS date) AS CheckDay, COUNT(*) AS TankCount ' +
'INTO dbo.Tbl_Multi_TankCount ' +
'FROM dbo.RawData ' +
'WHERE ' + STUFF(@sql2, 1, 4, '') + ' ' +
'GROUP BY CAST(CheckDate AS date) '
PRINT @sql1
EXEC(@sql1)
--SELECT * FROM dbo.Tbl_Multi_TankCount ORDER BY CheckDay
/*end of proc*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 18, 2021 at 7:11 pm
Thank you all for answering my post. I have to work on another problem now and cannot provide you with more information at this time. I will get back on it as soon as I get the other problem resolved. Thanks again for your patience.
Carroll
November 20, 2021 at 12:11 am
This is one where you are probably going to have to make a tradeoff between performance and cognitivie load / maintenance costs. Your approach means than any changes in business logic need to be applied multiple times and increases the cost of testing as all 10 procedures need to be tested.
A better approach from a code maintenance perspective would be to have a 'master' procedure that takes a string of the tanks required, splits them using your preferred split method and then iteratively calls a single sproc with OPENROWSET to build a comprehensive dataset of all tanks. This will be a performance hit because you will have to run the logic up to 10 times where the query optimiser would probably return the same results for all 10 with a nominal overhead for each incremental tank.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply