Seeing the source of dynamic stored procedure 1

[hawkpie]

Hi Everyone

I have a beefy stored procudure which takes in 6 parameters and then does a bit of logic on the where clause. However, there is a problem with the results that are returned.

Is there anyway I can see the final SQL code that is generated from this logic, so I can see what is going wrong.

Cheers
pie

 

[SQLDenis]

yes you can print it
instead of exec (@SQL)
use print (@SQL)


You can also capture the statement(s) with profiler

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[hawkpie]

cheers SQLDenis, but where do I do this?

do I change this at the bottom of my stored procedure? but when I call it from my code (which pass parameters in) where does it get printed out to?

my exec code at the mo is:
EXEC sp_executesql @SQLString,
@strParams,
@address,
@minprice,
@maxprice,
@bedrooms,
@lettings

and each one of those bits is part of the dynamic where clause.

cheers
pie

 

[SQLDenis]

print @SQLString + convert(varchar,@strParams) +
convert(varchar,@address) +
convert(varchar,@minprice) +
convert(varchar,@maxprice) +
convert(varchar,@bedrooms) +
convert(varchar,@lettings)

This will be printed in your messages window in Query Analyzer if you run it from QA

You can also change the print to select like this
select @SQLString + convert(varchar,@strParams) +
convert(varchar,@address) +
convert(varchar,@minprice) +
convert(varchar,@maxprice) +
convert(varchar,@bedrooms) +
convert(varchar,@lettings)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[hawkpie]

cheers for the info. will give that a try.

you mentioned using the profiler too.

do you know what fields/columns I would need to add to see the output SQL.

(sorry to be a pain)

cheers
pie

 

[SQLDenis]

events/T-SQL/SQL:StmtCompleted

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[hawkpie]

that's cool for the event, but which column do i need to add to be able to see the sql output.

ta for the info.

pie

 

[SQLDenis]

TextData

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[hawkpie]

cheers SQLDenis

you're an absolute star. thanks for bearing with me.

pie