September 16, 2011 at 12:42 am
Hi All,
I've written a stored procedure which joins 2 different tables from 2 different databases, But the database names are hardcoded there, I need to pass database name to the query dynamically because we dont know how client will be setting up database names on their machines.
Here is the stored procedure, Can anyone please help me out fixing this problem..
ALTER PROCEDURE [dbo].[usp_get_all_jobreqappliedcandidates]
AS
BEGIN
SELECT
j.JobReqID,j.JobReqCode,j.DateAdded,Count(c.CandidateID) as AppliedCandidates
FROM ResumesDB.dbo.Candidates c
FULL OUTER JOIN RecruitmentDB.dbo.JobRequisition j ON j.JobReqID=c.JobReqID
LEFT OUTER JOIN RecruitmentDB.dbo.CandidateInterview ci ON c.CandidateID=ci.CandidateID
AND ci.CandidateID is null
GROUP BY j.JobReqID,j.JobReqCode,j.DateAdded
ORDER BY j.DateAdded Desc
END
September 16, 2011 at 1:10 am
Here is one idea. Hopefully you can work it out from here.
USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Events_Aggregate_Count_by_EventType_AD] Script Date: 09/16/2011 16:37:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Events_Aggregate_Count_by_EventType_AD]
@SiteID nvarchar (max),
@Cause nvarchar (max),
@SourceType nvarchar (max),
@Top int,
@DateRange nvarchar(30),
@DBName nvarchar (max)
AS
SET NOCOUNT ON
Declare @StartDateRange AS DateTime
Declare @EndDateRange AS DateTime
SET @StartDateRange = (Select StartDateRange From fn_DateSel (@DateRange))
SET @EndDateRange = (Select EndDateRange From fn_DateSel (@DateRange))
BEGIN
Declare @sql nvarchar(max)
Declare @ParamDefinition AS nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT Cause, ISNULL(EventDetails.SourceID, ''0'') As SourceID
FROM [' + @DBName + '].dbo.EventDetails INNER JOIN
[' + @DBName + '].dbo.SiteDetails ON [' + @DBName + '].dbo.EventDetails.SiteID = [' + @DBName + '].dbo.SiteDetails.SiteID
WHERE LocalStartTime >= @StartDateRange AND LocalStartTime < @EndDateRange
AND SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND dbo.SourceTypeConv (EventDetails.SourceType) IN (Select Param From fn_MVParam ('''+@SourceType+''','',''))
AND Cause IN (Select Param From fn_MVParam ('''+@Cause+''','',''))
'
Set @ParamDefinition =
' @StartDateRange DateTime,
@EndDateRange DateTime,
@SiteID nvarchar(50),
@Cause nvarchar(max),
@SourceType nvarchar (max),
@Top int,
@DateRange nvarchar(30),
@DBName nvarchar (100)'
Create table #t([Cause] nvarchar(max), [SourceID] int)
Insert into #t
Execute sp_Executesql @sql, @ParamDefinition, @StartDateRange, @EndDateRange, @SiteID, @Cause, @SourceType, @Top, @DateRange, @DBName
---Print @sql
Select DISTINCT Top(@Top) WITH TIES [Cause], [SourceID], Count (Cause) AS CauseCount
From #t
Group BY [Cause], [SourceID]
Order By CauseCount DESC
Drop Table #t
END
SET NOCOUNT OFF
Return
September 16, 2011 at 1:23 am
I would create a synonym in the database, referring to the table in the other database.
Check "CREATE SYNONYM" in books online
ps I prefer still leaving a trace in the naming convention so the human eye detects it's a remote object being referred.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 16, 2011 at 5:24 am
Hi,
You can do it by dynamic SQL statement.
Search Google for Dynamic SQL statement.
Thanks
Shatrughna
Shatrughna
September 16, 2011 at 6:52 am
shatrughna (9/16/2011)
Hi,You can do it by dynamic SQL statement.
Search Google for Dynamic SQL statement.
Thanks
Shatrughna
Indeed, if you don't care about security, plan-recompile, ....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply