mesutd0nmez
10/28/2016 - 9:20 AM

How to create Store Procedure

How to create Store Procedure

sp_GenelAlimListesi_rpt  @DepositorId  =1, @RequestTypeId=1 

alter   proc sp_GenelAlimListesi_rpt(
	@DepositorId  smallint=null,
	@RequestTypeId smallint=null,
	@AcquireDate datetime=null,
	@IsMobile  bit=null,
	@BoutiqueSupplierRegionNo smallint=null,
	@ProductTypeId  smallint=null,
	@DriverId smallint=null,
	@PlateNo nvarchar(20)=null,
	@IsSpotVehicle bit=null,
	@IsHanger bit=null,
	@ReturnTypeId smallint=null)
as
SELECT  
		Depositor.Description as Mudi,
		RequestType.Description as 'Alım Tipi',
		BoutiqueId as 'Butik Id',
		BoutiqueName as 'Butik Adı'

FROM Request(Nolock)
	INNER JOIN Depositor(Nolock) on Depositor.DepositorId=Request.DepositorId
	INNER  JOIN Vehicle(Nolock) on Vehicle.VehicleId=Request.VehicleId
	INNER JOIN [User]  as Driver (Nolock) on Driver.UserId=Vehicle.DriverId
	INNER JOIN RequestType(Nolock) on RequestType.RequestTypeId=Request.RequestTypeId
	left JOIN ReturnType(Nolock) on ReturnType.ReturnTypeId=Request.ReturnTypeId
WHERE 
	(Request.DepositorId = @DepositorId   or @DepositorId   is null) And
	(Request.RequestTypeId = @RequestTypeId  or @RequestTypeId  is null) And
	(Request.AcquireDate  = @AcquireDate  or @AcquireDate   is null) And
	(Request.IsMobile    = @IsMobile    or @IsMobile     is null) And
	(Request.BoutiqueSupplierRegionNo   = @BoutiqueSupplierRegionNo or @BoutiqueSupplierRegionNo is null) And
	(Request.ProductTypeId   = @ProductTypeId   or @ProductTypeId   is null) And
	(Vehicle.DriverId = @DriverId  or @DriverId  is null) And
	(Vehicle.PlateNo  = @PlateNo   or @PlateNo  is null) And
	(case when Vehicle.SpotDate is null then 0 else 1 end     = @IsSpotVehicle or @IsSpotVehicle  is null) And
	(Request.IsHanger = @IsHanger  or @IsHanger is null) And
	(Request.ReturnTypeId = @ReturnTypeId or @ReturnTypeId is null)