SQL Server Function

Function declaration

USE [Girfa_Portal]

GO

/****** Girfa Student Help******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[getBookStock] (@bookcod varchar(50))

RETURNS int

AS

BEGIN

       Declare @rdata as int

       Declare @idata as int

       select @rdata=sum(stock) from BookStock

       where BookCode=@bookcod

       group by BookCode

       select @idata =count(TType) from bookIssue

       where TType='issue' and BookCode=@bookcod

       return  @rdata-@idata;

END

Calling

USE [Girfa_Portal]

GO

/****** Girfa Student Help ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure  [dbo].[BookIssueUpdate]

(@bocde bigint)

      

AS

BEGIN

declare  @s int

      

       select @s=dbo.getBookStock(110)

       if @s=0

              begin

              select 'Stock is full' as msg

              end

       else

              begin

              select 'ready to insert' as msg

              end

END

Next Example