Actually if you create func: create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1; and view: create view h_parm as select * from sw_hardware_big where unit_id = p1() ; Then you can call a view with a parameter: select s.* from (select @p1:=12 p) parm , h_parm s; I hope it helps.
instead of below Code BEGIN INSERT INTO EmailsRecebidos (De, Assunto, Data) VALUES (@_DE, @_ASSUNTO, @_DATA) WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos WHERE De = @_DE AND Assunto = @_ASSUNTO AND Data = @_DATA); END replace with BEGIN IF NOT EXISTS (SELECT * FROM EmailsRecebidos WHERE De = @_DE AND Assunto = @_ASSUNTO AND … Read more
Mysql has its EVENT functionality for avoiding complicated cron interactions when much of what you are scheduling is sql related, and less file related. See the Manual page here. Hopefully the below reads as a quick overview of the important steps and things to consider, and verifiable testing too. show variables where variable_name=”event_scheduler”; +—————–+——-+ | … Read more
JPA 2.1 now support Stored Procedure, read the Java doc here. Example: StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery(“sales_tax”); // set parameters storedProcedure.registerStoredProcedureParameter(“subtotal”, Double.class, ParameterMode.IN); storedProcedure.registerStoredProcedureParameter(“tax”, Double.class, ParameterMode.OUT); storedProcedure.setParameter(“subtotal”, 1f); // execute SP storedProcedure.execute(); // get result Double tax = (Double)storedProcedure.getOutputParameterValue(“tax”); See detailed example here.
I slightly modified your stored procedure (to use SCOPE_IDENTITY) and it looks like this: CREATE PROCEDURE usp_InsertContract @ContractNumber varchar(7), @NewId int OUTPUT AS BEGIN INSERT INTO [dbo].[Contracts] (ContractNumber) VALUES (@ContractNumber) SELECT @NewId = SCOPE_IDENTITY() END I tried this and it works just fine (with that modified stored procedure): // define connection and command, in using … Read more
This is our template (error logging removed) This is designed to handle Paul Randal’s article “No such thing as a nested transaction in SQL Server” Error 266 Trigger Rollbacks Explanations: all TXN begin and commit/rollbacks must be paired so that @@TRANCOUNT is the same on entry and exit mismatches of @@TRANCOUNT cause error 266 because … Read more
Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable. Declare @tablevar table(col1 col1Type,.. insert into @tablevar(col1,..) exec MyStoredProc ‘param1’, ‘param2’ SELECT col1, col2 FROM @tablevar
UPDATE I’ve added support for this on Nuget Package – https://github.com/Fodsuk/EntityFrameworkExtras#nuget (EF4,EF5,EF6) Check out the GitHub repository for code examples. Slightly off question, but none the less useful for people trying to pass user-defined tables into a stored procedure. After playing around with Nick’s example and other Stackoverflow posts, I came up with this: class … Read more
Some advantages of LINQ over sprocs: Type safety: I think we all understand this. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this … Read more
SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more