SQL query to create stored procedure

• 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