help me in using Dynamic Query

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 2005 Forums

Transact-SQL (2005)

help me in using Dynamic Query

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-08-11 : 04:57:19

Hi All,

This is my code
ALTER FUNCTION [dbo].[fn_GetTabSelectionInfo]
(
@SessionID Int
)
RETURNS
@Results TABLE (MatchID Int,ClubID Int,PlayerID Int,MatchDate Varchar(50))

AS
BEGIN
-- Declare @SessionID Int
-- Set @SessionID = 11
Declare @QueryStr Varchar(1000)
Set @QueryStr = ' '
IF Exists(Select 'T' From TrendSELECTED_COMPETITIONS Where SessionID=@SessionID)
Set @QueryStr=@QueryStr + ' Inner Join TrendSELECTED_COMPETITIONS TSC on TSC.CompetitionID=VMI.CompetitionID and TSC.SessionID= ' + cast(@SessionID as varchar)
IF Exists(Select 'T' From TrendSELECTED_Matches Where SessionID=@SessionID)
Set @QueryStr=@QueryStr + ' Inner Join TrendSELECTED_Matches TSM on TSM.MatchID=VMI.MatchID and TSM.SessionID= ' + cast(@SessionID as varchar)
IF Exists(Select 'T' From TrendSELECTED_TEAMS Where SessionID=@SessionID)
Set @QueryStr=@QueryStr + ' Inner Join TrendSELECTED_TEAMS TST on TST.ClubID=VMI.ClubID and TST.SessionID= ' + cast(@SessionID as varchar)
IF Exists(Select 'T' From TrendSELECTED_PLAYERS Where SessionID=@SessionID)
Set @QueryStr=@QueryStr + ' Inner Join TrendSELECTED_PLAYERS TSP on TSP.PlayerID=VMI.PLayerID and TSP.SessionID=' + cast(@SessionID as varchar)
IF Exists(Select 'T' From TrendSELECTED_POSITIONS Where SessionID=@SessionID)
Set @QueryStr=@QueryStr + ' Inner Join view_MatchPositionsInfoComplete VMP On VMP.PlayerID=VMI.PlayerID ' +
' Inner Join TrendSELECTED_Positions TSP on TSP.PositionID=VMP.PositionID and TSP.SessionID= ' + cast(@SessionID as varchar)
Insert into @results
EXEC('Select Distinct VMI.MatchID,VMI.ClubID,VMI.PlayerID,VMI.Date
From view_MatchInfoMoreComplete VMI ' + @QueryStr)

RETURN
End
If i execute the above function i got this error
Msg 443, Level 16, State 14, Procedure fn_GetTabSelectionInfo, Line 35
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

My doubt is cann we use Dynamic query execution in functions

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 05:06:48

You cant use dynamic sql inside a function. Make use of stored procedure instead

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 05:21:07

You probably want this i guess. no need of dynamic querry

Select Distinct VMI.MatchID,VMI.ClubID,VMI.PlayerID,VMI.Date
From view_MatchInfoMoreComplete VMI
left Join TrendSELECTED_COMPETITIONS TSC on TSC.CompetitionID=VMI.CompetitionID and TSC.SessionID=@SessionID 
left Join TrendSELECTED_Matches TSM on TSM.MatchID=VMI.MatchID and TSM.SessionID=@SessionID 
left Join TrendSELECTED_TEAMS TST on TST.ClubID=VMI.ClubID and TST.SessionID=@SessionID 
left Join TrendSELECTED_PLAYERS TSP on TSP.PlayerID=VMI.PLayerID and TSP.SessionID=@SessionID 
 Left Join TrendSELECTED_Positions TSP on TSP.PositionID=VMP.PositionID and TSP.SessionID=@SessionID 
...

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-08-11 : 05:58:15

thanks for your response

Solutions are easy. Understanding the problem, now, that's the hard part

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources