Friday, March 08, 2013 12:41 PM
I'm using Dynamic Queries in some Stored Procedures. When I'm trying to get the Procedure Definition with below command,
some lines of Query is breaking in the Middle of the line and some empty space automatically adding in the Query. so when i m executing the procedure its getting error. Again i m manually removing the lines and empty space.
I'm not understanding what is the problem.
Can anybody help me out in this regard.
Friday, March 08, 2013 12:46 PM
You can get it in format using two ways :
1) Go to database >> programmability >> stored procedure >> right click on it and go to filter >> write your SP name
2) Use Result in Text view instead grid view.You can set it by cntrl + T and then execute sp_helptext procedure.
Please vote if you find this posting was helpful or Mark it as answered.
- Proposed As Answer by Dineshkumar Friday, March 08, 2013 12:57 PM
Friday, March 08, 2013 1:02 PM
Thanks for ur reply.
I tried 2nd method but i'm getting same problem.
Is there any restriction is there to write a limited character in one line, and when it exeeds the limit its breaking the like to next line.
and Here i'm getting some white space also.
Friday, March 08, 2013 1:54 PM
The broken lines are an artefact of sp_helptext and has nothing to do with your query.
If you write dynamic SQL, you need to engage a good amount of discipline to make your code readable, and using parameterised SQL is a must. That usually helps produce a working SQL text. It is a good idea to include line breaks in the generate SQL, to make easier to read, like this:
SELECT @sql =
' SELECT col2, col2, col3 '
FROM ' + quotename(@tbl) + '
WHERE ' + quotename(@col) + ' @val'
If you need further help, you need to post your code.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
Saturday, March 09, 2013 5:49 AM
This may not be an exact solution requested, but helps in formatting SQL > http://www.dpriver.com/pp/sqlformat.htm
Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.
- Edited by Sarat Babu (SS) Saturday, March 09, 2013 5:53 AM