How to split number and digit in Sql Stored Procedure

[Maideen]

Hi

Pls advice me the following calculation in stored Procedure
my scenario is packing the magazine

Example:

Order = 6042 copies. 6042 have to be packed by 20 each. so 302 bundle. balance 2 copies.
If I divide 6042 /20 Result is 302.10 . How can store 302 and 2 in different column
that mean 302 is one column and 2 is another.

It is also differ the packing qty. Like some time 20 or 30 or 50 or 10
Also copies are also differ upto 9999 copies

Currently My SP is

UPDATE [dbo].[SOMaster_TEMP] SET [BSTD] = [Qty] /[STDB]
UPDATE [dbo].[SOMaster_TEMP] SET [BEND] =[Qty] / [STDB]

Qty is copies = 6042
STDB = 20 per bundle
BSTD is coming 302.10
BEND is also 302.10

Pls advice me
Thank you

Maideen

[WebspoonUK]

Try using the SQL Modulus function (%): -

UPDATE T
SET BSTD = @Qty / @StdB, BEND = @Qty % @StdB
FROM dbo.SOMaster_TEMP T
WHERE T.PK = @Id

Be sure to add a WHERE clause or all records in your table will be overwritten with the same calculation.

Happy coding!

[Amr.Mohammad87]

Use the moduls operator and you will get the result of 2, the result you want. If you want to split something in SQL Server try to use substring method and notice that this method used with strings only.