Tuesday, 20 June 2017

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