I've tried with my own script, but it can't works, the script which I tried is below:
My first attempt:
CODE
EXEC dbo.usp_PurVar date > '01-01-2017' AND < '01-06-2017'
My second attempt:
CODE
USE [TeaRC]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_PurVar]
@startDate = 20170101,
@endDate = 20170630
SELECT 'Return Value' = @return_value
GO
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_PurVar]
@startDate = 20170101,
@endDate = 20170630
SELECT 'Return Value' = @return_value
GO
The script below is what I used to create a Stored Procedure in the MS SQL server:
CODE
USE [TeaRC]
GO
/****** Object: StoredProcedure [dbo].[usp_PurVar] Script Date: 11/09/2018 4:07:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_PurVar]
-- Add the parameters for the stored procedure here
@startDate date,
@endDate date
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 "ICITEM"."FMTITEMNO", "PORCPL"."LOCATION", "PORCPH1"."PONUMBER", "PORCPH1"."VDNAME", "PORCPL"."UNITCOST", "PORCPL"."RQRECEIVED", "PORCPH1"."RCPNUMBER", "MFITMCT"."MTLSTD", "MFITMCT"."PACKSTD", "MFITMCT"."SETUPSTD", "MFITMCT"."LABSTD", "MFITMCT"."OVHSTD", "MFITMCT"."SUBCONSTD", "MFITMCT"."TOOLSTD", "PORCPL"."TXINCLUDED", "PORCPL"."SQRECEIVED", "PORCPL"."DTARRIVAL", "ICITEM"."ITEMNO", "ICLOC"."DESC", "ICLOC"."LOCATION", "ICITEM"."STOCKUNIT", "ICITEM"."DESC"
FROM (((TeaRC."dbo"."ICITEM" "ICITEM" INNER JOIN TeaRC."dbo"."PORCPL" "PORCPL" ON "ICITEM"."FMTITEMNO"="PORCPL"."ITEMNO") INNER JOIN TeaRC."dbo"."MFITMCT" "MFITMCT" ON "ICITEM"."FMTITEMNO"="MFITMCT"."ITEMNO") INNER JOIN TeaRC."dbo"."ICLOC" "ICLOC" ON "PORCPL"."LOCATION"="ICLOC"."LOCATION") INNER JOIN TeaRC."dbo"."PORCPH1" "PORCPH1" ON "PORCPL"."RCPHSEQ"="PORCPH1"."RCPHSEQ"
WHERE "PORCPH1"."PONUMBER"<>'' AND ("ICITEM"."FMTITEMNO">=' ' AND "ICITEM"."FMTITEMNO"<='ZZZZZZZZZZZZZZZZZZZZZZZZ')
AND CAST(CONVERT(VARCHAR(10), DTARRIVAL, 121) as Date) >= @startDate AND CAST(CONVERT(VARCHAR(10), DTARRIVAL, 121) as Date) <= @endDate
ORDER BY "ICITEM"."FMTITEMNO", "PORCPL"."LOCATION"
END
GO
/****** Object: StoredProcedure [dbo].[usp_PurVar] Script Date: 11/09/2018 4:07:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_PurVar]
-- Add the parameters for the stored procedure here
@startDate date,
@endDate date
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 "ICITEM"."FMTITEMNO", "PORCPL"."LOCATION", "PORCPH1"."PONUMBER", "PORCPH1"."VDNAME", "PORCPL"."UNITCOST", "PORCPL"."RQRECEIVED", "PORCPH1"."RCPNUMBER", "MFITMCT"."MTLSTD", "MFITMCT"."PACKSTD", "MFITMCT"."SETUPSTD", "MFITMCT"."LABSTD", "MFITMCT"."OVHSTD", "MFITMCT"."SUBCONSTD", "MFITMCT"."TOOLSTD", "PORCPL"."TXINCLUDED", "PORCPL"."SQRECEIVED", "PORCPL"."DTARRIVAL", "ICITEM"."ITEMNO", "ICLOC"."DESC", "ICLOC"."LOCATION", "ICITEM"."STOCKUNIT", "ICITEM"."DESC"
FROM (((TeaRC."dbo"."ICITEM" "ICITEM" INNER JOIN TeaRC."dbo"."PORCPL" "PORCPL" ON "ICITEM"."FMTITEMNO"="PORCPL"."ITEMNO") INNER JOIN TeaRC."dbo"."MFITMCT" "MFITMCT" ON "ICITEM"."FMTITEMNO"="MFITMCT"."ITEMNO") INNER JOIN TeaRC."dbo"."ICLOC" "ICLOC" ON "PORCPL"."LOCATION"="ICLOC"."LOCATION") INNER JOIN TeaRC."dbo"."PORCPH1" "PORCPH1" ON "PORCPL"."RCPHSEQ"="PORCPH1"."RCPHSEQ"
WHERE "PORCPH1"."PONUMBER"<>'' AND ("ICITEM"."FMTITEMNO">=' ' AND "ICITEM"."FMTITEMNO"<='ZZZZZZZZZZZZZZZZZZZZZZZZ')
AND CAST(CONVERT(VARCHAR(10), DTARRIVAL, 121) as Date) >= @startDate AND CAST(CONVERT(VARCHAR(10), DTARRIVAL, 121) as Date) <= @endDate
ORDER BY "ICITEM"."FMTITEMNO", "PORCPL"."LOCATION"
END
This post has been edited by k town shit: Sep 11 2018, 06:38 PM