Annoying error while running SProc

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)

Annoying error while running SProc

Author

Topic

aleayz
Starting Member

3 Posts

Posted - 2003-05-08 : 10:10:33

Hi, I just wrote a Sproc that's gonna be used in Search routine. Basically, I'm passing a lot of params from VB.Net to database. In my SProc I evaluate parms and if par is not null I include it in Where clause. Below is the code with error msg.
Thanks a lot in advance,
--Alex

---------------------------------------
CREATE PROCEDURE uspPARsearchPar

@strSSN1 as varchar(10),
@strSSN2 as varchar(10),
@strLast1 as varchar(50),
@strLast2 as varchar(50),
@strFirst1 as varchar(50),
@strFirst2 as varchar(50)

AS

Declare @strSQL varchar(2000)
Declare @strWhere varchar(1000)
Declare @strOrderBy varchar(100)

SET NOCOUNT ON

set @strSQL = 'select SSN = i.SSN,
PensionID = i.IndividualUID,
FullName = dbo.udfPARFormatName(FirstName, MiddleName, LastName, Suffix),
FirstName = IsNull(i.FirstName, ''),
LastName = i.LastName,
Gender = d.MDDCodeDescription,
DOB = i.DOB,
DOD = i.DOD,
MaritalStatus = i.MaritalStatus,
HomePhone = i.HomePhone,
CellPhone = i.CellPhone
from PAR_Participant p
inner join IND_Individual i on i.IndividualUID = p.PARIndividualUID
-- inner join IND_Alias a on i.IndividualUID = a.ALSIndividualUID
left join MST_DropDown d on d.MDDCode = i.Gender and d.MDDCategoryUID = 3 '

Set @strWhere = 'Where '

If @strSSN1 is not Null Begin
Set @strWhere = @strWhere + ' SSN like ''' + @strSSN1 + '%'''
End

If @strSSN2 is not Null Begin
Set @strWhere = @strWhere + ' and SSN like ''' + @strSSN2 + '%'''
End

If @strLast1 is not Null Begin
Set @strWhere = @strWhere + ' and i.LastName like ''' + @strLast1 + '%'''
End

If @strLast2 is not Null Begin
Set @strWhere = @strWhere + ' and i.LastName like ''' + @strLast2 + '%'''
End

If @strFirst1 is not Null Begin
Set @strWhere = @strWhere + ' and i.FirstName like ''' + @strFirst1 + '%'''
End

If @strFirst2 is not Null Begin
Set @strWhere = @strWhere + ' and i.FirstName like ''' + @strFirst2 + '%'''
End

set @strOrderBy = 'Order By i.SSN'

Set @strSQL = @strSQL + @strWhere + @strOrderBy

Execute (@strSQL)

GO

==================================
Error message:
Line 15: Incorrect syntax near '1'.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-08 : 10:22:45

if @strSSN1 is null and @strSSN2 is not

Set @strWhere = 'Where '
If @strSSN1 is not Null Begin
Set @strWhere = @strWhere + ' SSN like ''' + @strSSN1 + '%'''
End
If @strSSN2 is not Null Begin
Set @strWhere = @strWhere + ' and SSN like ''' + @strSSN2 + '%'''
End

will give you 'where and ...'

Don't think that is the error you have though.

You need a space before the order by clause.

IsNull(i.FirstName, ''),
sb IsNull(i.FirstName, ''''),

Try printing @sql - the errors should be obvious.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 05/08/2003 10:24:09

Edited by - nr on 05/08/2003 10:25:46

aleayz
Starting Member

3 Posts

Posted - 2003-05-08 : 10:39:39

Thanks for reply.

----
if @strSSN1 is null and @strSSN2 is not

Set @strWhere = 'Where '
If @strSSN1 is not Null Begin
Set @strWhere = @strWhere + ' SSN like ''' + @strSSN1 + '%'''
End
If @strSSN2 is not Null Begin
Set @strWhere = @strWhere + ' and SSN like ''' + @strSSN2 + '%'''
End

will give you 'where and ...'
---------------------------------

You're right, but how would I add 'and' if I don't know which parameter will be added first after Where??

--Alex

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-08 : 10:44:31

You can add them all with and then change the first and to where afterwards. Or set a flag when you add the first where and check it on every addition to the clause.

Also se the other problems I added afterwards.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aleayz
Starting Member

3 Posts

Posted - 2003-05-08 : 12:48:20

I fixed all of the syntax problems; as you said all problems were obvious.

But I'm still struggling with And/Or. How would I actually check for what need to be added to Where string?
Thanks,
--Alex

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-08 : 14:01:52

advice: do not use dynamic SQL unless you need to. look at what I've put below.

Your logic is a little hazy on this one; I did my best to clean things up and make it logically sound. Let me know if it makes sense.

CREATE PROCEDURE uspPARsearchPar

@strSSN1 as varchar(10),
@strSSN2 as varchar(10),
@strLast1 as varchar(50),
@strLast2 as varchar(50),
@strFirst1 as varchar(50),
@strFirst2 as varchar(50)

AS

SET NOCOUNT ON

SELECT SSN = i.SSN,
PensionID = i.IndividualUID,
FullName = dbo.udfPARFormatName(FirstName, MiddleName, LastName, Suffix),
FirstName = IsNull(i.FirstName, ''),
LastName = i.LastName,
Gender = d.MDDCodeDescription,
DOB = i.DOB,
DOD = i.DOD,
MaritalStatus = i.MaritalStatus,
HomePhone = i.HomePhone,
CellPhone = i.CellPhone
from PAR_Participant p
inner join IND_Individual i on i.IndividualUID = p.PARIndividualUID
left join MST_DropDown d on d.MDDCode = i.Gender and d.MDDCategoryUID = 3

WHERE (SSN in (@StrSSN1, @StrSSN2) OR
@StrSSN1 is null AND @StrSSN2 is null) AND
(i.LastName IN (@StrLast1, @StrLast2) OR
@StrLast1 is null AND @StrLast2 is null) AND
(i.FirstName IN (@StrFirst1, @StrFirst2) OR
@StrFirst1 is null AND @StrFirst2 is null)
Order By i.SSN

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-08 : 14:05:58

Set @strWhere = ''

If @strSSN1 is not Null Begin
Set @strWhere = @strWhere + ' and SSN like ''' + @strSSN1 + '%'''
End

If @strSSN2 is not Null Begin
Set @strWhere = @strWhere + ' and SSN like ''' + @strSSN2 + '%'''
End

If @strLast1 is not Null Begin
Set @strWhere = @strWhere + ' and i.LastName like ''' + @strLast1 + '%'''
End

If @strLast2 is not Null Begin
Set @strWhere = @strWhere + ' and i.LastName like ''' + @strLast2 + '%'''
End

If @strFirst1 is not Null Begin
Set @strWhere = @strWhere + ' and i.FirstName like ''' + @strFirst1 + '%'''
End

If @strFirst2 is not Null Begin
Set @strWhere = @strWhere + ' and i.FirstName like ''' + @strFirst2 + '%'''
End

if @strWhere <> ''
set @strWhere = stuff(@strWhere, 2, 3, 'where')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-08 : 16:02:16

Nigel -- a SSN cannot be two values at once -- I think you need the handle the logic a little closer to how I did it.

i.e., if two SSN's are passed, the code generated is:

WHERE SSN = @strSSN1 AND
SSN = @strSSN2

which will of course always fail unless the paramters are the same.

Also, try to avoid dynamic SQL if you can ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources