IF - ELSEIF - 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)

IF - ELSEIF

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2007-03-12 : 13:21:43

Can I use one parameter in stored procedure for changing WHERE clouse...something like this ...???

CREATE PROCEDURE Prodaja_SestUPC_Danas
@date_from datetime,
@date_to datetime,
@trazilica nvarchar(30),
@ReciNum integer,
@ParamC integer
AS
BEGIN
SET NOCOUNT ON
INSERT INTO arhPOSInvoiceItems_Sales ( [Description], [Item Number], Quantity, [UPC Code], [Quantity In Stock], [Entry Date],RepID )
SELECT arhPOSInvoiceLineitems.[Description], arhPOSInvoiceLineitems.[Item Number],
Sum(arhPOSInvoiceLineitems.Quantity) AS [SumOfQuantity], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri, @ReciNum
FROM arhPOSInvoiceLineitems
INNER JOIN Inventory
ON arhPOSInvoiceLineitems.[Item Number] = Inventory.[Item Number] And arhPOSInvoiceLineitems.[Ship Date] Between @date_from And @date_to

if @ParamC = 0 then WHERE Inventory.[UPC Code] Like @trazilica
elseif @ParamC = 1 WHERE Inventory.[Item Njumber] Like @trazilica
elseif @ParamC = 1 WHERE Inventory.[Discription] Like @trazilica

GROUP BY arhPOSInvoiceLineitems.[Description], arhPOSInvoiceLineitems.[Item Number], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
END

GO

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 13:38:37

Yes, just use a logical expression like this

WHERE (@ParamC = 0 AND Inventory.[UPC Code] Like @trazilica)
OR (@ParamC = 1 AND Inventory.[Item Njumber] Like @trazilica)
OR (@ParamC = 2 AND Inventory.[Discription] Like @trazilica)

ilimax
Posting Yak Master

164 Posts

Posted - 2007-03-12 : 16:22:01

quote:
Originally posted by snSQL

Yes, just use a logical expression like this

WHERE (@ParamC = 0 AND Inventory.[UPC Code] Like @trazilica)
OR (@ParamC = 1 AND Inventory.[Item Njumber] Like @trazilica)
OR (@ParamC = 2 AND Inventory.[Discription] Like @trazilica)

Looks so simple and works fine. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources