June 30, 2014 at 12:51 pm
Below is sample SQL:
SQL_1: Creates a database
SQL_2: Creates 2 stored procedures
Now, I need to search database SP`s for Table2 t2 ON t2.CID = t1.CID and ALTER them with Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
SQL_1:
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Dev1')
DROP DATABASE [Dev1]
GO
CREATE DATABASE [Dev1]
GO
USE [Dev1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))
DROP TABLE [dbo].[Table1]
GO
CREATE TABLE Table1(CID INT, Dept VARCHAR(10))
INSERT INTO Table1(CID, Dept)
VALUES
(1, 'A'),
(2,'B')
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table2]') AND type in (N'U'))
DROP TABLE [dbo].[Table2]
GO
CREATE TABLE Table2(CID INT, CDate DATETIME)
INSERT INTO Table2(CID, CDate)
VALUES
(1,'2013-02-21 00:00:00.000'),
(1,'2014-05-29 00:00:00.000'),
(2, '2013-08-22 00:00:00.000'),
(2, '2014-06-21 00:00:00.000')
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table3]') AND type in (N'U'))
DROP TABLE [dbo].[Table3]
GO
CREATE TABLE Table3(CID INT, CustGroup VARCHAR(15))
INSERT INTO Table3(CID, CustGroup)
VALUES
(1,'Employees'),
(2,'Contractors')
SQL_2:
USE [Dev1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_StoredProcedure1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[up_StoredProcedure1]
GO
CREATE PROCEDURE dbo.up_StoredProcedure1
AS
SELECT t1.CID, t1.Dept, MIN(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_StoredProcedure2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[up_StoredProcedure2]
GO
CREATE PROCEDURE dbo.up_StoredProcedure2
AS
SELECT t1.CID, t1.Dept, MAX(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
Results...After adding the code the SPs should be:
--Stored Procedure 1
ALTER PROCEDURE dbo.up_StoredProcedure1
AS
SELECT t1.CID, t1.Dept, MIN(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
--Stored Procedure 2
ALTER PROCEDURE dbo.up_StoredProcedure2
AS
SELECT t1.CID, t1.Dept, MAX(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
June 30, 2014 at 1:36 pm
I must be missing something. What is the question or what do you need help with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 2:19 pm
I need to ALTER both the stored procs at the same time with below SQL.
Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
After ALTER the SPs code should be:
--Stored Procedure 1
ALTER PROCEDURE dbo.up_StoredProcedure1
AS
SELECT t1.CID, t1.Dept, MIN(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
--Stored Procedure 2
ALTER PROCEDURE dbo.up_StoredProcedure2
AS
SELECT t1.CID, t1.Dept, MAX(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
June 30, 2014 at 2:26 pm
etirem (6/30/2014)
I need to ALTER both the stored procs at the same time with below SQL.
Table2 t2 ON t2.CID = t1.CIDINNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
After ALTER the SPs code should be:
--Stored Procedure 1ALTER PROCEDURE dbo.up_StoredProcedure1
AS
SELECT t1.CID, t1.Dept, MIN(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
--Stored Procedure 2
ALTER PROCEDURE dbo.up_StoredProcedure2
AS
SELECT t1.CID, t1.Dept, MAX(t2.CDate) CDate
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.CID = t1.CID
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'
GROUP BY t1.CID, t1.Dept
ORDER BY t1.CID, t1.Dept
OK. So why can't you just run the code you posted?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 30, 2014 at 2:39 pm
It`s an example I posted here. I need to do it for multiple procedures.
June 30, 2014 at 2:47 pm
you will be much better off simply scripting out all your procedures, and doing a find and manual replace/edit.
any search is going to have the potential to fail, simply because of white space/coding standards.
these are all the same, but would not be found in your generic search you envision: spaces around equals signs, etc will stop any search unless you go through a million variations of cleanup.
Table2 t2 ON t2.CID = t1.CID
Table2 t2 ON t1.CID = t2.CID
Table2 ON Table2.CID = t1.CID
Table2 ON t1.CID = Table2 .CID
Table2
ON t1.CID = Table2 .CID
just find all procedures that reference the table in question via and manually review them.
select sed.referenced_schema_name, sed.referenced_entity_name, so.type_desc
from sys.sql_expression_dependencies sed
join sys.objects so on sed.referenced_id=so.object_id
WHERE referencing_id = OBJECT_ID(N'TargetTable');
Lowell
June 30, 2014 at 2:51 pm
Sorry having a dense moment. I finally understand what you are trying to do. I agree with Lowell. Not only are things like spacing a challenge you might have different aliases in different queries. Then comes the challenge of where do you insert your code? In the two examples you posted you have aggregate data in your query and no existing where clause. You need to deal with all those conditions because not all queries will be the same. Could you write some code to do this? Sure. However, I suspect it would take you at least twice as long as just manually editing your procedures. For a 1 time like this just find the procedures you need to modify and do it manually so you know it is correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply