help me in this stored procedure issue please

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 2000 Forums

SQL Server Development (2000)

help me in this stored procedure issue please

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-09-16 : 10:11:36

here iam updating t_cbs_rqst_dtls table on the basis of data i got
from pstrTmpTbl(as input parameter).
here one issue facing this procedure will work when all the
data is there in pstrTmpTbl which i want to update in t_cbs_rqst_dtls table.
incase if all the data is not there in pstrTmpTbl table then it is not showing any error can u suggest me a way to solve this issue
iam sending entire code for that stored procedure as bellow
please copy this information to any notepad or querry analizer.
ok
i am waiting for ur answere
thanks
regards
dasu.g

ALTER PROCEDURE pr_cbs_upd_rsp_dtls @pstrTmpTbl VARCHAR(40),
@pintJobId INT,
@pintRqstStat INT,
@pintStatCd INT,
@pintStat INT OUTPUT
AS

/*

-- Declaring the local variables

DECLARE @strSqlUpd VARCHAR(1000),

@intDelErr INT,
@intUpdErr INT,
@intFail INT,
@intSuccess INT,
@strErrMsg VARCHAR(500),
@strErrCD VARCHAR(30),
@strMdulNm VARCHAR(30)

-- Initializtion of required local variables

SET @intSuccess =0
SET @intFail =-1
SET @strMdulNm ='pr_cbs_upd_rsp_dtls'

--updating status in t_cbs_req_dtls

SET @strSqlUpd=
'UPDATE
T_CBS_RQST_DTLS SET T_CBS_RQST_DTLS.stat_cd =
'+CAST (@pintStatCd AS VARCHAR(30))+' ,
T_CBS_RQST_DTLS.upd_job_id='+CAST ( @pintJobId AS VARCHAR(30))+'
FROM
T_CBS_RQST_DTLS ,'+ @pstrTmpTbl + '
WHERE

T_CBS_RQST_DTLS.acct_no = '+@pstrTmpTbl+'.rqst_acct_nm
AND
T_CBS_RQST_DTLS.val_dt = CAST ('+ @pstrTmpTbl+'.val_dt AS DATETIME)

AND
T_CBS_RQST_DTLS.rqst_cd='+CAST ( @pintRqstStat
AS VARCHAR(30))

EXEC (@strSqlUpd)
SET @intUpdErr=@@ERROR
IF (@intUpdErr<>0)
BEGIN

SET @pintStat=@intFail
SET @strErrMsg ='SQL ERROR NO IS '+ CAST (@intUpdErr AS VARCHAR(30))+'HAPPENED
IN pr_cbs_update_response_details STORED PROCEDURE '
SET @strErrCD='RECORD_NOT_FOUND_AT_DTLSTABLE'

EXEC pr_cbs_log_error @pintJobId,@strMdulNm,@strErrCD,@strErrMsg
RETURN
END

-- Deleting temptable data

IF (@intUpdErr=0)
EXEC pr_cbs_del_tmp @pstrTmpTbl ,@intDelErr OUTPUT

-- Prepare return variable

IF (@intDelErr=0)
BEGIN
SET @pintStat=@intSuccess
RETURN
END

ELSE
BEGIN
SET @pintStat=@intFail

SET @strErrMsg ='SQL ERROR NO IS '+ CAST (@intDelErr AS VARCHAR(30))+'HAPPENED
IN pr_cbs_update_response_details STORED PROCEDURE '
SET @strErrCD='DELETE_ERROR'

exec pr_cbs_log_error @pintJobId,@strMdulNm,@strErrCD,@strErrMsg

RETURN
END

-- End of the procedure

SqlStar
Posting Yak Master

121 Posts

Posted - 2004-09-21 : 07:13:06

Hi,

Its simple basic problem. When you concate any two strings,the both string should have value.In SQL server, when declare any string variable,its stroring NULL only.

Declare @a Varchar(10), @b varchar(15)

Set @a = 'I Likes'
Select @a + @b -------> Answer is "NULL"

Try like this

Set @a = 'I Likes'
Set @b = ''
Select @a + @b -------> Answer is "I Likes"

If you enter both value,

Set @a = 'I Like'
Set @b = 'Apple'
Select @a + @b -------> Answer is "I Likes Apple"

Try it...

:) While we stop to think, we often miss our opportunity :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources