Combine two Stored Procedure - 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 2005 Forums

Transact-SQL (2005)

Combine two Stored Procedure

Author

Topic

dand_dd
Starting Member

6 Posts

Posted - 2011-03-08 : 09:20:27

Hi

Can I combine this two stored procedures?

Procedure1 (works)

Update table_name set 
field1= case 
 When Datediff(d, time1, GETDATE() ) < 6 then '1'
 When Datediff(d, time1, GETDATE()) between 6 and 10 then '2'
 When Datediff(d,  time1, GETDATE()) > 10 then '3'	
 end,
 field2=  case 
  When Datediff(d, time1, GETDATE() ) < 6 then '1'
  When Datediff(d, time1, GETDATE()) between 6 and 10 then '2'
  When Datediff(d,  time1, GETDATE()) > 10 then '3'	
end,
field3=   case 
  When Datediff(d, time1, GETDATE() ) < 6 then '1'
  When Datediff(d, time1, GETDATE()) between 6 and 10 then '2'
  When Datediff(d,  time1, GETDATE()) > 10 then '3'	
	   end   
FROM table_name

Procedure2 (not working not corectly when are used multiple table fields)


UPDATE    table_name
    SET       
field1 = case When field1Status is Not NULL then '0' end,
field2 = case When field2Status is Not NULL then '0' end,
field3 = case When field3Status is Not NULL then '0' end
from table_name

Or how to write Procedure2 to become the request value?
if field1Status is NULL then update field1 with '0'

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 11:14:28

quote:

UPDATE table_name
SET
field1 = case When field1Status is Not NULL then '0' end,
field2 = case When field2Status is Not NULL then '0' end,
field3 = case When field3Status is Not NULL then '0' end
from table_name

quote:

Or how to write Procedure2 to become the request value?
if field1Status is NULL then update field1 with '0'

The two statements differ. The first update the value to 0 when field1Status is not null and in second statement you are requesting to update value to 0 when field1Status is null

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 11:15:45

Try this:

Update table_name set
field1= case
when field1Status is null then '0'
When Datediff(d, time1, GETDATE() ) < 6 then '1'
When Datediff(d, time1, GETDATE()) between 6 and 10 then '2'
When Datediff(d, time1, GETDATE()) > 10 then '3'
end,
field2= case
when field2Status is null then '0'
When Datediff(d, time1, GETDATE() ) < 6 then '1'
When Datediff(d, time1, GETDATE()) between 6 and 10 then '2'
When Datediff(d, time1, GETDATE()) > 10 then '3'
end,
field3= case
when field3Status is null then '0'
When Datediff(d, time1, GETDATE() ) < 6 then '1'
When Datediff(d, time1, GETDATE()) between 6 and 10 then '2'
When Datediff(d, time1, GETDATE()) > 10 then '3'
end
FROM table_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources