sql server stored procedure
CREATE PROCEDURE spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
AS
BEGIN
SELECT @EmployeeCount = COUNT(Id)
FROM tblEmployee
WHERE Gender = @Gender
ENDsql server stored procedure
CREATE PROCEDURE spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
AS
BEGIN
SELECT @EmployeeCount = COUNT(Id)
FROM tblEmployee
WHERE Gender = @Gender
ENDprocedure in sql
-Stored procedure is a group of SQL
statements that has been created
and stored in the database.
-A stored procedure will accept input
parameters so that a single procedure
can be used over the network by several
clients using different input data.
-A stored procedures will reduce
network traffic and increase the performance.
If we modify a stored procedure all the
clients will get the updated stored procedure.
Sample of creating a stored procedure
CREATE PROCEDURE test_display AS
SELECT FirstName, LastName FROM tb_test
EXEC test_displaycreate proc
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GOsql procedure
CREATE OR REPLACE PROCEDURE my_scheme.my_procedure(param1 IN VARCHAR2) IS
cnumber NUMBER;
BEGIN
cnumber := 10;
INSERT INTO my_table (num_field) VALUES (param1 + cnumber);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001, 'An error was encountered - '
|| sqlcode || ' -ERROR- ' || sqlerrm);
END;Stored-Procedure
@EnableJpaRepositories
@ComponentScan
@Configuration
public class AppConfig {
@Bean
public DataSource dataSource() {
return new EmbeddedDatabaseBuilder()
.setType(EmbeddedDatabaseType.HSQL)
.addScript("create-tables.sql")
.addScript("procedure.sql")
.setSeparator("/;")
.build();
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean factory =
new LocalContainerEntityManagerFactoryBean();
factory.setPersistenceProviderClass(HibernatePersistenceProvider.class);
factory.setDataSource(dataSource());
Properties properties = new Properties();
properties.setProperty("hibernate.hbm2ddl.auto", "update");
factory.setJpaProperties(properties);
return factory;
}
@Bean
public PlatformTransactionManager transactionManager() {
JpaTransactionManager txManager = new JpaTransactionManager();
txManager.setEntityManagerFactory(entityManagerFactory().getObject());
return txManager;
}
}Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
