Can I build WHERE clause dynamically in a Stored Procedure in SQL Server 2005?

Hello,
I am using SQL Server 2005 and I am just wondering if I can build the
WHERE clause dynamically. I only want to build WHERE class if the my
@.VIN count is of exactly 17 charecters. Here is my code
****************************************
*********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@.vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
if count(@.vin) = 17
begin
Where VIN = @.VIn
end
END
****************************************
***************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the
way I am doing it or if it is not possible what is the best way of
writing it?
Thanks
-LYou could do something like this:
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN|||Wow this is popular today.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Learner" <pradev@.gmail.com> wrote in message
news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
> Hello,
> I am using SQL Server 2005 and I am just wondering if I can build the
> WHERE clause dynamically. I only want to build WHERE class if the my
> @.VIN count is of exactly 17 charecters. Here is my code
> ****************************************
*********
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
> @.vin varchar(17)
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT V.vin
> ,C.FirstName
> ,C.LastName
> ,C.AccountNum
> ,D.DealerShipName
> ,Contrt.MaturityDate
> FROM ContractInfo Contrt
> Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
> Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
> Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
> if count(@.vin) = 17
> begin
> Where VIN = @.VIn
> end
> END
> ****************************************
***************
> But when I parse the above stored proc its throwing this message
>
> ********
> Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
> 33
> Incorrect syntax near the keyword 'Where'.
> ********
>
> If it is possible to dynamically build WHERE clause please correct the
> way I am doing it or if it is not possible what is the best way of
> writing it?
> Thanks
> -L
>|||> where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN
At first this struck me as incorrect, but technically it's not, just not in
the format I'd typically expect, e.g. compare the readability to:
WHERE VIN = CASE LEN(@.vin) WHEN 17 THEN @.Vin ELSE VIN END|||Hello,
Thank you all for the above suggestions and WHERE VIN = CASE LEN(@.vin)
WHEN 17 THEN @.Vin ELSE VIN END works.
I am not sure if we build this in SQL Server 2000 but SQL Server 2005
rocks!
Thanks
-L|||Question "du jour". :-)
Would you like a nice bottle of Merlot with that?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueXCca6RGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Wow this is popular today.
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html
>
>
> "Learner" <pradev@.gmail.com> wrote in message
> news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
>|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||"Learner" <pradev@.gmail.com> wrote in message
news:1142376483.643521.89440@.v46g2000cwv.googlegroups.com...
> Well why not :) By the way what do you mean by "du jour". ?
> Thanks
> -L
It's French.
http://en.wikipedia.org/wiki/Soup_du_jour|||>> I only want to build WHERE class if the my @.VIN count is of exactly 17 ch
arecters.<<
This is crazy. A VIN is CHAR(17) by definition; read your ISO
standard. Furthermroe, it has a very fixed format for each position in
the string. What you want is a CHECK() constraint or procedure that
will validate your input
NOTES ON VIN:
In North America, a system is used that is far more stringent than the
ISO Standards but is "backward compatible." Here, the VIN is divided
into four sections:
The first three characters shall uniquely identify the manufacturer,
make and type of vehicle (with the same exception of manufacturers that
produce less than 500 vehicles). Effectively, this is the WMI. There
are indeed examples of manufacturers who have more than one WMI that
use the third character as a code for a vehicle category (for instance
bus or truck). Just as often however this is not the case;
The second section consists of five characters (VIN positions 4-8) and
identifies the attributes of the vehicle. For each type of vehicle
(passenger cars, MPV's, trucks, buses, trailers, motorcycles,
incomplete vehicles other than trailers), different information is
required. For cars, MPV's and light trucks it is required that the
first two characters of this section are alphabetic, the third and
fourth shall be numeric and the fifth alphanumeric. This section is the
VDS in ISO 3779 but there it comprises another position of the VIN;
The third section consists of one character which is the check digit,
calculated over the other 16 characters of the VIN. This character can
be numeric or the letter X;
The fourth section consists of eight characters on positions 10-17 of
the VIN. The last five shall be numeric for cars, MPV's and light
trucks and the last four shall be numeric for all other vehicles. The
first character represents the vehicle model year, the second character
represents the plant of manufacture. The third through eighth
characters are a sequential production number (for manufacturers
producing more than 500 vehicles per year). For other manufacturers,
the sixth, seventh and eight positions represent the sequential
production number.
This section confirms to the VIS in ISO 3779.
A portion of the VIN is the WMI (World Manufacturer Identifier) Code.
SAE assigns this code to U.S. vehicle manufacturers. If you are a U.S.
manufacturer, please contact:
Cathy Douds
WMI Coordinator
SAE International
400 Commonwealth Drive
Warrendale, PA 15096-0001
724.772.8511
724.776.4026 - fax
douds@.sae.org
Related Standards:
There are several standards available on VINs and WMIs:
SAE - J187 - Truck Vehicle Identification Numbers
SAE - J218 - Passenger Car Identification Terminology
SAE - J272 - Vehicle Identification Number Systems
SAE - J273 - Passenger Car Vehicle Identification Number System
SAE - J853 - Vehicle Identification Numbers
SAE - J1108 - Truck and Truck Tractor Vehicle Identification Number
Systems
SAE - J1044 - World Manufacturer Identifier
SAE - J1229 - Truck Identification Terminology
SAE - J1877 - Recommended Practice for Bar-Coded Vehicle Identification
Number Label
SAE J129 - Engine and Transmission Identification Numbers
ISO 3779 - Road vehicles - Vehicle identification number (VIN) Content
and structure
ISO 3780 - Road vehicles - World manufacturer identifier (WMI) code