?
Solved

sql selective updating

Posted on 2011-04-19
2
Medium Priority
?
280 Views
Last Modified: 2012-05-11
sql 2008 - how do I restrict updating of various columns within the same stored procedure instruction?

i.e. on the web-form, there are x4 check boxes ... the service user selects the fields they want to update ... but I wanted to only have a single stored procedure to handle all update options required.


USE [myDB]
GO
/****** Object:  StoredProcedure [dbo].[SP_AllocateVouchers]    Script Date: 04/19/2011 16:55:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_DistributorAllocateVouchers]

@BatchPrefix			bigint,
@DistributorTo_ID		int,
@ActionedByAgent_ID		int,
@ControlNoBegin			bigint,
@ControlNoEnd			bigint,
@WarehouseRef			nvarchar(128),
@ActionReason			nvarchar(MAX),
@Discount				decimal(18, 2),
@HTTPBrowserIPAddress	nvarchar(15),
@HTTPBrowserSessionID	nvarchar(50),
@CheckBox1                  int,    //1=true 0=false
@CheckBox2                  int,
@CheckBox3                  int,
@CheckBox4                  int

AS
BEGIN
		UPDATE Vouchers
		
		SET	Distributor_ID =@DistributorTo_ID,
			ActionedByAgent_ID =@ActionedByAgent_ID,
			WarehouseRef =@WarehouseRef,
	  		ActionReason =@ActionReason,
	  		Discount =@Discount,
	  		HTTPBrowserIPAddress=@HTTPBrowserIPAddress,
	  		HTTPBrowserSessionID=@HTTPBrowserSessionID
				
		WHERE [BatchCounter]
			BETWEEN @ControlNoBegin AND @ControlNoEnd AND (BatchPrefix =@BatchPrefix)
 
END

Open in new window

0
Comment
Question by:amillyard
2 Comments
 
LVL 7

Accepted Solution

by:
JuanCarniglia earned 2000 total points
ID: 35425935
You could split up your Procedure in four parts (using a switch) and each part updates specific fields.

In that case maybe you will have to use an extra parameter to tell the procedure which update to execute.

Greetings.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35426001

You can use dynamic sql to construct the query or something like
ALTER PROCEDURE [dbo].[SP_DistributorAllocateVouchers]

@BatchPrefix			bigint,
@DistributorTo_ID		int,
@ActionedByAgent_ID		int,
@ControlNoBegin			bigint,
@ControlNoEnd			bigint,
@WarehouseRef			nvarchar(128),
@ActionReason			nvarchar(MAX),
@Discount				decimal(18, 2),
@HTTPBrowserIPAddress	nvarchar(15),
@HTTPBrowserSessionID	nvarchar(50),
@CheckBox1                  int,    --1=true 0=false
@CheckBox2                  int,
@CheckBox3                  int,
@CheckBox4                  int

AS
BEGIN
		UPDATE Vouchers
		
		SET	Distributor_ID =@DistributorTo_ID,
			ActionedByAgent_ID =@ActionedByAgent_ID,
			WarehouseRef =@WarehouseRef,
	  		ActionReason = case @CheckBox1 when 1 then @ActionReason when 0 then ActionReason end,
	  		Discount = case @CheckBox2 when 1 then @Discount when 0 then Discount end,
	  		HTTPBrowserIPAddress= case @CheckBox3 when 1 then @HTTPBrowserIPAddress when 0 then HTTPBrowserIPAddress end,
	  		HTTPBrowserSessionID= case @CheckBox4 when 1 then @HTTPBrowserSessionID when 0 then @HTTPBrowserSessionID end
				
		WHERE [BatchCounter]
			BETWEEN @ControlNoBegin AND @ControlNoEnd AND (BatchPrefix =@BatchPrefix)
 
END

Open in new window

0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question