April 9, 2019 at 6:14 pm
Hi,
I'm developing a stored procedure that expects a parameter of N string values, each separated by a comma. Depending on the string, I'm planning to run a unique query. The structure is the same for all of the unique queries. Ultimately, I am planning to insert each result set into a temp table. I know there are other ways to approach this, but what do you all think would be the "smartest"? I'm more concerned about scalability and ease of maintenance than I am about finding the absolute fastest approach.
Here's a pseudo example of what I'm thinking
The call would be something like this:
EXEC MyStoredProcedure @MyStrings = N'Test1,Test2,Test3'
The stored procedure would do something like this:
- Read @MyStrings into a table, one row per comma-separated value
- Create a #temptable with the structure I need
- Loop through each value of the value table
-
IF @currentValue = "Test1"
BEGIN
INSERT INTO #temptable
SELECT @currentValue, SomeField
FROM SomeTable
END
IF @currentValue = "Test2"
BEGIN
INSERT INTO #temptable
SELECT @currentValue, SomeField
FROM AnotherTable
END
IF @currentValue = "Test3"
BEGIN
INSERT INTO #temptable
SELECT @currentValue, SomeField
FROM AThirdTable
END
IF @currentValue = "Test4"
BEGIN
INSERT INTO #temptable
SELECT @currentValue, SomeField
FROM AFourthTable
END
SELECT *
FROM #temptable
Maybe a DROP TABLE #temptable at the end (though I don't believe it's necessary)
I know that's a very rough structure, but is that how you all might approach the situation?
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
April 9, 2019 at 6:22 pm
Why not use Jeff Moden's DelimitedSplit8K? It returns an Item and the Item's ordinal position. Then you could just insert that into your final table.
Got any sample data for us to play with?
April 9, 2019 at 6:27 pm
I doubt you need a temp table. You can just UNION ALL all the different query results. If a given query returns no rows, that's OK, it will be effectively ignored.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 8:03 pm
I doubt if you need a loop or a union all.
Use Jeff's splitter, and either join to the function or use EXISTS.
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/
April 9, 2019 at 8:52 pm
How would you pull data from at least 4 different tables efficiently without using a UNION ALL?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 2:04 pm
I'm not sure if this helps (and it's not the prettiest thing) but here is a sample structure and some sample data to demonstrate what I'm trying to achieve. I know a couple of you have suggested UNION ALL. My original thought was to use that too, but I'm just not sure how readable that would be if I have anywhere from 1 to 50 values coming in from the parameters and therefore have to UNION ALL 50 queries...but maybe I'm just not thinking of it the right way (it's been a long week and it's only Wednesday...)
Thanks again,
Mike
IF OBJECT_ID('SPTest') IS NULL
EXEC ('CREATE PROCEDURE SPTest @Params VARCHAR(MAX) AS SELECT 1')
GO
ALTER PROCEDURE SPTest @Params VARCHAR(MAX)
AS
BEGIN
CREATE TABLE #ParamValues
(
RowNum INT,
Pvalue VARCHAR(100)
)
CREATE TABLE #Results
(
Pvalue VARCHAR(100),
Username VARCHAR(50)
)
INSERT INTO #ParamValues
(RowNum,
Pvalue)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
value
FROM STRING_SPLIT(@Params, ',');
DECLARE @i INT
SELECT @i = Count(Pvalue)
FROM #ParamValues
WHILE @i > 0
BEGIN
DECLARE @CurrentValue VARCHAR(100)
SELECT @CurrentValue = Pvalue
FROM #ParamValues
WHERE RowNum = @i
IF @CurrentValue = 'Test1'
BEGIN
INSERT INTO #Results
(Pvalue,
Username)
SELECT TOP 10 @CurrentValue,
CONVERT(varchar(255), Newid())
FROM dbo.syscolumns tb1,
dbo.syscolumns
END
IF @CurrentValue = 'Test2'
BEGIN
INSERT INTO #Results
(Pvalue,
Username)
SELECT TOP 15 @CurrentValue,
CONVERT(varchar(255), Newid())
FROM dbo.syscolumns tb1,
dbo.syscolumns
END
IF @CurrentValue = 'Test3'
BEGIN
INSERT INTO #Results
(Pvalue,
Username)
SELECT TOP 25 @CurrentValue,
CONVERT(varchar(255), Newid())
FROM dbo.syscolumns tb1,
dbo.syscolumns
END
IF @CurrentValue = 'Test4'
BEGIN
INSERT INTO #Results
(Pvalue,
Username)
SELECT TOP 20 @CurrentValue,
CONVERT(varchar(255), Newid())
FROM dbo.syscolumns tb1,
dbo.syscolumns
END
SET @i = @i - 1
END
SELECT *
FROM #Results
DROP TABLE #ParamValues
DROP TABLE #Results
END
GO
EXEC SPTest N'Test1,Test2,Test3,Someothervalue'
GOMike Scalise, PMP
https://www.michaelscalise.com
April 10, 2019 at 2:30 pm
Yes, I should have said "and without a loop".
For me, I'd use UNION ALL rather than a loop for something like this. SQL will have to parse all the SQL anyway.
Maybe which version of code is preferred is person by person. I could see some people thinking the loop was "clearer".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 2:59 pm
Scott,
Would you mind giving a simple example of how you would do this based on the values in the parameter and using UNION ALL?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2019 at 3:26 pm
Sure. Based on the originally posted query:
SELECT @currentValue, SomeField
FROM SomeTable
WHERE @currentValue = 'Test1'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AnotherTable
WHERE @currentValue = 'Test2'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AThirdTable
WHERE @currentValue = 'Test3'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AFourthTable
WHERE @currentValue = 'Test4'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2019 at 3:27 pm
Ah, got it. Thank you.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2019 at 3:31 pm
Actually, I take that back. You wouldn't have a @currentValue unless you used a loop.
It would seem to avoid the loop, the parameters would have to be parsed into their own table (as I have done in my example) and then used in an IN operator for each query in the UNION ALLs...
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
April 10, 2019 at 6:55 pm
Right, good point. I meant to do that. You just do an INNER JOIN to the table of allowed values, with a WHERE clause(s) if needed .
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 12, 2019 at 2:50 pm
That makes senses. Thanks again.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply