January 24, 2012 at 2:25 pm
I have this part of a select statement.
SELECT ProductItems.ProductItemID, ProductItems.ProductItemName + ' ' + CONVERT(varchar, ProductItems.ProductItemSize) + ' ' + ProductUnits.ProductUnitName + ' ' + ProductStockingTypes.ProductStockingTypeName AS ProductItemFullName
FROM ProductItems INNER JOIN
ProductUnits ON ProductItems.ProductUnitID = ProductUnits.ProductUnitID INNER JOIN
ProductStockingTypes ON ProductItems.ProductStockingTypeID = ProductStockingTypes.ProductStockingTypeID
It is used in many stored procedures that select and join on much more than shown.
I think its silly to do this over and over again like this.
So I thought to myself well half of these stored procedure use an XML data type and uses this Where clause.
WHERE ProductItems.ProductID in (SELECT T.c.value('@ID', 'integer') As result FROM @ProductIDs.nodes('/Root/Key') as T(c))
So maybe i can make a UDF that accepts XML and returns a table variable and i can join on @TableVarible = GetProductFullName(@ProductXML). Then join that table variable on ID an use the productfullname that way.
I was just wondering whats the easiest way to not have that concatenated select statement through out our stored procedures.
Thanks a lot,
Keith
January 24, 2012 at 3:20 pm
Probably the way you are doing it now is the best and most efficient considering what result you need.
You could use a common table expression to produce the value then join to that but I doubt it would produce any different results besides looking better.
The probability of survival is inversely proportional to the angle of arrival.
January 24, 2012 at 3:51 pm
I ended up writing a Scalar Function that take the ProductItemID and returns the full name.
CREATE FUNCTION [dbo].[GetProductItemFullName]
(
@ProductItemsID INT
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Result VARCHAR(255)
DECLARE @ProductUnitType VARCHAR(255)
SELECT @Result = ProductItems.ProductItemName + ' ' + CONVERT(varchar, ProductItems.ProductItemSize) + ' ' +
ProductStockingTypes.ProductStockingTypeName, @ProductUnitType =
CASE ProductUnits.ProductUnitVisible
WHEN '0' THEN ' '
WHEN '1' THEN ProductUnits.ProductUnitName
END
FROM ProductItems INNER JOIN
ProductUnits ON ProductItems.ProductUnitID = ProductUnits.ProductUnitID INNER JOIN
ProductStockingTypes ON ProductItems.ProductStockingTypeID = ProductStockingTypes.ProductStockingTypeID
WHERE (ProductItems.ProductItemID = @ProductItemsID)
RETURN @Result
END
I was wondering how bad is that really, would the best way to do this be to have your function take parameters of all 4 Columns and do it that way? What are the benefits of this ? What are not ? Is it really going to matter that much ? I think in a select statement the database is going to take a hit for every product. But i don't spend all day in SQL Server so i don't know. Any constructive criticism is greatly appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply