How to replace a string in stored procedure in sql server through query

Question

I have a procedure with following code

CREATE PROCEDURE customer_panel_insert(
    @Panel_Name VARCHAR(200),
    @Panel_description VARCHAR(5000),
    @Type_of_change  CHAR(5)) 
AS
   DECLARE @data_set VARCHAR(MAX);
   DECLARE @sql VARCHAR(MAX);

   begin
      set @sql = 'select * from customer_details where panel_type = 1 AND PANEL_DATATYPE = ''VARCHAR'''

      exec @sql 
   END

I want to change the varchar datatype to nvarchar and char to nchar. Change the varchar variable with size more than 4000 characters to NVARCHAR(MAX). I want to change with help of a query.

Is there any query that can replace

Replace String is different. What you are asking is how to change the datatype of a column in a table through query. — Atanu Roy
– Atanu Roy, Commented Apr 7, 2014 at 12:29
That is not the requirement. i want to change the procedures contents with nvarchar in place of varchar — Axs
– Axs, Commented Apr 7, 2014 at 12:29
possible duplicate of How to change column datatype in SQL Server database without losing data — Atanu Roy
– Atanu Roy, Commented Apr 7, 2014 at 12:30
Hi chandan, The change is to happen in the contents of stored procedure — Axs
– Axs, Commented Apr 7, 2014 at 12:31
Generate a alter script for the SP and do a quick replace [Ctrl+F] of all VARCHAR(MAX). Is it what you want to do? — Atanu Roy
– Atanu Roy, Commented Apr 7, 2014 at 12:34

Atanu Roy · Accepted Answer · 2014-04-07 13:34:47Z

-1

Hope this works for you. Be careful before executing it.

update sys.sql_modules set definition = REPLACE(definition,'VARCHAR(5000)','NVARCHAR(MAX)')
where definition like '%VARCHAR(5000)%'

edited Apr 7, 2014 at 13:34

answered Apr 7, 2014 at 12:43

Atanu Roy

1,3942 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Axs Over a year ago

what about changing VARCHAR(5000) to NVARCHAR(MAX) . i have around 11000 stored procedures , shall i have to do one by one

Axs Over a year ago

i have some variables whose length is between 4000 to 5000 characters . As nvarchar supports 4000 characters as constant size

Axs Over a year ago

Hello Sir, we want to convert the same through scripts as it is not possible to generate scripts for 11000 stored procedures through SSMS generate script option and it involves a lot of manual work to do so for the procedures

Collectives™ on Stack Overflow

How to replace a string in stored procedure in sql server through query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related