Complex Stored Procedure

Question

Complex Stored Procedure

Viewing 12 posts - 16 through 26 (of 26 total)

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

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 1

Hi Kathi again

I am trying to use the Debugger in SQL Query Analizer, however when i do Debug on a Stored Proc, the following error crops up:-

SP debugging may not work properly if you log on as ‘Local System account’ while SQL Server is configured to run as service. You can open Event Viewer to see details.

If i press Continue, i cannot manage to step through the code.

What shall I do to setup the debugger properly?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

No answer to the debug question (I don't use it - always use print statements as Kathy suggested), I just have one comment about the code (since we're in learning mode )

This syntax is 'incorrect' (it may work, but don't count on it)

IF @inputUsed <> ' ' or @inputUsed <> null

It should read like this:

IF @inputUsed <> ' ' or @inputUsed IS null

When comparing with null, you should never use anything else than IS NULL or IS NOT NULL. By using that syntax, you're sure that the statement will evaluate as expected regardless of ANSI settings both on server and client that lies out of your programamtic control.

/Kenneth

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 3

Hi Kenneth, first of all thanks for you suggestion........I will change all the code of the stored prroc.

however, as I said before, when i use the Print Statement, all I am getting is Return Code = 0, so I cannot check where the error is at.

I was going to use the debugger since in VS.NET, i really feel at ease using it and getting the desired results.

Thanks again!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Well, you haven't told us what the error is. It's quite possible that you never get to the printstatement as long as there is an error.

For debug purposes it's easier (imo) to open up the proc code in QA and run the statements one by one. By doing so you should be able to find where the error occurs. (though it would help to know what it is )

/Kenneth

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 5

Hey Kenneth you are perfectly right

The Print statement was not printing out since i was never getting to it, cause there was an error.

Now i am printing an if statement at a time

Thanks for your help!

Will keep u updated

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 6

Hello Again,

The Print works marvels! I really liked debugging this way. I almost got the Stored Proc to run. However I have an error here which I cannot solve :-

SET @strSQL = @strSQL + ' WHERE im_id LIKE %'+@inputSearchText+'%

im_name LIKE %'+@inputSearchText+'%

OR im_desc LIKE %'+@inputSearchText+'%'

When I input for example.........'male', it is not formatted in the right way. How can I format this piece of SQL?

Thanks for your help and time

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

have you tried?

SET @strSQL = @strSQL + ' WHERE im_id LIKE ''%'+@inputSearchText+'%''

im_name LIKE ''%'+@inputSearchText+'%''

OR im_desc LIKE ''%'+@inputSearchText+'%'''

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 8

Hi Remi, thanks, it worked like this:-

SET @strSQL = @strSQL + ' WHERE im_id LIKE ''%'+@inputSearchText+'%''

OR im_name LIKE ''%'+@inputSearchText+'%''

OR im_desc LIKE ''%'+@inputSearchText+'%'''

Thanks for your help

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 9

The problem that may arise when nesting in doublequotes and escaping quotes and whatnot, is that the code quickly becomes very hard to read (and therefore also to debug)

A 'trick' you can use with control-chars and such, is to assign them to a variable first.

So, it might look like this:

DECLARE @quote CHAR(1)

SET @quote = CHAR(39) --<== ASCII value of the single quote

SET @strSQL = @strSQL + ' WHERE im_id LIKE %' + @quote + @inputSearchText + @quote + '%' +

' OR im_name LIKE %' + @quote + @inputSearchText + @quote + '%' +

' OR im_desc LIKE %' + @quote + @inputSearchText + @quote + '%'

Also, not letting quoted strings span rows may help in reading and debugging.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Sorry, for jumping right in

SQL Server MVP Erland Sommarskog has two frequently referenced article which might also give some additional help in this case:

http://www.sommarskog.se/dyn-search.html

http://www.sommarskog.se/dynamic_sql.html

To escape this maze of ' marks you can also use QUOTENAME() or Erland's QUOTESTRING(), which should also make it more readable.

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

That's true - didn't think of that (tnx Frank )

To expand on the 'trick' I described (and where quotename() won't help) - another fairly common formatting issue:

DECLARE @crlf CHAR(2)

SELECT @crlf = CHAR(13) + CHAR(10) --<== ASCII value of CR + LF

PRINT 'A' + @crlf + 'B'

/Kenneth

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 12

Thanks Frank,

I will look into them after I finish my MCSD exams