Issue with the T-SQL Replace function

Question

I'm currently facing a strange situation.

I'm collecting code of existing stored procedures from a query to a TMP table.

TABLE:

##SPListAndCode
(
    Code nVarchar(MAX)
)

Query:

INSERT INTO ##SPListAndCode
    SELECT OBJECT_DEFINITION (OBJECT_ID('SPname')))

After that I am trying to replace values to get from Create query, Alter query

REPLACE(CODE, 'CREATE PROCEDURE', 'ALTER PROCEDURE')

But problem is this: REPLACE function is not replacing values.

But, when I am trying to use

REPLACE(CODE, 'CREATE', 'ALTER')

function works as expected.

But this scenario are not acceptable for me, because inside the stored procedure there can be things like

CREATE TABLE

Example data inside "Code" column:

/****** Object:  StoredProcedure dbo.spName    Script Date: 6/20/2016 9:10:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE dbo.spName
AS
DECLARE  @pStartDate  date, @x int


SET @pStartDate  = (SELECT max(CT_ACTIVITY_DATE) FROM Table)

...

Thanks a lot in advance for any kind of support!

REPLACE should had done the trick. But to debug more could you please show us the code - 1. where you are populating ##SPListAndCode and 2. your query to REPLACE string/keywords — Abhishek
– Abhishek, Commented Sep 21, 2016 at 13:09
Change your replace to the first one you posted. And be careful here....this is a global temp table which means that any connection can see it and modify it. If you have two people running this at the same time you have exposed yourself to concurrency issues. — Sean Lange
– Sean Lange, Commented Sep 21, 2016 at 13:57

user6691848 · Accepted Answer · 2016-09-21 16:13:24Z

1

Your stored procedure has two spaces between CREATE and PROCEDURE, while your replace is looking for the string with a single space between the words.

answered Sep 21, 2016 at 16:13

user6691848

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

1 Comment

Firmack Bendera Over a year ago

Thank MATE ! this was an issue Double SPACE

marc_s · Accepted Answer · 2016-09-21 15:38:21Z

0

To gain access to the actual code contained inside of the stored procedures, you can use something like this:

SELECT 
    so.name [ObjectName], so.type, 
    OBJECT_NAME(sc.id), sc.id, sc.colid , sc.[text]
FROM 
    sys.syscomments sc
INNER JOIN 
    sys.sysobjects so ON so.id = sc.id
WHERE 
    so.type = 'P'
ORDER BY 
    sc.id, sc.colid

Note there can be multiple entries for each object, and the colid is used to order those entries.

edited Sep 21, 2016 at 15:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 21, 2016 at 13:08

LogicalMan

3842 silver badges7 bronze badges

1 Comment

marc_s Over a year ago

Or you could use the sys.procedures catalog view (instead of sys.sysobjects) and then you don't need the WHERE so.type = 'P' clause anymore...

Collectives™ on Stack Overflow

Issue with the T-SQL Replace function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related