>Hi,
>
>is there any other way to build the following condition on S.P.
>if @catcode <> ''
>begin
>select * from item where catcode = @catcode
>end
>
>if @catcode <> '' and @subcode <> ''
>begin
>select * from item where catcode = @catcode and subcode = @subcode
>end
>
>if @catcode = '' and @subcode <> ''
>begin
>select * from item where subcode = @subcode
>end
>
>best Regards
Try:
IF @catcode <> '' OR @subcode <> ''
BEGIN
SELECT * FROM Item
WHERE (@catcode = '' OR catcode = @catcode) AND
(@subcode = '' OR subcode = @subcode)
END
If you want ALL records if both variables are empty, just remove IF statement.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.