April 11, 2018 at 4:05 am
Hi SQLExperts,
Need some TSQL help.
Need an stored procedure which returns rows based on parameters. Stored procedure will have 3 parameters
PARAMETERS
===============
@BUList - comma separated values BU1,BU2
@Customerids - comma separated values 101,102,103
@Country_state_city - (Country:city1,city2 ; Country:city3,city4,city5)
example : US:Washington,Chicago,Los Angeles;Australia:Sydney
Format: semi colon is delimiter between countries. and to differentiate between country and cities , ":" is the delimter and cities are comma "," separated.
Now, for example when I run the stored procedure, based on above data it should return 3 rows and the dynamic query should look like below
SELECT loginid FROM [dbo].Customer
WHERE [BusinessUnit] IN ('BU1','BU2')
AND Customerid IN (101,102,103)
AND Country IN ('US','Australia')
AND City IN ('Washington','Chicago','Los Angeles','Sydney')
-- 3 rows should be returned
Table with dummy data
create table Customer
(CustomerID int identity(101,1) not null primary key,
Cname varchar(100),
loginid varchar(20),
BusinessUnit varchar(100),
Country varchar(100),
State varchar(100),
City varchar(100)
)
insert into Customer (cname,loginid,businessunit,country,state,City)
select 'Adam','l-adam','BU1','US','New Jersey','Washington'
union all
select 'Andrea','l-andy','BU2','US','California','Chicago'
union all
select 'Smith','l-Smith','BU2','US','Texas','Los Angeles'
union all
select 'Kaira','l-kra','BU3','Australia','Victoria','Sydney'
union all
select 'Krishna','l-krish','BU3','Australia','Tasmania','Melbourne'
go
Thanks,
Sam
April 11, 2018 at 4:29 am
Sam
You could use dynamic SQL for this, but unless you're very careful, you'd be vulnerable to SQL injection. Instead, I recommend that you use a splitter function to split the strings into their component elements and CROSS APPLY to that.
John
April 11, 2018 at 4:32 am
Hi Sam
Use DelimitedSplit8K to split the parameters out into rows in #temp tables, then join to those temp tables. Your query will also need OPTION(RECOMPILE) because of the wide range of inputs.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2018 at 7:56 am
vsamantha35 - Wednesday, April 11, 2018 4:05 AMHi SQLExperts,
Need some TSQL help.
Need an stored procedure which returns rows based on parameters. Stored procedure will have 3 parameters
PARAMETERS
===============
@BUList - comma separated values BU1,BU2
@Customerids - comma separated values 101,102,103
@Country_state_city - (Country:city1,city2 ; Country:city3,city4,city5)
example : US:Washington,Chicago,Los Angeles;Australia:Sydney
Format: semi colon is delimiter between countries. and to differentiate between country and cities , ":" is the delimter and cities are comma "," separated.Now, for example when I run the stored procedure, based on above data it should return 3 rows and the dynamic query should look like below
SELECT loginid FROM [dbo].Customer
WHERE [BusinessUnit] IN ('BU1','BU2')
AND Customerid IN (101,102,103)
AND Country IN ('US','Australia')
AND City IN ('Washington','Chicago','Los Angeles','Sydney')-- 3 rows should be returned
Table with dummy data
create table Customer
(CustomerID int identity(101,1) not null primary key,
Cname varchar(100),
loginid varchar(20),
BusinessUnit varchar(100),
Country varchar(100),
State varchar(100),
City varchar(100)
)insert into Customer (cname,loginid,businessunit,country,state,City)
select 'Adam','l-adam','BU1','US','New Jersey','Washington'
union all
select 'Andrea','l-andy','BU2','US','California','Chicago'
union all
select 'Smith','l-Smith','BU2','US','Texas','Los Angeles'
union all
select 'Kaira','l-kra','BU3','Australia','Victoria','Sydney'
union all
select 'Krishna','l-krish','BU3','Australia','Tasmania','Melbourne'
goThanks,
Sam
Just an fyi, but you don't have a state value as input in your list of values for the @Country_state_city parameter. As the US has the city of Springfield in MA, OH, and MO, at the very least, and possibly a number of other states, you probably need to cover that situation with your query, or risk getting mis-matches. Advice has already been provided about the use of Jeff Moden's DelimitedSplit8K function, and I would CROSS APPLY as needed to handle each parameter value (you'll need it twice to handle the @Country_state_city parameter, and possibly a 3rd time if you integrate a state value - and I'd probably leave that to a separate parameter, as thing get tricky when you try to combine 3 values into one parameter - but the more I think about it, you might need to create special handling if the STATE values are going to have to match a specific set of country and city combinations, so that will be "fun"...). In any case, you have your work cut out for you. Post back if you get stuck...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 13, 2018 at 1:36 pm
What is going to be executing this stored procedure? It first glance I thought you might have these lists generated by a multi-select parameter from SSRS, but judging by the @Country_state_city parameter, I have to assume that is not the case.
I’d recommend using table variables (if the process calling this stored procedure allows it) and passing the values in through table variables. Or you can use JSON and you can have your customer ID and whatever you need in there and query it almost like a table:
DECLARE @json AS NVARCHAR(MAX);
SET @json = N'[
{
"Address":
{
"City": "Los Angeles",
"State": "CA"
}
},
{
"Address":
{
"City": "New York",
"State": "NY"
}
},
{
"Address":
{
"City": "Chicago",
"State": "IL"
}
}]'
SELECT * FROM OPENJSON(@JSON)
WITH (
City VARCHAR(100) '$.Address.City',
State CHAR(2) '$.Address.State'
);
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply