DateTime conversion error when using dynamic SQL 2

[Craftor]

Hi everybody,

I'm using dynamic SQL in a stored procedure to build up a query that can have a variety of WHERE clauses depending on what parameters are passed into the stored procedure. One of my parameters (a DATETIME) seems to be causing an error:

Conversion failed when converting datetime from character string

The stored proc code is:

CREATE PROCEDURE dbo.praGetSpecifiedEvent
	@vcrUserName VARCHAR (8) = NULL,
	@dtmDateFrom DATETIME  = NULL,
	@dtmDateTo DATETIME = NULL,
	@in4IDEventStatus INT = NULL,
	@result TINYINT OUTPUT

AS

BEGIN

	SET NOCOUNT ON

	SET @result = null
	
	DECLARE @sqlStatement VARCHAR (5000)
	DECLARE @whereClause VARCHAR(5000)

	SELECT @sqlStatement = 
		'

		SELECT 
			in4IDEvent,
			dtmDateLastEvent,
			dtmDateCurrentEvent,
			vcrNameLastModifiedUser

		FROM
			dbo.tblEvent TE
				INNER JOIN dbo.tblPaymentDetails P
					ON TE.in4IDPayment = P.in4IDPayment
				INNER JOIN dbo.tblEventStatus ES
					ON TE.in4IDEventStatus = ES.in4IDEventStatus
		'

		IF @vcrUserName IS NOT NULL
		BEGIN
			SELECT @whereClause = ' P.vcrUserName = ''' + @vcrUserName + ''''
		END

		IF @dtmDateFrom IS NOT NULL
		BEGIN
			IF @whereClause IS NOT NULL
			BEGIN
				SELECT @whereClause = @whereClause + ' AND '
			END
			SELECT @whereClause = @whereClause + ' TE.dtmDateLastEvent <= ''' +  @dtmDateTo  + ''''
			
		END

		IF @dtmDateTo IS NOT NULL
		BEGIN
			IF @whereClause IS NOT NULL
			BEGIN
				SELECT @whereClause =  @whereClause + ' AND '
			END
			SELECT @whereClause = @whereClause + ' TE.dtmDateLastEvent <= @dtmDateTo '
		END

		IF @in4IDEventStatus IS NOT NULL
		BEGIN
			IF @whereClause IS NOT NULL
			BEGIN
				SELECT @whereClause = @whereClause + ' AND '
			END
			SELECT @whereClause = @whereClause + ' TES.in4IDEventStatus <= ' + @in4IDEventStatus
		END

		IF @whereClause IS NOT NULL
		BEGIN 
			SELECT @sqlStatement = @sqlStatement + ' WHERE ' + @whereClause
		END


		EXEC (@sqlStatement)
			
		SET @result = 1
	

END

GO

When trying to run it with test data as follows:

DECLARE @result tinyint
DECLARE @dtm DATETIME
SET @dtm = '2007-12-06' 
EXEC praGetSpecifiedEvent 'username', @dtm, NULL, NULL, @result OUTPUT
SELECT @result

it generates the error above on the line:

SELECT @whereClause = @whereClause + ' TE.dtmDateLastEvent <= ''' +  @dtmDateTo  + ''''

Can someone help me with this, please?

Thanks as always

Craftor

 

[ca8msm]

The best overall method is to get rid of the dynamic sql. It's not as efficient and could be a security risk.

This may help you when you rewrite the query.



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Qik3Coder]

my favorite alternative to dynamically built sql is dynamically executed sql.

You have

if @myDate is not null
begin
   @sql = @sql + " t.date = " + @myDate
end

whereas, i would have

and t.date = CASE WHEN @myDate is not null then [b]@myDate[/b] else [b]t.date[/b] END

This is fun to write, but a pain to debug. You get a better performance out than dynamically built sql.

Someone's going to yell at me for saying this, but i like using the case when with joins, it makes the code cleaner to read, once you get used to it.

-Sometimes the answer to your question is the hack that works

 

[Craftor]

Thank you for both of your responses.

I ended up removing the dynamic SQL and using the ISNULL statement to see whether the parameter passed in is null and, if not, to use that parameter.

Thanks as always


Craftor