February 21, 2013 at 9:05 pm
I have two stored procedures with the same parameters : From Date & To Date.
SP#1 - uses birth date
SP#2 - uses hire date
Both stored procedures return the same exact columns.
I would like to consolidate this into just one stored procedure with an additional parameter, let's say @DateType. If user selects 1, then the stored procedure runs with birth date in the WHERE clause. If the user selects 2, then the stored procedure uses hire date in the WHERE clause.
Is this possible? If so, could someone help me out with a small sample?
Thank you in advance.
February 21, 2013 at 10:23 pm
If you're just looking for a single SP to access the same data I'd just setup a 3rd SP to call one or the other...
if your looking for just 1 sp the easiest would be to just replace the call to the sp's in the sample code with the actual script from each SP..
--Switching the SP's
create proc SwitchingSP
@Parm1 int
,@Parm2 datetime
,@UseBDate bit
AS
if @UseBDate = 1
begin
exec SPwithBDate @Parm1, @Parm2
end
else
begin
exec SPwithOtherDate @Parm1, @Parm2
end
or dynamic sql I think this will work... but if your original SP is really really complicated or long I'd just go the first way...
create proc MyProc
@Parm1 int
,@Parm2 datetime
,@Type bit
AS
declare @SQL as nvarchar(max)
set @SQL = N'select top 10 * from sys.tables where object_id = @ID and '
if @Type = 1
set @SQL = @SQL + N'Create_Date = @Date'
else
set @SQL = @SQL + N'modify_date = @Date'
exec sp_executesql @SQL, N'@Date Datetime, @ID int', @ID = @Parm1, @Date = @Parm2
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
February 22, 2013 at 2:42 am
If the created_date and modify date are not nullable, then please check this code.
create procedure usp_switch_data
@create_date datetime = null
,@modify_date datetime = null
as
if @create_date is not null or @modify_date is not null
begin
select * from mytable
where created_date = isnull(@create_date, create_date)
and modify_date = isnull(@modify_date, modify_date)
end
February 22, 2013 at 4:18 am
Yes, we can call stored procedures inside stored procedures.
see sample storedprocedure:
Create Procedure Details
(
@FromDate datetime
,@ToDate datetime
,@DateType nchar(10)
)
AS
BEGIN
IF @DateType = 'Birth'
EXEC procwithBirthDate @FromDate, @ToDate
ELSE
EXEC procWithHireDate @FromDate, @ToDate
END
I think it vl helps.
February 22, 2013 at 5:15 am
salliven (2/22/2013)
If the created_date and modify date are not nullable, then please check this code.
create procedure usp_switch_data
@create_date datetime = null
,@modify_date datetime = null
as
if @create_date is not null or @modify_date is not null
begin
select * from mytable
where created_date = isnull(@create_date, create_date)
and modify_date = isnull(@modify_date, modify_date)
end
ISNULL() in where clauses is usually a performance killer in SQL Server.
February 22, 2013 at 5:41 am
I ended up going with the first suggestion by Want a cool Sig and it worked just as I needed.
I will also try the others to see if I can make them work and understand them.
Thank you everyone for your suggestions. I really appreciate the help.
Have a great day,
- nails
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply