|
|
Posted by Erland Sommarskog on 05/26/07 20:26
Khafancoder (khafancoder@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|