Software

Ask something
Question Locked

SQL Help

0 helpful
2 replies

by evga

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have am having a problem writing a store procedure to update a specific record

Below is the record

" Goal:999999999 Recovery Needs:asdfewwfwe Referrals for Additional Services:dfasdfasdf"

As you can see there is three parts to the record

1. Goal:
2. Recovery Needs:
3. Referral for additional
Services:

I wrote the store proc and it works. The problem I have is If the Goal part is left "null" I don't want to erase what is there already in the record. So I figure adding a if then statement to subquery the Goal part and leave it how it this should work, problem is I can't figure how to do this.

SQL 2005

2 replies

by evga

If anyone looked at this I figured it out please note the part ********

USE [WFS_PRODUCTION]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[TP_MTP_ProblemEdit](
@TierUserID int,
@TierDocid int,
@TierFolderID int,
@IntakeKey int,
@ClientKey int,
@Goal Varchar(250),
@EditID int,
@DateMod datetime,
@Recneed Varchar (250),
@Refser Varchar (250)

)

AS
Declare @GoalV VARCHAR(100)
SET @GoalV = (Select PLanv From MTP_PLan Where op__docid=@EditID and LnType='G')

if @Goal is null
Begin

Update MTP_Plan

********Set PlanV= + (SELECT SUBSTRING(@GoalV, 1, CHARINDEX('Recovery', @GoalV) - 1) AS [Goal])+' Recovery Needs:'+ isnull(convert(varchar(254),@Recneed,101),'')+' Referrals for Additional Services:'+ isnull(convert(varchar(254),@Refser,101),'')*********

,DateMod=@DateMod
,CurrentStatus='Active'
,CurrentStatusDate=GetDate()
Where op__docid=@EditID and LnType='G'
Return
End

Update MTP_Plan
Set PlanV=' Goal:'+ isnull(convert(varchar(254),@Goal,101),'')+' Recovery Needs:'+ isnull(convert(varchar(254),@Recneed,101),'')+' Referrals for Additional Services:'+ isnull(convert(varchar(254),@Refser,101),'')
,DateMod=@DateMod
,CurrentStatus='Active'
,CurrentStatusDate=GetDate()
Where op__docid=@EditID and LnType='G'

This discussion is closed to new replies.