question

red68 avatar image
red68 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!
sql-server-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
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
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

red68 avatar image red68 commented ·
Do you have an example of TVP used in an Update? Thx.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
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
0 Likes 0 ·
red68 avatar image red68 commented ·
I did go through the link and the only piece that I was missing was the update piece where clause. Thanks!
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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
[1]: https://ask.sqlservercentral.com/questions/8413/how-to-send-the-datatable-from-cnet-to-sqlserver-2.html [2]: https://ask.sqlservercentral.com/questions/8438/how-to-update-the-session-datatable-from-cnet-to-s.html
0 Likes 0 ·
red68 avatar image red68 commented ·
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.
0 Likes 0 ·
red68 avatar image red68 commented ·
Oleg - to build on this process. Say, I have 10 different type of excludes that can be run by user. The user can run at least 1 exclude all the way up to all 10 excludes. Would you build 1 source stored proc and then pass an exclude parameter of which excludes to run? 1st exclude is above "Out of Footprint", 2nd exclude would be based on a deliverabilityInd '1,2,3' and 3rd thru 10 excludes are totally different. If I combine all this in 1 stored proc and a user can any number of the excludes, how would I pass the parameters to make it work? Thx.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.