Update stored procedure based on conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update stored procedure based on conditions

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-25 : 08:49:09

I have three dropdown lists Dept Div and Sect. If any one, two or three of them has a value I want to update the value in my table to this new value. If the dropdownlist has no value selected I want the field in the table to be left with its origianl value.

How can I do this in an sp ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-25 : 09:23:03

Create a stored proc with these three as parameters and a fourth one to identify the row in the table that needs to be updated. Then, update only if the value passed in is not null. Something like this:

CREATE PROCEDURE dbo.YourUpdateProc
	@Dep VARCHAR(32) = NULL,
	@Div VARCHAR(32) = NULL,
	@Sec VARCHAR(32) = NULL,
	@RowIdentifier INT
AS
	UPDATE YourTable SET
		Dep = COALESCE(NULLIF(@Dep,''),Dep),
		Div = COALESCE(NULLIF(@Div,''),Div),
		Sec = COALESCE(NULLIF(@Sec,''),Sec)
	WHERE
		@RowIdentifier = RowIdentifier;
GO

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-26 : 09:57:30

Thank you :-)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-26 : 10:42:39

I have tried this out now and the problem is if my original record shows

Dep "Central"
Div "Gardens and Parks"
Sect "Flower beds"

and in my ddls where I enter the new vales I just enter as below leaving Dep and Sect blank my resulting record is update to that exactly, removing Central and Flower beds. I want to leave the original values if there is no value selected in the dropdown

Dep
Div "Outdoor areas"
Sect

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-26 : 11:40:58

Can you run the stored procedure from SSMS like shown below and see if that works correctly?

EXEC dbo.YourUpdateProc 
	@Dep  = NULL, 
	@Div  = 'Outdoor areas',
	@Sec = NULL,
	@RowIdentifier=23 -- or whatever rowidentifier should be

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-27 : 05:45:26

Nothing changes if I do that - here's my sp. To get it to work you have to have all the parameters matching in the selection criteria and then in the new values either select the same if you want it to stay the same or change it to a new value. What I want is if a value is selected as a parameter it doesn't matter what the other values are it ignores those and when new values are selected it only changes them if a new value is chosen

ALTER PROCEDURE [dbo].[sp_BlockUpdate]
-- Add the parameters for the stored procedure here

@strDep nvarchar(100)= null,
@strDiv nvarchar(100)= null,
@strSect nvarchar(100) = null,
@strDepNew nvarchar(100)= null,
@strDivNew nvarchar(100)= null,
@strSectNew nvarchar(100) = null

AS
BEGIN
UPDATE MyTable SET

Dep = COALESCE(NULLIF(@strDepNew,''),@strDepNew),
Div = COALESCE(NULLIF(@strDivNew,''),@strDivNew),
Sect = COALESCE(NULLIF(@strSectNew,''),@strSectNew)

WHERE (Dep = @strDep or @strDep = '') and
(Div = @strDiv or @strDiv = '') and
(Sect = @strSect or @strSect = '')
and Destroyed = 0
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-27 : 10:12:43

[code]ALTER PROCEDURE [dbo].[sp_BlockUpdate]
-- Add the parameters for the stored procedure here

@strDep nvarchar(100)= null,
@strDiv nvarchar(100)= null,
@strSect nvarchar(100) = null,
@strDepNew nvarchar(100)= null,
@strDivNew nvarchar(100)= null,
@strSectNew nvarchar(100) = null

AS
BEGIN
UPDATE MyTable SET

Dep = COALESCE(NULLIF(@strDepNew,''),~~@strDepNew~~ Dep ),
Div = COALESCE(NULLIF(@strDivNew,''),~~@strDivNew~~ Div ),
Sect = COALESCE(NULLIF(@strSectNew,''),~~@strSectNew~~ Sect )

WHERE (Dep = @strDep or @strDep = '') and
(Div = @strDiv or @strDiv = '') and
(Sect = @strSect or @strSect = '')
and Destroyed = 0
END
[/code]

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-04-30 : 10:33:47

Nearly there....
If I have this as my selection ddls

Dep "Central"
Div "Gardens and Parks"
Sect "Flower beds"

and want to remove Flower Beds so the record looks like this
Dep "Central"
Div "Gardens and Parks"
Sect

If I have 'update to' ddls as above, nothing happens. Sect isn't changed to nothing even though my third ddl is blank

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-30 : 14:15:50

quote:
Originally posted by Pinto

Nearly there....
If I have this as my selection ddls

Dep "Central"
Div "Gardens and Parks"
Sect "Flower beds"

and want to remove Flower Beds so the record looks like this
Dep "Central"
Div "Gardens and Parks"
Sect

If I have 'update to' ddls as above, nothing happens. Sect isn't changed to nothing even though my third ddl is blank

Isn't this contrary to what you wanted to do as you described in your post of - 04/26/2012 : 10:42:39, where you wanted to leave it alone if you send in a blank/null?

One possibility is that you can decide that if you send a null, you will leave it unchanged and if you send in an empty string, you will blank out the existing value.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-05-01 : 06:19:06

I think you are right - I am confusing myself.You have correctly summed up what I want to do below.

One possibility is that you can decide that if you send a null, you will leave it unchanged and if you send in an empty string, you will blank out the existing value.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 06:59:32

quote:
Originally posted by Pinto

I think you are right - I am confusing myself.You have correctly summed up what I want to do below.

One possibility is that you can decide that if you send a null, you will leave it unchanged and if you send in an empty string, you will blank out the existing value.

You can do that by changing the update to this:

....
UPDATE MyTable SET

Dep = COALESCE(@strDepNew,Dep ),
Div = COALESCE(@strDivNew,Div ),
Sect = COALESCE(@strSectNew,Sect )
.....

I don't understand the purpose of the where clause and the reason for sending in @strDep,@strDiv and @strSect.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2012-05-01 : 07:03:09

If a user only enters only one parameter for selection then it could in result in many thousands of records - 200,000 possibly. I want them where possible to filter the records to a lesser number if at all possible.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-01 : 09:34:33

You can either return a set maximum number of records, or you can return an error and make them add more filtering criteria.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources