Posted by Khafancoder on 05/26/07 09:54

> But of course, I don't know what your real case is like. Maybe you are
> only using stores, products and parts for the sake of the example?

exactly!
so i can't change the design and also i can't disable identity
insertion !!

i'm trying to take an alternative way, something lik this :

--disable check constrains

INSERT INTO Parts
(ProductId, PartName, PartDesc)
SELECT
dbo.CopyProduct(ProductId, @DestinationStoreId) , PartName, PartDesc
FROM Parts WHERE StoreId=@StoreId

--enable check constrains

and CopyProduct functions is supposed to copy requested ProductItem
and return it's Id,
but i can't do that in UDF !!

CREATE FUNCTION CopyProduct(@ProductId bigint, @DestinationStoreId
bigint) RETURNS bigint
AS
BEGIN

INSERT INTO Products (StoreId, ProductName)
SELECT @DestinationStoreId, ProductName FROM Products WHERE
ProductId=@ProductId

RETURN SCOPE_IDENTITY()
END


i think it should solve the problem, but because of sqlserver
restriction i can't do that in a function !!

any idea ?






On May 26, 11:50 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Khafancoder (khafanco...@gmail.com) writes:
> > because copied products or parts info may needed to be changed by each
> > owner.
> > so, i will consider disabling identity insertion.
>
> I still don't think this is a good design. Reasonably, there are
> inherit properties with a product that does not depend on the store.
>
> I think you need a new table ProductStores:
>
> CREATE TABLE StoreProducts(StoreId bigint NOT NULL,
> ProductId bigint NOT NULL,
> CONSTRAINT pk_ProductStores PRIMARY KEY(StoreId,ProductId))
>
> This table can then be augmented with columns that the store owner can
> set as he pleases. If needed, you could also have a StoreProductParts,
> with (StoreId, ProductId, PartNo) as key and with (StoreId, ProductId)
> as foreign key to StoreProducts and (ProductId, PartNo) as foreign key
> to Parts. But it seems funny to me that the same product would have
> different parts in different stores.
>
> But of course, I don't know what your real case is like. Maybe you are
> only using stores, products and parts for the sake of the example?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]