Newbie - I Dont Know

Transact-SQL
1

Okay im rather new to mssql, actually i dont usually use it beyond simple MVC Web Applications, and here in lies the problem.

Ive been working on something much more complex, and ive spent almost a month trying to implement this in MVC itself with no real success so i decided to see if T-SQL can do what i need.

I refer to the word triggers (What i mean is a stored proceadure).

Creating the views to return the data i want was point and click so far so good, but now im stumped.

With all the data returned, i now need to process that data on a scedule, that data involves doing a very quick maths calculation, then updating a specific entry. Once a day on a schedule.

Now i dont know what i need to give you on here for the best help, this kind of MSSQL is beyond me, most of my sql experiance is within MVC and even then its not the best, this may simply be a case of way above my paygrade, but iif you fail to try you fail to succeed.

Any help links insight advice are apreciated, perhaps you would be intrested in creating the proceadure for me for a fee and walking me thru it (Im on of those who learn best taking apart someone elses work :))

Thanks

PS if i put this in the wrong place or beyond the scope let me know , on google it came up with the perfect place for beginners so here i am

2

Ive included what ive got so far.

So far this all works but where im stumped is how to start the last bit

//// Lines are comments to break down key items

`USE [JustStake]
GO
/****** Object: StoredProcedure [dbo].[cakes_proc] Script Date: 01/03/2016 23:44:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[cakes_proc]
-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT TOP 1000 [CakeId]
      ,[isCake]
      ,[NOWDAY]
      ,[CAKEDAY]
      ,[Amount]
      ,[Done]
      ,[UserId]
  FROM [JustCake].[dbo].[cakeday]
   where [cakeid] = '6088F482-DEDB-E511-80BA-00505604A23D' 


   SELECT 
    sum([Balance])
      
  FROM [Justcake].[dbo].[Proff]
  where [cakeid] = '6088F482-DEDB-E511-80BA-00505604A23D' 
  and balance > 0

  SELECT  [CakeId]
      ,sum([Amount])
FROM [JustCake].[dbo].[cakeday]
where [cakeid] = '6088F482-DEDB-E511-80BA-00505604A23D'    
  group by [CakeId]

END
`

So the script now returns

All cakes = true and all done = true

it then SUMS the amount (i now need to store this somewhere)

next it checks a users database of there "balances" and returns all entrys where cakeID = whatever id.

Next i need to Take the value we stored earlier, multiply it buy the balance and then update the balance entry and write
a ledger entry into another table consisting of Type Ammount DateOfCalc DateOfAction

3

Which value are you referring to ?

something like this . .

UPDATE BE
SET    balance = balance - (@Value * @Balance)
FROM   [Balance Entry] AS BE
WHERE  [cakeid] = '6088F482-DEDB-E511-80BA-00505604A23D' 

INSERT INTO [another table] ( Type , Ammount , DateOfCalc , DateOfAction )
SELECT Type = 'some type', Ammount = @Amount, DateofCalc = getdate(), DateofAction = getdate()
4

Now this suddenly looks a whole less scary than before, and i think i understand it :slightly_smiling:

The value is the sum total all of all entrys where iscake=true done=true , they have an ammount value, since the only number im intrested in is the total of all values

And thanks for the help so far, im not going to ask for more, im going to go away and try with what youve shown me and try connect the dots on my own if i can.

I thank you for your help so far, i shall return with either a finished script of a few more questions

5

Sounds good to me !!!

A pedantic point to clarify:

A trigger is "fired" after Insert, Update or Delete. (One trigger can fire after all three, or different triggers on each one, and initially there are no triggers on a new table that you create).

You can set triggers on Tables and also on Views (that's more unusual though).

Critical point: A trigger runs ONCE per operation and NOT for each row, individually, in the batch. So you must write your Trigger to handle ALL rows in the "set" being processed, and not assume it only has to process a single row.

You can raise an error, in the SQL code in a Trigger, and that will abort the (insert, update or delete) process - and rollback any changes. So you can use a trigger for data validation, or to maintain/update a running-total in an associated Master table, and so on.

We use triggers, mainly, to store the changes users make to data in a (separate) Audit table.

A Stored Procedure, OTOH, is as per the one you have written, and it encapsulates some SQL code & logic, optionally takes some parameters, and can be Executed to perform a task - either by an Application, or as a scheduled task - MS SQL includes "SQL Agent" which has a task scheduler built in.

In using a Stored Procedure the task is parametrised and you are giving SQL the opportunity to store a Query Plan for the job, which is independent of the actual specific values you want to use each time you call it. This can dramatically improve the performance [compared to dynamic SQL]

For example, you can directly execute some SQL from your application (this is "dynamic SQL", you build the whole statement in your Application, and the exact SQL varies each time, e.g. based on the values within it)

SELECT Address FROM SomeTable WHERE UserName = 'FRED'

SQL will make a Query Plan (e.g. to use the INDEX on UserName, if you have one) and save that. To reuse that, specific, cached query plan you have to send the identical SQL - so that Plan only works for 'FRED'. (I am oversimplifying, SQL is smarter than that in some cases).

If you write a procedure:

CREATE PROCEDURE MySProc
    @UserName varchar(10)
AS
SELECT Address FROM SomeTable WHERE UserName = @UserName

SQL, again, makes a query plan, but this time your "exact" SQL does not change each time, it is parametrised, so SQL reuses the cached query plan each time. Much better for performance ...

here endeth the lesson!!

6

Ill pretend i know most of what you said.

But i am making progress, ive had to expand alot of what ive already dont so i have all the information needed for each step returned.

I even got it writing single entrys into the ledgers table albeit not perfect i admit, but this is a learning curve right.

I had planned to run more than one stored proceadure, one for each cakeId all pritty much hard coded, since all it has to do is a job that takes me most of the day to do by hand.

I just wished my skillset matched my ambition

7

You don't have to do that. If you have any doubt or anything unclear, just post back.

It is good to set your ambition high. It will lower as time goes. When I was younger, I wanted to reach the sky. Now I am happy that i am not 2 meters under . . . :slightly_smiling:

8

Its a good approach - its worked for me all my adult working life!!

Practice and Experience will take care of that. And using a good Forum of course ...

Hmmm ... you ru n the risk, with that approach, that you find a bug and then have to fix (that same bug) in several different Procedures.

You could have a generic procedure, which takes a parameter for [cakeId] (or whatever) and then individual, other, procedures do a "hardwired job", but actually call the generic procedure to do the heavy-lifting.

If you schedule the individual jobs then you don't really need individual procedures because, in each job, you can explicitly say EXEC MyGenericProc @CakeId = 123

But from elsewhere it might be helpful to be able to say

EXEC DoCakeID_ProcessX
EXEC DoCakeID_ProcessY

Personally I wouldn't, I'd do

EXEC MyGenericProc @CakeId = 123
EXEC MyGenericProc @CakeId = 456

but "it depends" definitely applies.