asked
Repeatable Code
I have to exclude data based on state which is different from one client to another. Trying to determine the most efficient and fastest way to do this if "state" changes from time to time but the underlying code stays the same. Ex of update statement. Update Table1 Set Exclude = 'Out of Footprint' Where State in ('TX','MS','TN') --the values here can change from client to client. Would you pass state values as a parameter to a stored proc or create a function to do it or take another approach? Thanks!
If there could be multiple values varying in numbers then you can use [Table Valued Parameter][1] passed to the update procedure. There are other options possible as well depending upon the situation but this is what I generally prefer in SQL 2012+. [1]:
https://technet.microsoft.com/en-us/library/bb510489(v=sql.110).aspx
I hope you have gone through the above mentioned BOL link. If not, then please read it. Anyhow the following example should be easy to understand use tempdb go create table Table1 ( State varchar(10), Exclude varchar(20) ) go create type dbo.StateTVP as table ( State varchar(10) ) go insert dbo.Table1 select 'TX', '' union all select 'MS', '' union all select 'TN', '' declare @StateTVP AS dbo.StateTVP; insert @StateTVP select 'TX' union all select 'MS' update dbo.Table1 set Exclude = 'Out of Footprint' where State in (select State from @StateTVP) select * from dbo.Table1
@red68 Here are the links to the questions asked on this site and answered about 7 years ago, but the idea is the same: [first link][1], [second link][2]. The other option (instead of using TVP) would be to create a proc which takes a comma-delimited list of states to update. The procedure's script is static, the list is usually pretty small, so it should work OK:
create proc dbo.usp_UpdateExclude (@list varchar(150))
as
begin
set nocount on;
declare @xml xml = '<r>' + replace(@list, ',', '</r><r>') + '</r>';
update dbo.Table1
set
Exclude = 'Out of Footprint'
where [State] in (
select
r.x.value('.[1]', 'char(2)')
from @xml.nodes('r') r(x)
)
set nocount off;
end;
go
-- example usage:
exec dbo.usp_UpdateExclude 'MS,TN,TX'; -- this will update 3 rows
18 People are following this question.
