x

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!

more ▼

asked Mar 09 at 03:37 PM in Default

avatar image

red68
498 11 16 25

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If there could be multiple values varying in numbers then you can use Table Valued Parameter 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+.

more ▼

answered Mar 09 at 04:09 PM

avatar image

Usman Butt
14.9k 6 13 21

Do you have an example of TVP used in an Update? Thx.

Mar 09 at 06:49 PM red68

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
Mar 09 at 07:39 PM Usman Butt

I did go through the link and the only piece that I was missing was the update piece where clause. Thanks!

Mar 09 at 07:50 PM red68

@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, second link.

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
Mar 09 at 08:10 PM Oleg

Oleg - to build on this, say I have 10 different type of excludes. According to specs, I can 1 up to all 10 of the excludes at any given time. Would you build 1 exclude stored procedure and run based on a parameter passed to it by user? Thx.

Mar 13 at 04:54 PM red68
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x428

asked: Mar 09 at 03:37 PM

Seen: 25 times

Last Updated: Mar 13 at 05:01 PM

Copyright 2018 Redgate Software. Privacy Policy