Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 how to execute a Stored Procedure, to view in MS SQL Server Mgmt Studio

views
     
TSk town shit
post Sep 11 2018, 06:22 PM, updated 6y ago

Enthusiast
*****
Junior Member
758 posts

Joined: Jan 2009
Hi sifu, I have a question here, quite new to MS SQL stored procedure. The reason why I want to execute the Stored Proc is because of I want to see what kind of information is the Stored Proc used to extract. I've tried many ways, but it just couldn't works, can anybody help?

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



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


This post has been edited by k town shit: Sep 11 2018, 06:38 PM
PATAR
post Sep 13 2018, 10:27 PM

Getting Started
**
Junior Member
191 posts

Joined: Jan 2018
From: Sweden/Kedah
QUOTE(k town shit @ Sep 11 2018, 06:22 PM)
Hi sifu, I have a question here, quite new to MS SQL stored procedure. The reason why I want to execute the Stored Proc is because of I want to see what kind of information is the Stored Proc used to extract. I've tried many ways, but it just couldn't works, can anybody help?

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

*
Second one looks ok, but try this one

CODE

Try EXEC dbo.usp_PurVar '01-01-2017',  '01-06-2017'


Do you get any error msg?


TSk town shit
post Sep 17 2018, 10:31 PM

Enthusiast
*****
Junior Member
758 posts

Joined: Jan 2009
QUOTE(PATAR @ Sep 13 2018, 09:27 PM)
Second one looks ok, but try this one

CODE

Try EXEC dbo.usp_PurVar '01-01-2017',  '01-06-2017'


Do you get any error msg?
*
thanks, this is works
cawl
post Oct 19 2018, 03:50 PM

New Member
*
Junior Member
44 posts

Joined: Oct 2008


date format gua n no condition needed when you exec the sp

EXEC dbo.usp_PurVar '2017-01-01' '2017-01-06'

This post has been edited by cawl: Oct 19 2018, 03:51 PM
TSk town shit
post Oct 21 2018, 12:04 AM

Enthusiast
*****
Junior Member
758 posts

Joined: Jan 2009
QUOTE(cawl @ Oct 19 2018, 02:50 PM)
date format gua n no condition needed when you exec the sp

EXEC dbo.usp_PurVar  '2017-01-01'  '2017-01-06'
*
thanks, got it

 

Change to:
| Lo-Fi Version
0.0172sec    0.57    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 04:50 PM