SQL Developer – Creating a Stored Procedure – MSSQL (SQL server) and MySQL

Stored procedure is a code stored in the database and executed on the server. The value is in many aspects, but can summarised as a backend operation running on the DB server.

If you like to run the code, user command lines or install SQL client tools:

  • MSSQL – SQL Server Management Studio or Visual Studio
  • MySQL – There are a few tools like MySQL client and MySQL Workbench.

    a few differences between MSSQL and MySQL:

    • MySQL – note the DELIMITER
    • MySQL – remember to close operations with ; (semicolon)
    • Auto Number: IDENTITY (MSSQL) -> AUTO_INCREMENT (MySQL)
    • AUTO_INCREMENT (MySQL) must be in the PRIMARY KEY

    SQL Server

    * Run each piece of code in a separate query (if using SQL management studio)

    For preparations, create the DB, table, index and enter some sample data.

    
    
    -- step 1 - create a database
    CREATE DATABASE otp;
    
    
    
    
    
    
    -- step 2 - use the new DB
    USE otp;
    -- step 3 - create a table
    CREATE TABLE
    	otp_demo
    	(
    		id INT IDENTITY,
    		p_name VARCHAR(200),
    		p_city VARCHAR(200) DEFAULT 'Boston',
    		p_state	VARCHAR(100) DEFAULT 'MA'
    	
    	)
    ;
    -- step 4 - create an index
    CREATE INDEX
    	inx_otp_demo
    ON 
    	otp_demo
    	(
    		p_name,
    		p_city,
    		p_state
    	)
    ;
    INSERT INTO otp_demo
    	(p_name,
    	p_city,
    	p_state
    	)
    VALUES
    	('Dan','Brookline', 'MA'), 
    	('Dana','Boston', 'MA'),
    	('Carla','Newton', 'MA'),
    	('Deborah','Cambridge', 'MA')
    
    
    
    
    

    Create a simple stored procedure

    
    
    CREATE PROCEDURE otp_count_names
    AS
    	BEGIN
    		
    		SELECT 
    			COUNT(*)
    		FROM
    			otp_demo
    	
    	END	
    
    
    

    Run the stored procedure.

    
    
    EXEC otp_count_names
    
    
    

    Result: 4

    MySQL

    * Note there are small differences

    • IDENTITY -> AUTO_INCREMENT
    • AUTO_INCREMENT must be in the PRIMARY KEY

    For preparations, create the DB, table, index and enter some sample data.

    
    
    -- step 1 - create a database
    CREATE DATABASE otp
    
    
    
    
    
    
    USE otp;
    -- step 3 - create a table
    CREATE TABLE
    	otp_demo
    	(
    		id INT AUTO_INCREMENT,
    		p_name VARCHAR(200),
    		p_city VARCHAR(200) DEFAULT 'Boston',
    		p_state	VARCHAR(100) DEFAULT 'MA',
    	PRIMARY KEY (id)
    	
    	)
    ;
    -- step 4 - create an index
    CREATE INDEX
    	inx_otp_demo
    ON 
    	otp_demo
    	(
    		p_name,
    		p_city,
    		p_state
    	)
    ;
    INSERT INTO otp_demo
    	(p_name,
    	p_city,
    	p_state
    	)
    VALUES
    	('Dan','Brookline', 'MA'), 
    	('Dana','Boston', 'MA'),
    	('Carla','Newton', 'MA'),
    	('Deborah','Cambridge', 'MA')
    
    
    

    Create a simple stored procedure

    
    
    DELIMITER $$
    CREATE PROCEDURE otp_count_names()
    
    	BEGIN
    		
    		SELECT 
    			COUNT(*)
    		FROM
    			otp_demo;
    	
    	END $$	
    
    
    
    

    Run the stored procedure.

    
    
    CALL otp_count_names
    
    
    

    Result: 4

Boston SQL developer MySQL – write from a table to a text file

Write an output file from MySQL:



use test;
DELIMITER $$
DROP PROCEDURE IF EXISTS write_output_file;
CREATE PROCEDURE write_output_file(flag varchar(1))
       BEGIN
	DECLARE s  VARCHAR(255);
	SET s='this is an example';	
	IF flag	='r' THEN 
		-- Note you can't overwrite an existing file
		SELECT s INTO OUTFILE '/home/USER/output.txt'; -- write to a file
	ELSE
		SELECT s; -- print to the console
		
	END IF;
       END$$		
   DELIMITER ;

-- Run this procedure
call write_output_file('r')


Database Developer – What Does it Take to Be One?

An SQL database developer specializes in programming that focuses on writing database-specific codes. Structured Query Language or SQL expertise is a sought-after skill in many programming positions since SQL is vital in accessing numerous database systems.

How to Become a Database Developer

Programming experience is a must if you want to be an SQL developer. You need to be familiar with Java, C#, and other general languages. It is also important to have sufficient database knowledge and experience as well as fundamental security practices. An SQL developer needs to have experience with business intelligence and reporting tools such as SAP Crystal Reports, Microsoft SQL server, and Cognos. There are Database developer meetings in Boston which share skills such as the ability to effectively communicate and collaborate with technical and non-technical end-users.

Becoming an SQL database developer needs you to be a programmer with database knowledge and experience so that you can successfully pursue this high-earning career.

Looking for a Database Developer in Boston MA area? Contact OT PROJECTS.

Looking for a software developer you can trust? ask my customers. (Boston)

Credible software developer and project manager.

If you’re looking for a self-sufficient person to develop and support your software needs, I’ll be happy to work with you.

I have a wide knowledge of both development and business understanding. You’ll find an open ear and good advises that already added business value to dozens of customers.

– Enhancements to e-Commerce systems.
(Looking for e-Commerce enhancements? Contact OT PROJECTS.)
– Automate business processes.
– Development of reports
– Improvements and tuning of Databases.
– Op-Dev operations.

Location: Boston, Cambridge North Shore, South Shore South NH area

References are available.

Can work on-site

– ERP and CRM customization.
– Website development and enhancements.
– Innovative E-commerce Information Technologies expertise E-commerce Business Processes
– Web Technologies Development and Maintenance.
– Database Development and Maintenance Services
– Data and Site Migration Services
– Migrate Excel and MS Access to Databases, Databases to Excel.

email for more information
———————————–
Shopping carts, databases, Oracle, MySQL,T-SQL, sql server, sql, python, php, asp classic, .NET,programmer, developer, development, website, web design, javascript, vbscript, maintenance, data migration, excel, ms access, ADO,IIS, apache web security, amazon, AWS, EC2 ,Django, Flask, Cloud, Catalog, Pyramid, PLM, PDM

 

Looking for a software developer you can trust? You just found your Contact Your Trusted Developer in Boston.

A Closer Look at E-commerce Development for Medical Supply

Healthcare and medical supplier requirements in connection to e-commerce may differ from business to business e-commerce. One of the main differences is in the content management needs where the product information could vary based on the size of the supplier and its buyer. Therefore, there is a difference in the content management level requirements. Medical suppliers may have different product information while buyers’ needs could be diverse in terms of the procurement and materials management.

Healthcare e-commerce websites are the main source of up to 15 percent of the total revenues for healthcare supplier sectors. Many suppliers move to B2B e-commerce processes after they do their analysis on their ROI or return on investment, resulting in up to 30% positive results. For more mature and professional players in the industry, the results are even better with their businesses receiving bigger web orders that could potentially reach between 60 and 65 percent increase.

SQL Developer in Boston

The best practices of programming language include producing effective and healthy T-SQL codes. SQL developer considers creating in sets or relational terms when you write your T-SQL code but it would require a basic background in procedural programming. Adopt the correct mindset in writing your bode such as not getting stuck to using new language as your code extension. Have a better understanding of the T-SQL concepts that relate to rows, columns, and tables, which have different significance to file, record, and field.

SQL developer can use NULLS which represent a missing value and applicable or inapplicable. NULL refers to the symbol for the missing value and not a value per se. Be mindful of the NULLs when you write your T-SQL codes along with their interaction that is considered special and at times counter-intuitive. Always remember that in writing the T-SQL codes, you need to consider if the data could possibly interact with NULLs and if you should make specific tests for NULLs.