Posted by Khafancoder on 05/27/07 09:45

> There is one final question, I will have to ask: in the real Products
> table are there any columns beside the IDENTITY column that are unique
> within a store? If the answer is yes, then my answer is yes, you
> can do it set-based, and if you tell which version of SQL Server you
> are using, I can sketch a solution.

no, there isn't.
but isn't possible through StoreId & ProductId together ?

and finally, isn't any other alternative to do insert in UDFs ?
or executing an sp in a select statement ?

Thanks



On May 26, 11:26 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Khafancoder (khafanco...@gmail.com) writes:
> >> 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 presume then that the real tables are more complex than the mock-up
> posted.
>
> There is one final question, I will have to ask: in the real Products
> table are there any columns beside the IDENTITY column that are unique
> within a store? If the answer is yes, then my answer is yes, you
> can do it set-based, and if you tell which version of SQL Server you
> are using, I can sketch a solution.
>
> If the answer is no, you will have to run a loop and insert the
> products one-by-one. You should still be able to copy all parts for
> a product in one go, as long as you don't need the part it anywhere.
> If this sounds clunky to you, it is because the design is not optimal.
>
> > 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
>
> Right. A user-defined function cannot change database state, so
> that's a non-starter.
>
> --
> 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]