How to check effect of insert statement during debugging of stored procedure

Question

How to check effect of insert statement during debugging of stored procedure

SAMDEV

SSC-Addicted

Points: 494
More actions
January 12, 2015 at 7:08 am

#293638

I am debugging a stored procedure. During this I am executing some insert statements on a table which are created from within stored procedure only.
Now, during my debugging as my cursor passes on one INSERT statement, I want to see if that INSERT statement has been successful or not. How to check it?
I tried by using SELECT statement during debug, it didn't work.
Any suggestion????
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SAMDEV (1/12/2015)
I am debugging a stored procedure. During this I am executing some insert statements on a table which are created from within stored procedure only.
Now, during my debugging as my cursor passes on one INSERT statement, I want to see if that INSERT statement has been successful or not. How to check it?
I tried by using SELECT statement during debug, it didn't work.
Any suggestion????
Thanks

Can we see your code please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SAMDEV SSC-Addicted Points: 494 More actions · Answer 2

Below is just a part of code

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

DECLARE @Rec int

DECLARE @FieldType2 int

DECLARE @FieldLabel2 VARCHAR(50)

DECLARE @Ctr int

DECLARE ExtraAux CURSOR FOR

SELECT FieldType, FieldLabel FROM Tab2Field WHERE BlockType = @BlockType AND FieldNumber NOT IN

(Select FieldNumber FROM Tab2Val WHERE Blocktype = @BlockType)

SET @Ctr = 0

OPEN ExtraAux

FETCH NEXT FROM ExtraAux INTO @FieldType2, @FieldLabel2 WHILE @@FETCH_STATUS = 0

BEGIN

@Rec = 2

INSERT INTO TempAux (TempRecordID,TempFieldType,TempFieldValue,TempFieldLabel) VALUES (@Rec,@FieldType2,'',@FieldLabel2)

SET @Ctr = @Ctr + 1

SET @AuxCount = @AuxCount + 1

FETCH NEXT FROM ExtraAux INTO @FieldType2, @FieldLabel2

END

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

Please concentrate on line displayed in bold is inserting records, which I want to see if successful or not

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Why not just remove the cursor? From what you posted there is no need for a cursor here at all. This insert process should be set based instead of row by row.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SAMDEV SSC-Addicted Points: 494 More actions · Answer 4

Actually i have hard-coded value for @Rec but it gets calculated for each record being inserted from source table into destination.

For this purpose I cannot directly insert values from source table into destination table.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SAMDEV (1/12/2015)
Actually i have hard-coded value for @Rec but it gets calculated for each record being inserted from source table into destination.
For this purpose I cannot directly insert values from source table into destination table.

@Rec = 2

will throw an error. Try SET @Rec = 2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SAMDEV (1/12/2015)
Actually i have hard-coded value for @Rec but it gets calculated for each record being inserted from source table into destination.
For this purpose I cannot directly insert values from source table into destination table.

Can you show the calculation?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

SAMDEV (1/12/2015)
Actually i have hard-coded value for @Rec but it gets calculated for each record being inserted from source table into destination.
For this purpose I cannot directly insert values from source table into destination table.

Huh? A calculation does not mean you can't do this without a cursor. Just do your calculation in a select statement.

INSERT INTO TempAux (TempRecordID,TempFieldType,TempFieldValue,TempFieldLabel)

SELECT SomeField * SomeOtherField + OrWhateverMakesUpYourCalculation

, FieldType

, ''

, FieldLabelFROM Tab2Field

WHERE BlockType = @BlockType

AND FieldNumber NOT IN

(

Select FieldNumber

FROM Tab2Val

WHERE Blocktype = @BlockType

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/