SQL Search - 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 2000 Forums

SQL Server Development (2000)

SQL Search

Author

Topic

egghi
Starting Member

17 Posts

Posted - 2006-10-16 : 16:19:30

Hi,

I had a stored procedure which search a SQL table, PIPDocument, based on the search criteria selected by user using an Access form. I started with one search parameter on the form, and the stored procedure worked.

CREATE PROCEDURE s_PIPDocumentSearchResults
@patname varchar(50)
AS

SELECT Type, PatName, PatDoB, SARNumber, PatCIN, CCSCaseNum, CAST(DocumentID AS varchar(40)) + '.htm' AS DocumentLink, EffFromDate, EffThruDate, ServiceCode
FROM PIPDocument
WHERE (DIFFERENCE(@patname, PatName) > 3)
GO

However, I now want to add 4 more parameters now, and I don't know how to modify the stored procedure so that the search result will return based on different criteria selected by users. I am using SQL 2000. I only come up with something like this so far and it's not working:

CREATE PROCEDURE s_PIPSearch
@patname varchar(50) = NULL,
@dob datetime = NULL,
@SAR varchar(11) = NULL,
@CIN varchar(10) = NULL,
@CCS varchar(7) = NULL AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

SELECT @sql =
'SELECT Type, PatName, PatDoB, SARNumber, PatCIN, CCSCaseNum, CAST(DocumentID AS varchar(40)) + '.htm' AS DocumentLink, EffFromDate, EffThruDate, ServiceCode
FROM PIPDocument
WHERE 1 = 1'

IF @patname IS NOT NULL
SELECT @sql = @sql + ' AND PatName = @xpatname'

IF @dob IS NOT NULL
SELECT @sql = @sql + ' AND PatDoB = @xdob'

IF @SAR IS NOT NULL
SELECT @sql = @sql + ' AND SARNumber = @xSAR'

IF @CIN IS NOT NULL
SELECT @sql = @sql + ' AND PatCIN = @xCIN'

IF @CCS IS NOT NULL
SELECT @sql = @sql + ' AND CCSCaseNum = @xCCS'

SELECT @paramlist = '@xpatname varchar(50),
@xdob datetime,
@xSAR varchar(11),
@xCIN varchar(10),
@xCCS varchar(7)'

EXEC sp_executesql @sql, @paramlist,
@patname,@dob,@SAR,@CIN,@CCS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-16 : 17:04:49

Here is one not non-dynamic-sql possibility:


FROM    PIPDocument 
WHERE	PatDoB = isNull(@xdob, PatDob)
and	SARNumber = isNull(@xSAR, SARNumber)
and	PatCIN = isNull(@xCIN, PatCIN)
and	CCSCaseNum = isNull(@xCCS, CCSCaseNum)
and	3 < case when @patname is null then 4 else DIFFERENCE(@patname, PatName) end

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-16 : 19:04:23

>>> it's not working

Could you give us a little more to go on? Is it giving an error? Incorrect results?

CODO ERGO SUM

egghi
Starting Member

17 Posts

Posted - 2006-10-17 : 13:42:17

Hi,

My original stored procedure which only search by one parameter, patname, worked out perfect. The problem started when I tried to add other parameters to the stored procedure. I got error messages such as "@SAR parameter does not exist or was not supplied" because I did not enter any value for the parameter on the ACCESS form. However, the stored procedure should allow null value in the parameter and search the results based on best match to the parameters provided by users.

Please let me know if my answer helps you understand the problem better!

Thank you very much

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-17 : 15:08:37

>>I got error messages such as "@SAR parameter does not exist or was not supplied"
if you go the dynamic sql route you'll have to change your statements like this:


IF @patname IS NOT NULL 
SELECT @sql = @sql + ' AND PatName = ''' + @patname  + ''''

btw, I'd avoid dynamic sql if you can get an approach like the one I suggested ealier to work with the same efficiency.

EDIT:
when you troubleshoot dynamic sql be sure to "print" the contents of your @sql variable. Your errors will be easy to see

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 09:33:40

>>when you troubleshoot dynamic sql be sure to "print" the contents of your @sql variable. Your errors will be easy to see

Thats what everyone forgets

Also refer

http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources