SQL query to create stored procedure

Updated at 21-Aug-2022, By samar

SQL query to create stored procedure

Hello everyone, in this post we will look at how to solve "SQL query to create stored procedure" in programming.

How to create stored procedure and call the procedure using sql queries in phpmyadmin? Code example to create stored procedure using mysql query in phpmyadmin.
  • SQL query to create stored procedure

    Create table and insert record

    Just copy paste the data and execute the query.

    CREATE TABLE `posts` (
      `id` bigint(20) UNSIGNED NOT NULL,
      `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `slug` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
      `is_shared` tinyint(2) NOT NULL DEFAULT '0',
      `parent_id` int(20) DEFAULT NULL,
      `user_id` int(20) NOT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    --
    -- Dumping data for table `posts`
    --
    
    INSERT INTO `posts` (`id`, `title`, `slug`, `body`, `is_shared`, `parent_id`, `user_id`, `created_at`, `updated_at`) VALUES
    (11, 'Labore do commodi et', NULL, '', 0, NULL, 1, '2021-10-12 23:40:23', '2021-10-12 23:40:23'),
    (12, 'Accusantium nostrum', NULL, '', 0, NULL, 4, '2021-10-12 23:40:43', '2021-10-12 23:40:43'),
    (13, 'Enim assumenda eveni', NULL, 'Iure quo nihil paria', 0, NULL, 0, '2021-10-12 23:41:22', '2021-10-12 23:41:22'),
    (14, 'Tenetur similique ve', NULL, 'Cupidatat veniam re', 0, NULL, 0, '2021-10-13 05:25:45', '2021-10-13 05:25:45'),
    (15, 'Saepe vero omnis ea', NULL, 'Dicta ducimus magna', 0, NULL, 0, '2021-10-13 05:42:38', '2021-10-13 05:42:38'),
    (16, 'Sit quibusdam qui au', NULL, 'Magni aut et velit e', 0, NULL, 1, '2021-10-13 05:52:51', '2021-10-13 05:52:51'),
    (17, 'Est commodo laborios', NULL, '<p>Lorem ipsum</p>', 0, NULL, 0, '2021-10-13 06:32:49', '2021-10-13 06:32:49'),
    (18, 'Fugiat cillum asper', NULL, '<p>Lorem ipsum data</p>', 0, NULL, 0, '2021-10-13 06:34:29', '2021-10-13 06:34:29'),
    (19, 'Id dolorum rerum qui', NULL, '<h2>Hi, i am samar</h2>', 0, NULL, 0, '2021-10-13 22:57:58', '2021-10-13 22:57:58'),
    (21, 'Random text', NULL, 'Random body', 1, 13, 0, '2021-11-02 03:54:39', '2021-11-02 03:54:39'),
    (22, '', '', 'Lorem ipsum body', 0, NULL, 1, '2022-01-07 22:14:02', '2022-01-07 22:14:02'),
    (23, 'lorem ipsum text', 'lorem-ipsum-text', 'Lorem ipsum body', 0, NULL, 1, '2022-01-07 22:14:56', '2022-01-07 22:14:56'),
    (24, 'lorem ipsum text', 'lorem-ipsum-text', 'Lorem ipsum body', 0, NULL, 1, '2022-01-07 22:40:42', '2022-01-07 22:40:42');
    
    --
    -- Indexes for dumped tables
    --
    
    --
    -- Indexes for table `posts`
    --
    ALTER TABLE `posts`
      ADD PRIMARY KEY (`id`);
    
    --
    -- AUTO_INCREMENT for dumped tables
    --
    
    --
    -- AUTO_INCREMENT for table `posts`
    --
    ALTER TABLE `posts`
      MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
    COMMIT;
    
    

    Create procedure using sql query to get the posts created by a user.

    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `get_posts_by_userid`(IN idx int)
    BEGIN
            SELECT * FROM posts WHERE user_id = idx;
            END$$
    DELIMITER ;
    

    SQL query code to call a procedure

    SET @p0='1'; CALL `get_posts_by_userid`(@p0);
    

Back to code snippet queries related sql