Dynamic stored procedure hangs, Select works fine

Question

Post reply

Dynamic stored procedure hangs, Select works fine

jena.wetzel

Old Hand

Points: 315
More actions
October 12, 2009 at 9:06 am

#385560

I have a dynamic stored procedure that will not return when you execute. It has one bigint parameter that sets up local variables based on the parameter value. If you run the select in a SQL window with the same parameter value, it returns.
Any idea why the select works but the exec does not return?

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

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

Steve Jones - SSC Editor SSC Guru Points: 740241 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 740241

October 12, 2009 at 9:14 am

#1064786

Are you sure it's the same code?

Can you post it?

Jack Corbett SSC Guru Points: 184397 More actions · Answer 2

Nope. Can you provide some code?

Are you getting the select by doing a PRINT @sqlstatement in the procedure?

You might want to try running profiler when you run the SP to see what is happening?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

jena.wetzel Old Hand Points: 315 More actions · Answer 3

I am running "exec spname" passing the int parameter, which does not return. The if I script the sp to a query window, note out the Create and declare the parameter and run it, it does return the expected data.

I have tried dropping and creating the procedure with no luck. WITH RECOMPILE does not work either. This procedure has ran in the past. I have seen this happen with more than 1 dynamic stored procedure. It hangs and does not return, but the next time it runs on a monthly basis, it returns the data and nothing in the procedure was changed.

Jack Corbett SSC Guru Points: 184397 More actions · Answer 4

Really can't answer the question without more information. Have you checked for blocking issues/deadlocks when the procedure hangs?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

jena.wetzel Old Hand Points: 315 More actions · Answer 5

I ran DBCC FREEPROCCACHE and the procedure runs now. I am guessing the procedure had a bad execution plan, but on some of the other procedures, the parameters don't vary.