0

I have a stored procedure in SQL Server (using SSMS) with dynamic SQL. The input data is around millions of rows. Hence procedures takes up entire log space and return an error logspace filled. I tried separating the statements using batches inside dynamic SQL but it did not work.

Does anyone have a solution as how to split batches inside a stored procedure?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ss198811
  • 23
  • 3
  • 1
    The answer to "Does any one have.." is simple: YES. but if you need help with your SP then reading mind will not work well, and general stories does not help. It is more useful if you actually provide the relevant information like you SP and the table structure as well. In addition the server version can point us to give you solution that fit your server. – Ronen Ariely Aug 11 '18 at 08:10
  • I would like to know different methods to separate statement inside stored procedure into batches. Using 'Go' did not work. – ss198811 Aug 11 '18 at 08:15
  • 1
    "Go" has nothing to do with Transact SQL! This is a Batch separator in the client side, used by applications like SSMS. You can change the configuration of SSMS and choose any separator that you want. During April First several years ago I change the configuration on my team's machines and let them find the issue :-) – Ronen Ariely Aug 11 '18 at 08:37
  • @RonenAriely Hope they found out :) – JohnyL Aug 11 '18 at 08:47
  • Took some time @JohnyL :-) Anyhow, I did worse in April First... Let's see what I remember fast: Changed the Data file extension to ldf and the log file to ndf and the secondary file to mdf. People always mistakenly think that the extension in the file name has any meaning in SQL Server which is Wrong. One time I added TRIGGERs that rollback what they try to execute... and so on... NOT IN PRODUCTION if it was not clear :-) – Ronen Ariely Aug 11 '18 at 09:05
  • @RonenAriely Oh! I ***did*** think it was in production. ))) I guess there are lots of things to fool people) For one, you could set the white color of text in editor))) – JohnyL Aug 11 '18 at 09:14
  • One way to do it is using Cursor programming inside the SP. If you want to avoid this, read this answer: https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor – knb Aug 11 '18 at 10:06

0 Answers0