CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
user1740381
user1740381

Reputation: 2199

How to write a simple sql procedure?

I am working on sql server database. I have 2 tables with 1 to many association in my db. First is Parent table and second is Child table. Parent table has a column ChildCount which will update whenever the child entries for this parent is added or deleted.

So for this i decided to write a stored procedure and a DML trigger which will execute on INSERT and DELETE operations on Child table. I am totally new in database. What i tried yet is :

First i am trying to create a procedure ( which i will execute from the trigger )

CREATE PROCEDURE [dbo].[ChildCount]
    @parentId int
AS
    //here first i have to extract the total child for the given parentId and 
    //than in the next update statement i will update the count.

    UPDATE Parent
    SET ChildCount = //above total child value
    WHERE Id = parentId
RETURN 0

Here i don't understand how to extract a total child and save it in a variable and than use that variable in the update statement ??

And please after guiding me on this CREATE PROCEDURE, suggest me on what i am doing this is correct, good and efficient approach or there is other better approach for doing this ??

Upvotes: 0

Views: 209

Answers (3)

Timothy Walden
Timothy Walden

Reputation: 676

You can also consider using computed columns instead of triggers. Just create a UDF that will return the number of children for a given parent and create a computed column out of it.

Here is how it might look like 

CREATE FUNCTION dbo.GetChildCount(@ParentID int)
RETURNS int
BEGIN
    RETURN (SELECT COUNT(*) FROM Child WHERE ParentID = @ParentID)
END


ALTER TABLE Parent
    ChildCount as dbo.GetChildCount(ParentID)

Here is a link with more details.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

If you want to do this with a trigger it could be something like this:

create trigger dbo.tr_Child on dbo.Child for insert, update, delete
as

update dbo.Parent
set ChildCount = (select count(*) from dbo.Child where Child.ParentID = T.ParentID)
from 
  (
  select ParentID from inserted union 
  select ParentID from deleted
  ) as T
where Parent.ParentID = T.ParentID;

SQL Fiddle

Upvotes: 0

Amit Singh
Amit Singh

Reputation: 8109

Try like this 

 CREATE PROCEDURE [dbo].[ChildCount]
        @parentId int
    AS

    Begin
    Declare @i as int;

    Select @i=count(child) from childtable where parentid=@parentId 

        UPDATE Parent
        SET ChildCount =@i
        WHERE Id = @parentId
    End

Upvotes: 3

Related Questions