June 23, 2011 at 5:22 am
Hi Guys.,
stored Procedure :
USE [TMSLocal]
GO
/****** Object: StoredProcedure [dbo].[GetUpcomingEventsForSchedulings] Script Date: 06/23/2011 15:43:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUpcomingEventsForSchedulings]
@EventDate DATETIME,
@ExcludeBrands VARCHAR(225),
@ExcludeEventTypes VARCHAR(225)
AS
BEGIN
DECLARE @strQuery VARCHAR(MAX)
SET @strQuery = 'SELECT E.EventID, isnull(E.EventName,'') as EventName,
E.EventDate, E.venue,
E.VenueCity, E.VenueState, E.VenueCountry, E.EventStartTime, E.OnSaleDate,
E.SalesGross, E.TicketUnits, E.EventCapacity, E.Milage, E.BrandID,
ISNULL(B.BrandName, '') AS BrandName, E.EventType, E.Published,
ISNULL(ET.EventType, '') AS EventTypeName,
ISNULL(E_T.TotalTalents, 0) AS TotalTalents
FROM EventTypes AS ET
RIGHT OUTER JOIN EventMaster AS E ON ET.EventTypeID = E.EventType
LEFT OUTER JOIN Brands AS B ON E.BrandID = B.BrandID
LEFT OUTER JOIN (SELECT EventID, Count(TalentID) as TotalTalents
FROM event_Talent Group By EventID) E_T ON E_T.EventID = E.EventID
WHERE E.EventDate >=' + CONVERT(DATETIME, @EventDate) + 'And E.Cancelled=0'
IF(@ExcludeBrands <> '')
BEGIN
SET @strQuery = @strQuery + ' AND E.BrandID NOT IN (' + @ExcludeBrands + ')'
END
IF(@ExcludeEventTypes <> '')
BEGIN
SET @strQuery = @strQuery + ' AND E.EventType NOT IN (' + @ExcludeEventTypes + ')'
END
print @strQuery
EXEC(@strQuery)
END
Input Parameter :
exec GetUpcomingEventsForSchedulings '2009-11-02 00:00:00.000','',''
Error :
Msg 241, Level 16, State 1, Procedure GetUpcomingEventsForSchedulings, Line 10
Conversion failed when converting date and/or time from character string.
Please give me the solution for this problem
Thanks
June 23, 2011 at 6:22 am
Thanks
I got solution because single quots problem ,
'SELECT E.EventID, isnull(E.EventName,'''') as EventName,
E.EventDate, E.venue,
E.VenueCity, E.VenueState, E.VenueCountry, E.EventStartTime, E.OnSaleDate,
E.SalesGross, E.TicketUnits, E.EventCapacity, E.Milage, E.BrandID,
ISNULL(B.BrandName, '''') AS BrandName, E.EventType, E.Published,
ISNULL(ET.EventType, '''') AS EventTypeName,
ISNULL(E_T.TotalTalents, 0) AS TotalTalents
FROM EventTypes AS ET
RIGHT OUTER JOIN EventMaster AS E ON ET.EventTypeID = E.EventType
LEFT OUTER JOIN Brands AS B ON E.BrandID = B.BrandID
LEFT OUTER JOIN (SELECT EventID, Count(TalentID) as TotalTalents
FROM event_Talent Group By EventID) E_T ON E_T.EventID = E.EventID
missing single quotes ( Bold,Underline)
Thanks for great Support to slove the problem
thanks
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply