Help with wildcard numeric search

I have constructed this stored procedure using union to let me find with
lots of flexibility. However one of the Parameters is a Numeric and I only
want to test if it's gtreater than zero. When I do an IF statement I get a
syntax error. I'm new to stored procs, this sort of thing would fly anywhere
else. Can someone help. The parameter I am trying to match on if greater
than zero is @.SuiteAncillaryID. If zero I want the complete result set so I
will need an else.
CREATE PROCEDURE [dbo].[pr_tblPersonAddress_Find_Limited]
@.Complex_Name varchar(60) = '%',
@.Building_Name varchar(60) = '%',
@.Location_Descriptor varchar(60) = '%',
@.House_Number_1 varchar(6) = '%',
@.Street_name varchar(45) = '%',
@.Locality_Name varchar(46) = '%',
@.Surname varchar(50) = '%',
@.FirstName varchar(50) = '%',
@.Position varchar(50) = '%',
@.TradingName varchar(255) = '%',
@.CompanyName varchar(255) = '%',
@.ACN char(20) = '%',
@.ABN char(20) = '%',
@.SiteName varchar(255) = '%',
@.SiteAncillaryID int =0,
@.ErrorCode int OUTPUT
AS
SET NOCOUNT ON
SELECT POIC From
(Select POIC
From tblpoic POICP
JOIN tblPerson PERSON
on POICP.PersonID=PERSON.PersonID
Where
PERSON.[Surname] like '%' + @.Surname + '%' AND
PERSON.[FirstName] like '%' + @.FirstName + '%' AND
PERSON.[Position] like '%' + @.Position + '%'
Union
Select POIC
From tblpoic POICO
JOIN tblOrganisation ORGANISATION
on POICO.OrgID=ORGANISATION.OrgID
Where
ORGANISATION.[TradingName] like '%' + @.TradingName + '%' AND
ORGANISATION.[CompanyName] like '%' + @.CompanyName + '%' AND
ORGANISATION.[ACN] like '%' + @.ACN + '%' AND
ORGANISATION.[ABN] like '%' + @.ABN + '%'
UNION
IF @.SiteAncillaryID>0
Begin
Select POIC
From tblpoic POICS
JOIN tblSite SITE
on POICS.SiteID=SITE.SiteID
WHERE
SITE.[SiteName]>@.SiteName
and SITE.[SiteName]=@.siteAncillaryID
Union
END
Select POIC
From tblpoic POICA
JOIN tbladdress ADDRESS
ON POICA.AddressID = ADDRESS.AddressID
where
ADDRESS.[Complex_Name] like '%' + @.Complex_Name + '%' AND
ADDRESS.[Building_Name] like '%' + @.Building_Name + '%' AND
ADDRESS.[Location_Descriptor] like '%' + @.Location_Descriptor + '%' AND
ADDRESS.[House_Number_1] like '%' + @.House_Number_1 + '%' AND
ADDRESS.[Street_name] like '%' + @.Street_name + '%' AND
ADDRESS.[Locality_Name] like '%' + @.Locality_Name + '%' ) as TEMP
Order by POIC
-- Get the Error Code for the statement just executed.
SELECT @.ErrorCode=@.@.ERROR
-- Get the IDENTITY value for the row just inserted.
GOJust include it in the where clause - if it's <= 0 then you will get an empt
y
resultset for that part of the union.
UNION
Select POIC
From tblpoic POICS
JOIN tblSite SITE
on POICS.SiteID=SITE.SiteID
WHERE
SITE.[SiteName]>@.SiteName
and SITE.[SiteName]=@.siteAncillaryID
and @.SiteAncillaryID>0|||If statements are Transact-SQL, not SQL. A Stored Proc is written in
Transact-SQL language, which is an MS SQL Server priprietary Programming
language for control flow and Variable declaration, whch understands standar
d
SQL..
Standard SQL is the embedded statements that "talk" to the query processor,
the Selects, Updates, Inserts, and Deletes... The T=SQL constructions, (If,
While, Begin End, Declare @.Variable, etc.) can be used only Outside of SQL
Statements not inside of one.
btw, the SQL equivilent (closest equivilent) to IF is Case statement.
Check it out in Books On Line.
"Shoeman" wrote:

> I have constructed this stored procedure using union to let me find with
> lots of flexibility. However one of the Parameters is a Numeric and I only
> want to test if it's gtreater than zero. When I do an IF statement I get a
> syntax error. I'm new to stored procs, this sort of thing would fly anywhe
re
> else. Can someone help. The parameter I am trying to match on if greater
> than zero is @.SuiteAncillaryID. If zero I want the complete result set so
I
> will need an else.
>
> CREATE PROCEDURE [dbo].[pr_tblPersonAddress_Find_Limited]
> @.Complex_Name varchar(60) = '%',
> @.Building_Name varchar(60) = '%',
> @.Location_Descriptor varchar(60) = '%',
> @.House_Number_1 varchar(6) = '%',
> @.Street_name varchar(45) = '%',
> @.Locality_Name varchar(46) = '%',
> @.Surname varchar(50) = '%',
> @.FirstName varchar(50) = '%',
> @.Position varchar(50) = '%',
> @.TradingName varchar(255) = '%',
> @.CompanyName varchar(255) = '%',
> @.ACN char(20) = '%',
> @.ABN char(20) = '%',
> @.SiteName varchar(255) = '%',
> @.SiteAncillaryID int =0,
> @.ErrorCode int OUTPUT
>
> AS
> SET NOCOUNT ON
> SELECT POIC From
> (Select POIC
> From tblpoic POICP
> JOIN tblPerson PERSON
> on POICP.PersonID=PERSON.PersonID
> Where
> PERSON.[Surname] like '%' + @.Surname + '%' AND
> PERSON.[FirstName] like '%' + @.FirstName + '%' AND
> PERSON.[Position] like '%' + @.Position + '%'
> Union
> Select POIC
> From tblpoic POICO
> JOIN tblOrganisation ORGANISATION
> on POICO.OrgID=ORGANISATION.OrgID
> Where
> ORGANISATION.[TradingName] like '%' + @.TradingName + '%' AND
> ORGANISATION.[CompanyName] like '%' + @.CompanyName + '%' AND
> ORGANISATION.[ACN] like '%' + @.ACN + '%' AND
> ORGANISATION.[ABN] like '%' + @.ABN + '%'
> UNION
> IF @.SiteAncillaryID>0
> Begin
> Select POIC
> From tblpoic POICS
> JOIN tblSite SITE
> on POICS.SiteID=SITE.SiteID
> WHERE
> SITE.[SiteName]>@.SiteName
>
> and SITE.[SiteName]=@.siteAncillaryID
> Union
> END
>
>
> Select POIC
> From tblpoic POICA
> JOIN tbladdress ADDRESS
> ON POICA.AddressID = ADDRESS.AddressID
> where
> ADDRESS.[Complex_Name] like '%' + @.Complex_Name + '%' AND
> ADDRESS.[Building_Name] like '%' + @.Building_Name + '%' AND
> ADDRESS.[Location_Descriptor] like '%' + @.Location_Descriptor + '%' AN
D
> ADDRESS.[House_Number_1] like '%' + @.House_Number_1 + '%' AND
> ADDRESS.[Street_name] like '%' + @.Street_name + '%' AND
> ADDRESS.[Locality_Name] like '%' + @.Locality_Name + '%' ) as TEMP
> Order by POIC
>
> -- Get the Error Code for the statement just executed.
> SELECT @.ErrorCode=@.@.ERROR
> -- Get the IDENTITY value for the row just inserted.
> GO
>
>|||> Standard SQL is the embedded statements that "talk" to the query processor,
> the Selects, Updates, Inserts, and Deletes... The T=SQL constructions, (If
,
> While, Begin End, Declare @.Variable, etc.) can be used only Outside of SQ
L
> Statements not inside of one.
So you think that t-sql is use to provide control of flow for sql?
Brings up a few interesting questions - like what is sql?
Actually t-sql is the version of sql implemented on sql server which
includes differences and extensions to any ansi standard.
All control of flow, variable declaration, select statements are t-sql.
p.s. A case statement is usually what people want when they try to use an if
in a select statement but I don't think it is in this case.|||Nigel,
As I'm sure you understand, I'm Just trying to explain why "If" cannot be
used inside a "SQL statement". There is a distinction between those
Statements which Select, Insert, update or delete data, and the control flow
statements which they are embedded in...
"If" is a T-SQL Control-FLow construct, and is not useable within a "SQL"
Statement (a Select/Inert/Update/Delete). Although I cannot find
specifically where this distinction is made in the defintitions, or what the
exact words are to describe it, it is exactly pertinent to the issue the
individual was having...
"Nigel Rivett" wrote:

> So you think that t-sql is use to provide control of flow for sql?
> Brings up a few interesting questions - like what is sql?
> Actually t-sql is the version of sql implemented on sql server which
> includes differences and extensions to any ansi standard.
> All control of flow, variable declaration, select statements are t-sql.
> p.s. A case statement is usually what people want when they try to use an
if
> in a select statement but I don't think it is in this case.|||I'll agree that if is a control of flow statement.
It is also a t-sql statement just like select.
The problem is your assertion that select is an sql statement rather than a
t-sql statement. The distinction is made in defining control of flow
statements.
If you are looking for something that says if is t-sql and select sql then
you won't find it because it's not correct.
You can say that select is part of the ansi standard sql definition and is
also implemented in t-sql.
"CBretana" wrote:
> Nigel,
> As I'm sure you understand, I'm Just trying to explain why "If" cannot
be
> used inside a "SQL statement". There is a distinction between those
> Statements which Select, Insert, update or delete data, and the control fl
ow
> statements which they are embedded in...
> "If" is a T-SQL Control-FLow construct, and is not useable within a "SQL"
> Statement (a Select/Inert/Update/Delete). Although I cannot find
> specifically where this distinction is made in the defintitions, or what t
he
> exact words are to describe it, it is exactly pertinent to the issue the
> individual was having...
>
> "Nigel Rivett" wrote:
>|||Yes, I agree... My semantic mistake was using the Acronym "SQL" to refer to
just those statements which modify or retrieve Data... I'm not sure there is
a phrase or acronym which makes this distinction, but "SQL Statements"
seemed a viable choice, given the issue the user was experiencing...
But you are correct, Thanks.
"Nigel Rivett" wrote:
> I'll agree that if is a control of flow statement.
> It is also a t-sql statement just like select.
> The problem is your assertion that select is an sql statement rather than
a
> t-sql statement. The distinction is made in defining control of flow
> statements.
> If you are looking for something that says if is t-sql and select sql then
> you won't find it because it's not correct.
> You can say that select is part of the ansi standard sql definition and is
> also implemented in t-sql.
> "CBretana" wrote:
>|||Since you're arguing semantics, it would be more accurate to say that the IF
statement is not part of the DML language elements in mickeysoft's T-SQL
implementation and thus cannot be used in a DML statement like Select,
Insert, Update or Delete.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:3385D49B-2CBB-4EC3-B8F1-3CB31E95E39E@.microsoft.com...
> Yes, I agree... My semantic mistake was using the Acronym "SQL" to refer
> to
> just those statements which modify or retrieve Data... I'm not sure there
> is
> a phrase or acronym which makes this distinction, but "SQL Statements"
> seemed a viable choice, given the issue the user was experiencing...
> But you are correct, Thanks.
>
> "Nigel Rivett" wrote:
>