sql selective updating

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

amillyardAsked:
Who is Participating?
 
JuanCarnigliaConnect With a Mentor Commented:
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
 
Ephraim WangoyaCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.