Get return value is sql runs correctly

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)

Get return value is sql runs correctly

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-09-02 : 08:13:45

What I need to do is get a return value from this sql Sp if it runs correctly back to my aspx page so that I can tell a user if the procedure ran correctly. I know how to return selects, ans such but how do i return a sql error???

ALTER Proc [dbo].[sp_UpdateMPDVMistake] (@SSN varchar(9), @EventId int)
AS
BEGIN

declare @execsql nvarchar (4000), @answer varchar(6), @dtlogged varchar(30), @strRemarks varchar(1000), @strLogged varchar(100),
@MPDV varchar(10), @QId int, @Datenow as varchar(35)

set @Datenow = cast(GETDATE() as varchar(max))

Declare MPDV Cursor FOR
Select ed.intquestionId,
q.intMPDVID,
cast(Case When ed.intAnswer = 0 THEN 'GO' WHEN ed.intAnswer = 1 THEN 'NO GO' ELSE 'NO GO' END as varchar(6)),
ed.dtLogged,
ed.strRemarks,
ed.strLogged
From tblSRPEventDataHistory as ed INNER JOIN
tblSRPQuestion as q on q.intQuestionId = ed.intQuestionId
Where q.intMPDVID IS NOT NULL AND intPersonnelID = @SSN AND
intSrpAttendId IN (Select intSrpAttendId from tblSRPAttendance where intEventId = @EventId)
Order by intAnswer
OPEN MPDV
FETCH Next From MPDV INTO @QId,@MPDV,@answer,@dtLogged,@strRemarks,@strLogged
WHILE @@FETCH_STATUS = 0
BEGIN
set @execsql = 'update OPENQUERY(RCASDBOR, ''Select STA, DT_TM_COMPL, NOTES, LAST_UPDT_NM, LAST_UPDT_DT FROM MOB.MOB_CHECKLIST
WHERE TASK_CTRL_SEQ_ID = '+ @MPDV + ' AND
UNIT_ID = '+ @SSN +''')
SET STA = ''' + @answer + ''',
DT_TM_COMPL = ''' + @dtLogged + ''',
NOTES = ''' + @strRemarks + ''',
LAST_UPDT_NM = ''' + @strLogged + ''',
LAST_UPDT_DT = '''+ @Datenow +''''

exec(@execsql)
FETCH Next From MPDV INTO @QId,@MPDV,@answer,@dtLogged,@strRemarks,@strLogged
END
Close MPDV
Deallocate MPDV
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 08:20:34

use an output variable and return through it.
alternatively you can use return clause if its an integer value

see

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources