Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Removing Empty Space in Dynamic Query

Proposed Answer Removing Empty Space in Dynamic Query

  • Friday, March 08, 2013 12:41 PM
     
     
    Sign In to Vote
    0

    Hi,

    I'm using Dynamic Queries in some Stored Procedures. When I'm trying to get the Procedure Definition with below command,

    sp_helptext Procedure_Name

    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.

    Thank u

    Regards,

    Sanjay

     

All Replies

  • Friday, March 08, 2013 12:46 PM
     
     Proposed Answer
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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'
    PRINT @sql

    If you need further help, you need to post your code.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Saturday, March 09, 2013 5:49 AM
     
     
    Sign In to Vote
    1

    @Sanjay,

    This may not be an exact solution requested, but helps in formatting SQL > http://www.dpriver.com/pp/sqlformat.htm

    Change the 'Max length per line in compact mode:' from 80 to 8000 or some large number,this is just avoid truncation of SQL command.

    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.