Debugging T-SQL

Question

Post reply

Debugging T-SQL

markjrouse

Mr or Mrs. 500

Points: 512
More actions
March 26, 2010 at 5:50 am

#213197

Ho do I step through or debug my T-SQL code. I'm trying to use a cursor to compare 5 fields from one table with five fields in another table and update a third table with the results.
The problem I have is that I get an invalid column name message and can' work out where it is going wrong.
It wuld be nice if I could step trhough my T-sql so I can see what is happening at each stage.

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

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

davidandrews13 SSCertifiable Points: 6689 More actions · Answer 1

i tend to put PRINT lines in my code to see what its returning.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

i guess you have stored proc which creating problem if yes then u can ignore it's above part

like

create proc sp_name ( id int , parma varchar(300))as

and use remaining part as a sql script so whenever you will execute it and find any error

by hitting on error(in result pane) it will highlight the culprit area.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

markjrouse Mr or Mrs. 500 Points: 512 More actions · Answer 3

When using a cursor, does it pull out the entire row, or can I tell it what fields to pull out from each row to populate my cursor variables?

I have created a temp table that pulls 5 fields from table A into it as "source" fields and has 5 additional empty "match fields" for populating later with a cursor.

I use the cursor to match these 5 "source" fields to table B and populate my temp table "match" fields were values match..

The problem I now have is that 4 of the 5 "match" fields ae correctly populated with the matched values, but one of the "match" fields populates values from my ID field in my temp table raher than the matched values from table B.

I can't figure out why the cursor would assign the value of my ID primary key field in my temp table to my first cursor varaible when I never make refer to ths ID field accept to create it at the beginning.

Are there some perculiar behaviours of cursor I should be aware of?

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

markjrouse (3/26/2010)
Are there some perculiar behaviours of cursor I should be aware of?

Please go thro the following article, especially the Remarks sections to understand the limitations of cursors...

http://msdn.microsoft.com/en-us/library/ms180169.aspx

also please post ur code, sample data , ur expected output, ur vlaues of input as stated in the following Jeff's article.. this will make our attempts easier

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

vijay.s SSCrazy Points: 2161 More actions · Answer 5

Kindly write down your code here with same script.

So it will provide you fast help

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

IIRC, you need the Enterprise version of SQL Server to use DEBUG and you need some seriously elevated privs even then. The detailed information you seek is too long to post here. I recommend that you check Books Online (the free help system that comes with SQL Server) for "Debug".

As a side bar and as some of the others have said, I wouldn't be looking to debug a cursor... I'd be looking to replace it with set based code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions