Error context in Stored Procedures / SP Portability 1

[cbeggan]

Folks,

I am implementing some SQL processing which will be kicked off from a windows batch file. This file will use osql to call an SP which contains nested SPs. (The batch file also calls some windows applications afterwards). The SPs will result in the creation of acsii output files via bcp.

I have two question in this post a/ on error contexts and b/ on hardcoding server names in SP sqls.

---
a/
I'm using the 'standard' IF @@error = 0 error checking in my top level SP. This seems to be the only way (as RAISERROR will cause the top-level osql command to quit I think). However, if I hit an error in my lower SPs I want to know where it occurred.

So.. is there a way of capturing the error context (as is printed interactively (e.g. Server: Msg 137, Level 15, State 2, Procedure testprocedure, Line 3 Must declare the variable '@foobar'.).

I'm planning to write error messages to an error table which will be dumped to file at the end of processing. (Users can acces the file, but cannot see the DB).
---
b/
Also, I'd rather not hardcode the server/db/user/password info into my osql call or inside the SQLs in my stored proc. I intended to pass them as DOS parameters, but it seems I cannot use variables for these within the stored procedures without using messy (and less maintainable) dynamic sqls. Is this true? And anyone got a neat solution ?

Thanks in advance!

 

[JohnDTampaBay]

a/ Re: Error contexts from BOL:

@@ERROR is the only part of a Microsoft® SQL Server™ 2000 error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings such as object names, are returned only to the application in which they can be processed using the API error handling mechanisms.

It looks like SQL can only capture @@Error.

b/ Re: Hardcoding server names: I ran across

xp_sprintf

and

xp_sscanf

that seem like they will come in handy for dynamic SQL. These functions allow you to stuff or parse a string given a format string. Check out this example.

DECLARE @sql varchar(1000),
  @table varchar(50),
  @column varchar(50)

SELECT @sql = 'SELECT %s FROM %s',
  @table = 'ThisTable',
  @column = 'ThisColumn'

EXEC master.dbo.xp_sprintf @sql OUTPUT,@sql,@table,@column

PRINT @sql

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected

 

[JohnDTampaBay]

Duh. The result from the PRINT statement should be

SELECT ThisTable FROM ThisColumn

So I got the variables reversed, but you get the idea of how the string is stuffed with the variable contents at the %s placeholders. Maybe this approach will make dynamic SQL a bit easier to maintain. --Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected

 

[cbeggan]

Thanks for that. As I see I don't need to use +'s to assign a multi-line string to a variable (only change required to the SQLs is the use of twin single quotes), this may be the way forward...

------
Dublin, Ireland.