Insert into some table in your dynamic sql statement with your @@error,
and then check your table after the dynamic sql executes..before
proceeding.

MJKulangara
http://sqladventures.blogspot.com

Look up sp_executesql in Books Online, and consider using it properly - i.e.
by using the 'params' parameter.

You could try something like this (untested, fill in the blanks):

declare @sql nvarchar(4000)
declare @params nvarchar(4000)

declare @myError int
declare @newfield2 ???

set @newTime = getdate()

set @sql = N'
set @myError = 0

update myTable
set field2 = @newfield2
where (table_key = <some value>)

set @myError = @@error'

set @params = N'
@myError int output
,@newfield2 ???'

exec sp_executesql
@stmt = @sql
,@params = @params
,@myError = @myError output
,@newTime = @newTime

if (@myError = 0)
begin
print 'no error'
end
else
begin
print @myError
end



ML

---

I have a t-sql stored procedure that looks like this:

-------------------------------------------------------------------------------------------------------------
insert into myTable field1,field2 values myUniqueValue1, myOtherInfo2

if @@error <> 0
begin

update myTable set field2 = myOtherInfo2 where field1=
myUniqueValue1
end
--------------------------------------------------------------------------------------------------------------
-- Field1 is a unique primary key


So it trys to insert a record but if there is a unique key failure
then the error is caught and the record is updated.

I'm quite happy with the way this operates since the @@error condition
is very infrequent

Now I need to make up the table name dynamically and therefore need to
create an sql string and execute it as follows

----------------------------------------------------------------------------------------------------------------

set @sql = 'insert into ' + @myDynamicTableName + ' field1,field2
values ' + @myUniqueValue1 +',' + myOtherInfo2
exec sp_excutesql @sql
---------------------------------------------------------------------------------------------------------------

The trouble is the @@error value is not valid after the sp_executesql
so I cannot then do the 'if @@error' part to update the record.

So the question I suppose is:

1. Is there a workaround for cases where the sql must be created
dynamically?

or

2. Is there a way of getting at the @@error within the SP?

or

3. A N other


Thanks, much appreciate all the help I get from this board

Bill

Bill (wje@blueyonder.co.uk) writes:
[quoted text, click to view]

Certainly, it is a lot cleaner to say:

IF NOT EXISTS (SELECT *
FROM myTable WHERE field1 = myUniqueValue)
INSERT ...
ELSE
UPDATE ....

Or even:

UPDATE myTable
SET ...
WHERE ...
IF @@rowcount = 0
INSERT myTable (...)


[quoted text, click to view]

The table name dynamically? For what reason? "Dynamic table name" is
a strong red-light indicator that you have a design flaw somewhere.
Either there is an error in the table design, or you should simply
write a new procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at