Hi i need a sql stored procedure
i am currently using the select statement below, but i need to use a if exsits i think.
i want
select Alo_Qty
from table
where Alo_UserID = @UserID and Alo_Stock_Code = @StockCode
but if there is no record in the table that matches then i need to set Alo_Qty = 0
any ideas what i need to do?
Ok,
I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.
Dbo.Products – This table holds product names and information.
Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID
Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.
Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID
Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.
Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are “required� (no product will be returned that does not have what is in the list).
I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.
If any of you know how to do the above with a subquery of some kind, please let me know.
Dave Larson