SQL Example: Stored procedures

The first question: there is a funny math problem in Marx's manuscript: There are 30 people, including men, women and children, in a restaurant to eat a total of 50 shillings, each man spent 3 shillings, each woman spent 2 shillings, each child spent 1 shillings, ask the men, women and children how many people?

1 CREATE PROCEDURE [dbo].[T1]2  as3 BEGIN4 /*5 There is a funny math problem in Marx's manuscript: There are 30 people, including men, women and children, eating in a restaurant.6 A total of 50 shillings, each man spent 3 shillings each, each woman spent 2 shillings, each child spent 1 shillings, asked how many men, women and children? 7 Solution Equation Set8 write the procedure and use the poor lifting method to find out the result. 9 */Ten     DECLARE @M INT = 0,@W INT = 0,@c INT = 0 One     CREATE TABLE# (MINTWINTCINT) A      while @M<= - -         BEGIN -              while @W<= - the                 BEGIN -                     SET @c =  - - @M - @W -                     IF 3 * @M + 2 * @W + @c =  - -                         BEGIN +                             INSERT  into#VALUES(@M,@W,@c) -                         END +                     SET @W = @W + 1 A                 END at             SET @M = @M + 1 -         END -     SELECT *  from # -     DROP TABLE # - END - GO

The second question: write the program, according to the following formula to find the value of E. Requires two methods of calculation:
1) for loop, calculate the first 50 items
2) while loop until the value of the last item is less than 10-4

1 CREATE FUNCTION [dbo].[find factorial]2(@i  as BIGINT 3 )4 RETURNS BIGINT5  as6 BEGIN7     DECLARE @x BIGINT = 1,@y BIGINT = 18      while @x <= @i9         BEGINTen             SET @y = @y * @x One             SET @x = @x + 1 A         END -   RETURN @y - END the GO

View Code

1 CREATE PROCEDURE [dbo].[T002]2   @FF  as bit = 03  as4 BEGIN5 /*6 Write a program that evaluates the value of E according to the following formula. Requires two methods of calculation:7 1) for loop, calculate the first 50 items8 2) While loop until the value of the last item is less than 10-49 there is no for loop in Ps:sql server, 50! It's going to be out of BigInt's range, 20 meaning .Ten */ One     DECLARE @i tinyint = 1,@x FLOAT = 1.0,@f FLOAT = 1.0 A     IF @FF = 0 -         BEGIN -              while @i <=  - the                 BEGIN -                     SET @f = @f + 1.0 /Dbo.[T002. Finding factorial](@i) -                     SET @i = @i + 1 -                 END +             SELECT @f -         END +     ELSE A         BEGIN at              while @x >= 0.0001 -                 BEGIN -                     SET @x = 1.0 /dbo. To find factorial (@i) -                     SET @f = @f + @x -                     SET @i = @i + 1 -                 END in             SELECT @f -         END to END + GO

The third problem: Enter a number from the keyboard (unlimited number of digits), using the loop to program the sentence to determine and output the number of digits.

1 CREATE PROCEDURE [dbo].[T3]2   @Num  as varchar( $)3  as4 BEGIN5 /*6 Enter a number from the keyboard (unlimited digits), and use the loop to programmatically determine and output the number of digits. 7 */8     DECLARE @S CHAR(1)= '1',@i INT = 19      while @S <> "'Ten         BEGIN One             SET @S = SUBSTRING(@Num,@i,1) A             IF @S <> "' -                 BEGIN -                     SET @i = @i + 1 the                 END -         END -     SELECT @i - 1,LEN(@Num) - END + GO

View Code

SQL Example: Stored procedures