Please help me solve this 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)

Please help me solve this query

Author

Topic

vinton
Starting Member

3 Posts

Posted - 2008-10-29 : 01:18:47

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SpSearchContracts]
@ContractNumber varchar(20)=NULL,
@TenantID int=NULL,
@StartDate datetime =null,
@EndDate datetime =null
AS
DECLARE @query nvarchar(1000)
BEGIN
set @query='SELECT ContractID, ContractNumber, CurrencyID, OwnerID, TenantID, PropertyID, PropertyUnitID, ContractTypeID, FrequencyID, Amount, AdvanceAmount,
StartDate, EndDate, Terms
FROM dbo.Contract where ContractID<>0'
IF @TenantID IS NOT NULL
BEGIN
SET @query = @query + ' AND [TenantID]= '+ cast(@TenantID as nvarchar(10))
END
IF @StartDate IS NOT NULL
BEGIN
SET @query = @query + ' AND [StartDate]= '+ cast(@StartDate as datetime)
END
IF @EndDate IS NOT NULL
BEGIN
SET @query = @query + ' AND [EndDate]= '+ cast(@EndDate as datetime)
END

IF @ContractNumber IS NOT NULL
BEGIN
SET @ContractNumber=REPLACE(@ContractNumber,'''','''''')
SET @query = @query + ' AND [ContractNumber] like '''+@ContractNumber+'%'+''''
END
SET @query = @query + ' ORDER BY [ContractID] '

END

EXEC sp_executesql @query

when i execute this query it shows error converting datetime to varchar something .please help me

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-29 : 03:26:58

Datetime values must be enclosed in quotes.

The actual query shd look something like this ...
AND [StartDate]= '01/01/2008'

So your query shd look like this....
SET @query = @query + ' AND [StartDate]= '''+ cast(@StartDate as datetime) + ''''

BTW why have you posted under Net questions?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 05:25:12

quote:
Originally posted by cvraghu

Datetime values must be enclosed in quotes.

The actual query shd look something like this ...
AND [StartDate]= '01/01/2008'

So your query shd look like this....
SET @query = @query + ' AND [StartDate]= '''+ cast(@StartDate as datetime) + ''''

BTW why have you posted under Net questions?

NB: Please note that depending on you database locale setting this format of date can give you wrong results. Consider Brittish / American date format differences. The 2 ANSI standard DATETIME strings are

yyyymmdd & yyyy-mm-ddThh:mm:ss.mmm

Examples


AND [StartDate] = '20080401' -- (1st April 2008)
AND [startDate] = '2008-04-01T23:59:59.000' (1st April 2008 @ 2359 and 59 seconds)
[code]

Doing [code]
AND [startDate] = '01/04/2008'

Could mean 1st April or 4th January depending on the database locale.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 05:31:33

Also, there is no reason to use dynamic sql here. Your query can be rewritten so:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SpSearchContracts]
	@ContractNumber varchar(20)=NULL,
	@TenantID int=NULL,
	@StartDate datetime =null,
	@EndDate datetime =null
AS
SELECT
	ContractID
	, ContractNumber
	, CurrencyID
	, OwnerID
	, TenantID
	, PropertyID
	, PropertyUnitID
	, ContractTypeID
	, FrequencyID
	, Amount
	, AdvanceAmount
	, StartDate
	, EndDate
	, Terms
FROM
	dbo.Contract
where
	ContractID<>0

	AND (
		[TenantID] = @TenantID
		OR
		@TenantId IS NULL
		)

	AND (
		[startDate] = @startDate
		OR
		@startDate IS NULL
		)

	AND (
		[endDate] = @endDate
		OR
		@endDate IS NULL
		)

	AND (
		[ContractNumber] LIKE @contractNumber + '%'
		OR
		@contractNumber IS NULL
		)
ORDER BY
	[contractID]

This way you don't have to worry about any conversions.

-------------
Charlie

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-29 : 05:57:50

moved to appropriate forum

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

vinton
Starting Member

3 Posts

Posted - 2008-10-29 : 06:14:41

Thanks to all

vinton
Starting Member

3 Posts

Posted - 2008-10-30 : 00:42:35

But when i execute this query its not displaying any value data.
exec SpSearchContracts '','','2005-12-12'
But i execute exec SpSearchContracts '','1','2005-12-12' its displaying.Can u correct me? if i enter dates without '' like
SpSearchContracts '','',2005-12-12 this,its displaying error 'Incorrect syntax near '-'.pleas help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 04:14:35

you need pass them as NULL rather than '' as your stored procedure expects default value to be NULL

exec SpSearchContracts NULL,NULL,'2005-12-12'

also date values should be passed as string within ''

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 05:16:04

You don't need to fully qualify the procedure call if you don't want to, assuming your stored proc has defaults for the missing parameters.

You could do:


exec SpSearchContracts @startDate = '2005-12-12'

That will be equivalent to the syntax visakh16 posted but it might help you remember which parameter you are actually passing data to. You could also do something like this


EXEC SpSearchContracts
    @startDate = '2005-12-12'
    , @endDate = '2006-01-01'

Rather than having to type


EXEC SpSearchContracts NULL, NULL, '2005-12-12' '2006-01-01'

NB These date format strings (yyyy-mm-dd) are not ANSI standard strings. Therefore they change depending on the Locale of the database.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources