Two Stored Procs doing similar thing merge into on

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Two Stored Procs doing similar thing merge into on

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-07 : 08:41:13

I have a stored procedure which looks at distinct Class of business and gives you all distinct values depending on YOA, a report uses this.

I have another one which looks at I have a stored procedure which looks at distinct Class of business and gives you all distinct values depending on start and enddate of inception, a second report uses this.

I've been told to save on support i should merge these into one BUT i dont see how or why?!

Any guidence would be greatly appreciated as getting very stressed, as I dont see why if they work fine seperately I cant keep them like that - my manager said I cant! :(

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-07 : 08:46:06

>> depending on start and enddate of inception
How does that work.

could probably create a single SP and pass in a parameter for the call type.
Use that in the where clause to restrict the resultset.

where (getdate() bewteen dte1 and dte2 or @CallType = 'All')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-07 : 08:49:20

basically its same exact select clause and joins but one has a where statement

WHERE
drh.CurrentFlag = 1
AND drh.[status] IN ('Cancelled', 'Live')
AND LEFT(drh.PolicyRef,4) NOT IN ('PACK', 'PROG')
AND drh.YOA IN (SELECT LTRIM(ListValue) FROM dbo.udf_ListToStringArray(@YOA))

Second one has

WHERE
drh.CurrentFlag = 1
AND drh.status IN ('live', 'cancelled')
AND left(drh.PolicyRef,4) not in ('PACK', 'PROG')
AND drh.InceptionDate between @StartDate and @EndDate

realyl struggling cos for I dont want to change the underlying report for the 1st one to incorproate any change into 2nd one , i dont understand why using two is an issue but it is, can anyone explain why as well?

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-07 : 08:50:17

As you can see its just the last line causing issue of both - so frustrating! :(

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-07 : 09:05:02

WHERE
drh.CurrentFlag = 1
AND drh.[status] IN ('Cancelled', 'Live')
AND LEFT(drh.PolicyRef,4) NOT IN ('PACK', 'PROG')
AND ( ((drh.YOA IN (SELECT LTRIM(ListValue) FROM dbo.udf_ListToStringArray(@YOA)) and @CallType = 'List')
or ((drh.InceptionDate between @StartDate and @EndDate and @CallType = 'dates')
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 09:53:50

Try to create dynamic store proceduer in which you will create a sql query dynamicaly then execute using sp_execuptesql

See sample below which get data from a table where criteria is dynamic
ALTER PROCEDURE [GetAll]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from temptable'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 10:08:10

quote:
Originally posted by jassi.singh

Try to create dynamic store proceduer in which you will create a sql query dynamicaly then execute using sp_execuptesql

See sample below which get data from a table where criteria is dynamic
ALTER PROCEDURE [GetAll]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from temptable'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

isnt this same suggestion you gave for earlier post too?
its indeed a xerox bot i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources