COALESCE usage - SQL Server Forums

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)

COALESCE usage

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-03-11 : 09:53:12

Please tell me what's wrong in this query. I am using SQL 2005

It gives too many arguments specified

CREATE PROCEDURE spocsearch 
(

@nm nvarchar(30), @ID nvarchar(7), @custid nvarchar(10)

)

AS

SELECT nm, ID, custid, custcode

FROM Customer

WHERE

(ID = COALESCE(@ID,ID) OR ID IS NULL) AND 

(nm = COALESCE(@nm,nm ) OR nm IS NULL) AND

(custid = COALESCE(@custid ,custid ) OR custid IS NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:59:21

You should either use COALESCE or use the IS NULL approach.

CREATE PROCEDURE spocsearch 
(
	@nm nvarchar(30),
	@ID nvarchar(7),
	@custid nvarchar(10)
)
AS

SELECT	nm,
	ID,
	custid,
	custcode
FROM	iTrns
WHERE	ID = COALESCE(@ID, ID)
	AND nm = COALESCE(@nm, nm)
	AND custid = COALESCE(@custid, custid)

or this

CREATE PROCEDURE spocsearch 
(
	@nm nvarchar(30),
	@ID nvarchar(7),
	@custid nvarchar(10)
)
AS

SELECT	nm,
	ID,
	custid,
	custcode
FROM	iTrns
WHERE	(ID = @ID OR @ID IS NULL)
	AND (nm = @nm or @nm is null)
	AND (custid = @custid or @custid is null)