Removing Empty Space in Dynamic Query

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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, [e-mail]

Answer 4

@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.