flexible update - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

flexible update

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-07-09 : 06:46:57

Hi,
I am writing an update stored proc as follows:

Question:
How should the set statement be whether or not there is a value?
For example, update only if @Address is not null or @contactID > 0
Thanks

create procedure sp

@ID int,
@Name varchar(20),
@address varchar(50) = null,
@contactID int = 0,
...
...

as

update tblMain
set

where
ID = @ID

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 07:02:25

I assume you want to update Address if @Address is not null, otherwise leave it alone. And the same for ID, if @ID is greater than 0. If that is true:

UPDATE tblMain
SET
	NameCol = @name,
	AddressCol = COALESCE(@address,AdressCol),
	ContactIdCol = CASE WHEN @ContactId > 0 then @ContactId else ContactIdCol END
WHERE
	ID = @Id;

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-09 : 07:17:03

Hi,
I beleive You can use the subquery and do,

Update Tablename
SET address= CASE WHEN ISNULL(A.Address,0)= 0 AND A.Contact > 1 THEN 'Required value' ELSE '' END
FROM
(SELECT Address,Contactid FROM Tablename
WHERE ... condition)A

I checked and hope u r looking for following condition
eg:-
SELECT CASE WHEN ISNULL(A.col,0)=0 AND A.no >1 THEN 'AA' END
FROM
(SELECT '' as 'col' ,2 as 'no')A

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-07-09 : 07:19:53

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources