Using RAISERROR with Dynamic SQL

Question

Post reply

Using RAISERROR with Dynamic SQL

GBeezy

SSCarpal Tunnel

Points: 4717
More actions
June 28, 2013 at 11:16 am

#279361

Can anyone provide me a short example of how to implement this? I am not able to do this and need to.
Any and all help will be greatly appreciated!! Back to Google!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

you want to use raiseerror as a progress indicator from some dynamic SQL, is that it?

here's one example that would be appriate in a cursor loop, does this help?

--print error immediately in batch

declare @i int,

@err varchar(100)

--set @i=1

while 0=0

begin

SET @err = 'Progress So Far: Step ' + convert(varchar(30),ISNULL(@i,1)) + ' completed.'

raiserror (@err,0,1) with nowait

waitfor delay '00:00:02'

set @i=ISNULL(@i,1) + 1

end

RAISERROR ('Duplicate Records',16,1)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Or do you want to use RAISERROR with a message that contains a variable string?

Example:

SELECT @intCount = COUNT(*)

FROM table_name

WHERE code = @passed_code;

IF @intCount > 0

BEGIN

SET @strErr = 'Error: Parameter ' + @passed_code + ' not in table table_name';

RAISERROR(@strErr, 16, 1);

RETURN;

END;

We're just guessing at your intent here. If we've missed it, could you provide something else to go on?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

GBeezy SSCarpal Tunnel Points: 4717 More actions · Answer 3

Lowell and Ed,

Thanks for the replies, I will try to better explain what I am doing and what I am looking for.

What I am doing: I have cursor that is looping over all of the Linked Servers I have set up on my server and inserting into a local table on my server. I am using Dynamic SQL for my INSERT INTO statement.

What I am trying to do: If for some reason, there is an error within the INSERT, I want to use RAISERROR to return the error back to my SQL Agent job, close the cursor and deallocate the cursor.

My apologies as well for the late reply, I was out of town all weekend without much access to Internet. Please let me now if I am not doing a very good job of explaining myself.

Thanks Again for your help!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Could you please post your sql which builds dynamic SQL your INSERT INTO statement.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]