55602

mysql concat and insert into not working

Question:

i have a code in stored procedure which adds "PID-" to the id number, so if id number is 1, the result shoulb be PID-1. but it's not working.

Here's the code:

DROP PROCEDURE `inserproducts`// CREATE DEFINER=`root`@`localhost` PROCEDURE `inserproducts`(pid int,pname varchar(50),pdesc varchar(50),psupp varchar(50),pdate date,pquant int) begin insert into products(productid,productname,proddescription,supplier,lastpurchasedate,quantityleft) values(select concat('PID',pid,pname),pdesc,psupp,pdate,pquant)); select pid=last_insert_id(); end

how can i join insert into and concat together? Please help me with this one.

Answer1:

Use INSERT INTO...SELECT

insert into products(productname,proddescription,supplier,lastpurchasedate,quantityleft) select concat('PID',pid,pname),pdesc,psupp,pdate,pquant

You can omit the column productid if it is an AUTO_INCREMENT column.

<em>I was wondering why you need to execute select pid=last_insert_id(); when pid is an IN parameter</em>.

<strong>UPDATE 1</strong>

DROP PROCEDURE `inserproducts`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `inserproducts` ( pid int, pname varchar(50), pdesc varchar(50), psupp varchar(50), pdate date, pquant int ) begin insert into products (productname, proddescription, supplier, lastpurchasedate, quantityleft) select concat('PID',pid,pname), pdesc, psupp, pdate, pquant; select last_insert_id(); end$$ DELIMITER ;

Answer2:

just following up... if you specify values you don't need the select - there's probably a slight performance gain by just using values:

DROP PROCEDURE `inserproducts`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `inserproducts` ( in pid int, in pname varchar(50), in pdesc varchar(50), in psupp varchar(50), in pdate date, in pquant int ) begin insert into products (productname, proddescription, supplier, lastpurchasedate, quantityleft) values (concat('PID',pid,pname), pdesc, psupp, pdate, pquant ); select last_insert_id(); end$$ DELIMITER ;

I'd leave select alone unless you actually need data from another table...

Recommend

  • Error Code 1046: No database Selected
  • Insert and update in a cursor in SQL Server
  • T-SQL Statements to Grant Access and Create Account
  • how to create event trigger for create table or select into
  • Cannot assign method group to an implicitly-typed local variable in asp.net,linq,c#
  • How to break insert query, before insert trigger mysql
  • How to insert data into sqlite database just one time
  • MySQL select all entries between two dates regardless of year
  • Adding XElement to XML file using Linq to XML
  • Finding perfmon counter id via winreg
  • SecurityException Permission Denial MediaProvider READ_EXTERNAL_STORAGE
  • Using Google Api: Speech To Text on PC Version
  • fetch all links under/in a specific class-selenium webdriver (java)
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • How to find data from last week in MySQL
  • Is there a way to get the process ID of a console program I've just started in the background?
  • Spree, Ruby on Rails - Add to cart multiple variants of the same product
  • How to resolve “ Multiplicity is not valid in Role” error?
  • In App Purchase functionality is not present in your binary
  • SCons libraries and sub-libraries
  • Shopify API CARTS - Changing line_item line_price for price Override
  • How can I make a 100% stacked chart with nvd3?
  • Find VMID for running instance
  • CORS with socket.io
  • Returning the auto incrementing value after an insert using slick
  • Identifier too long in Oracle
  • mave 3.2 not able to access local nexus instance return 502 code
  • rewrite uppercase url to lowercase url htaccess
  • Tomcat memory Leak
  • Sending HTML Form Data to Spring REST Web Service
  • Eliminate partial duplicate rows from result set
  • JBoss External Properties Files in Classpath
  • htaccess add www if not subdomain, if subdomain remove www
  • Cannot resolve symbol 'MyApi'
  • Jenkins: How To Build multiple projects from a TFS repository?
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Sorting a 2D array using the second column C++
  • How can i traverse a binary tree from right to left in java?
  • java string with new operator and a literal