1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Axs
  • 755
  • 2
  • 14
  • 26
  • Replace String is different. What you are asking is how to change the datatype of a column in a table through query. – Atanu Roy Apr 07 '14 at 12:29
  • That is not the requirement. i want to change the procedures contents with nvarchar in place of varchar – Axs Apr 07 '14 at 12:29
  • possible duplicate of [How to change column datatype in SQL Server database without losing data](http://stackoverflow.com/questions/5136013/how-to-change-column-datatype-in-sql-server-database-without-losing-data) – Atanu Roy Apr 07 '14 at 12:30
  • Hi chandan, The change is to happen in the contents of stored procedure – Axs Apr 07 '14 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 Apr 07 '14 at 12:34
  • So you're asking how to alter the stored procedure itself? – alroc Apr 07 '14 at 12:34
  • i have a thousands of stored procedures in the database where i have to change all varchar to nvarchar and char to nchar in stored procedures only. i need to loop through the sys.procedures and replace the datatypes with help of query – Axs Apr 07 '14 at 12:36
  • Please mention your last comment as an update to your main question. This is exactly what makes your question unique! – Atanu Roy Apr 07 '14 at 12:36
  • no change of schema is required but the procedure contents should be modified – Axs Apr 07 '14 at 12:38

1 Answers1

-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)%'
Atanu Roy
  • 1,384
  • 2
  • 17
  • 29
  • what about changing VARCHAR(5000) to NVARCHAR(MAX) . i have around 11000 stored procedures , shall i have to do one by one – Axs Apr 07 '14 at 13:04
  • i have some variables whose length is between 4000 to 5000 characters . As nvarchar supports 4000 characters as constant size – Axs Apr 07 '14 at 13:05
  • 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 – Axs Apr 07 '14 at 13:18