INFORMATION_SCHEMA.PARAMETERS

Is there any way to find out the stored procedure output paramters like I
can findout the input params through following query but I want to findout
the output params list...
SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
thanks in advance.
select
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
PARAMETER_MODE,
NUMERIC_PRECISION,
NUMERIC_SCALE
from INFORMATION_SCHEMA.PARAMETERS
where specific_name = @.chvProcName
order by ordinal_position
look for PARAMETER_MODE it will have a value of OUT
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> Is there any way to find out the stored procedure output paramters like I
> can findout the input params through following query but I want to findout
> the output params list...
> SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
> thanks in advance.
>
>
>
|||No, I mean ... let's assume this is the stored procedure right ... I need
the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
Time...
CREATE PROCEDURE [dbo].[SPSelectTotalServices]
@.ModalityTypeID INT = NULL,
@.LHIN_Code INT = NULL,
@.Lan CHAR(2) = 'EN'
AS
IF (@.Lan = 'EN')
BEGIN
SELECT
MF_ModalityTypeID AS SiteID,
(
SELECT
MT_Alias
FROM tblModalityType
WHERE MT_ModalityTypeID = MF_ModalityTypeID
) AS Site,
CEILING(sum(MF_Median)) AS 'Median Wait Time',
CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
FROM tblModalityFacility
WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
AND
(@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
AND MF_ModalityTypeID IS NOT NULL
GROUP BY MF_ModalityTypeID
ORDER BY 2
END
GO
Is there any way ?
Thanks in advance.
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...[vbcol=seagreen]
> select
> PARAMETER_NAME,
> DATA_TYPE,
> CHARACTER_MAXIMUM_LENGTH,
> PARAMETER_MODE,
> NUMERIC_PRECISION,
> NUMERIC_SCALE
> from INFORMATION_SCHEMA.PARAMETERS
> where specific_name = @.chvProcName
> order by ordinal_position
> look for PARAMETER_MODE it will have a value of OUT
> http://sqlservercode.blogspot.com/
>
> "Rogers" wrote:
|||Thats not an output parameter but a resultset
You could use sp_helptext and parse thru that
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> No, I mean ... let's assume this is the stored procedure right ... I need
> the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
> Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
> AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
>
|||what are you trying to do with the output?
In any case, look up SET FRMONLY in books on line - that should get you there.
for instance:
SET FMTONLY ON
GO
SELECT *
FROM SPSelectTotalServices
GO
(remember to SET FMTONLY OFF when you're done!)
"Rogers" <Rogers@.mailstuff.com> wrote in message news:O7dV$StvFHA.2932@.TK2MSFTNGP10.phx.gbl...
> No, I mean ... let's assume this is the stored procedure right ... I need the output like SiteID,Site,Median Wait Time, 90% Wait
> Time,Average Wait Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID) AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
sql

Info on SQL Server waits

I'm looking for explanations and troubleshooting
documentation for analyzing SQL Server waits (output from
dbcc sqlperf(waitstats)) other than from this paper:
http://support.microsoft.com/default.aspx?scid=KB;en-
us;q244455
Does anyone have anything more current? Thanks.
RobWhat is it specifically you are looking for? Specific
wait types?
If you are concerned with specific waitstats, and do not
know what the waistat is, then search on www.deja.com. It
is a good resource.
Greg
>--Original Message--
>I'm looking for explanations and troubleshooting
>documentation for analyzing SQL Server waits (output from
>dbcc sqlperf(waitstats)) other than from this paper:
>http://support.microsoft.com/default.aspx?scid=KB;en-
>us;q244455
>Does anyone have anything more current? Thanks.
>Rob
>.
>