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

SQL Server Development (2000)

sql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-27 : 11:30:55

writes "On my asp page I stringed my t-sql instead of doing a stored procedure. It's like this:

if Request.Form("operation") = 2 then
sql = "BEGIN TRAN UPDATE My_Table1 SET "
sql = sql & "Table_1 = '" & persons_id & "', "
sql = sql & "Table_2 = NULLIF('" & strAddress & "', ''), "
sql = sql & "Table_3 = NULLIF('" & strSchool & "', ''), "
sql = sql & "Table_4 = NULLIF('" & strPhone & "', ''), "
sql = sql & "WHERE Table_1 = '" & persons_id & "' "
sql = sql & " IF @@ERROR <> 0 "
sql = sql & " RAISERROR(@@ERROR,16,1)
sql = sql & " ROLLBACK TRAN "
sql = sql & " ELSE "
sql = sql & " COMMIT TRAN "
set rs = cn.Execute(sql)
if Err.number <> 0 then
errorMsg = errorMsg & "<font size=5 color=red>A Database Error occured while updating a record.

"
errorMsg = errorMsg & " The Error Number is " & err.number & "

"
errorMsg = errorMsg & " The Error Description is " & err.description & "

"
errorMsg = errorMsg & "Please print this screen and contact the Help Desk.</font>"
errorMsg = errorMsg & "</center>
</center>
"
Response.Write errorMsg
set cn = nothing
set rs = nothing
Response.End ()
end if

My question is: Does the raiserror works in an asp environment or only inside a store procedure. Transactions and rollbacks are working fine. I just can't raise the error message. What can be suggested with this other than making a stored procedure as a last resort? Would this be more efficient and/or faster? Thanks..."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 12:38:16

raiserror shold work.
I usually

rollback tran
raiserror(...)
return

Yes a stored proc would be faster, more efficient and easier to maintain and test.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-27 : 16:16:07

And besides all that, @@ERROR has been reset to 0 by the time you call your RAISEERROR statement because @@ERROR only contains the condition of the LAST EXECUTED COMMAND, which in your case was the IF statement. You need to store the value of @@ERROR in another variable and then test and raise that other variable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources