October 17, 2017 at 9:55 am
Hello, I am working to create a Store Procedure that will need to accept parameters being passed by a web page. Based on the parameters based, my PROC needs to be able to return the desired results.
There are 3 scenarios to consider as described below in my notes and sample code. Can someone help me understand how I should code this in best practice?
-- Scenarios
-- 1. If I click 'All" the PROC will return all values in the list
-- 2. If I select "Atlanta" only, only "Altanta will return
-- 3. If I select Boston and New York, only Boston and New York will return
-- DROP TABLE #T
CREATE TABLE #T (LocID int, LocName varchar(25))
INSERT INTO #T VALUES (1,'Atlanta')
INSERT INTO #T VALUES (2,'Baltimore')
INSERT INTO #T VALUES (3,'Boston')
INSERT INTO #T VALUES (4,'Cleveland')
INSERT INTO #T VALUES (5,'Detriot')
INSERT INTO #T VALUES (6,'New York')
INSERT INTO #T VALUES (7,'Pittsburgh')
INSERT INTO #T VALUES (8,'San Diego')
INSERT INTO #T VALUES (9,'San Francisco')
INSERT INTO #T VALUES (10,'Seattle')
-- SELECT * FROM #T
October 17, 2017 at 10:32 am
One option is to pass an array in and then split it and search based on the values resulting from that.
But I'd probably use a table valued parameter.
So you'd create your new table typeCREATE TYPE LocList AS TABLE (LocName varchar(25))
Then you'd have your procedure (I changed #T to a permanent table still called T)CREATE PROCEDURE [dbo].[get_loc]
@loclist LocList READONLY
AS
SELECT LocName
FROM T
WHERE LocName IN (SELECT LocName FROM @loclist)
Testing it with the following would return a LocName of "Atlanta"DECLARE @loctemp LocList
INSERT INTO @loctemp VALUES ('Atlanta')
EXEC [get_loc] @loctemp
This would get you "Boston" and "New York"DECLARE @loctemp LocList
INSERT INTO @loctemp VALUES ('Boston'), ('New York')
EXEC [get_loc] @loctemp
For an ALL option you would pass the whole list in the parameter or maybe only pass the word "All" in the parameter and change the logic to check for that single value and return everything.
October 17, 2017 at 12:48 pm
Thank you very much!
October 19, 2017 at 7:05 am
SQLPirate - Tuesday, October 17, 2017 10:32 AMOne option is to pass an array in and then split it and search based on the values resulting from that.
But I'd probably use a table valued parameter.So you'd create your new table type
CREATE TYPE LocList AS TABLE (LocName varchar(25))Then you'd have your procedure (I changed #T to a permanent table still called T)
CREATE PROCEDURE [dbo].[get_loc]
@loclist LocList READONLY
ASSELECT LocName
FROM T
WHERE LocName IN (SELECT LocName FROM @loclist)Testing it with the following would return a LocName of "Atlanta"
DECLARE @loctemp LocList
INSERT INTO @loctemp VALUES ('Atlanta')EXEC [get_loc] @loctemp
This would get you "Boston" and "New York"
DECLARE @loctemp LocList
INSERT INTO @loctemp VALUES ('Boston'), ('New York')EXEC [get_loc] @loctemp
For an ALL option you would pass the whole list in the parameter or maybe only pass the word "All" in the parameter and change the logic to check for that single value and return everything.
Just be aware that while this works, it can throw the optimizer for a loop, because the table variable that results within the stored procedure will ALWAYS be assumed to have exactly 1 row by the optimizer. You would be better served performance wise by passing a delimited string and using Jeff Moden's DelimitedSplit8K inline table-valued function to split that string into a table of values within the stored procedure. Jeff's code comes at the end of an article that does a phenomenal job of explaining exactly how it works. The article can be found here and is well worth reading:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply