Prev: Report call times out when called through web service but not through Report Manager
Next: Help: Creating a Variance column within a Column Group
From: jfredel via SQLMonster.com on 12 Mar 2010 13:49
Looking for some assistance. I need to create a stored procedure for SSRS.

Here is the query

Select Distinct a.mpin, b.longdesc INTO #Specialties
From dbo.PROV_SPECIALTIES a Inner Join dbo.SPECIALTY_TYPES b on
a.spectypecd = b.spectypecd Inner Join dbo.PROF_LANGUAGE c ON
a.mpin = c.mpin
Where c.LanguageCd IN ('jap', 'kor', 'chi', 'man', 'can', 'vie') AND
a.DirectoryInd = 'Y' AND
a.PractInSpecInd = 'Y'
Order by a.mpin

Any assistance is greatly appreciated.

--
Message posted via http://www.sqlmonster.com

From: Bruce L-C [MVP] on 12 Mar 2010 14:45
Not sure what your question is unless you have never created a stored
procedure before. RS totally supports stored procedures but if creating a
new report you would need to hard code in the wizard (I.e. include
parameters)

For instance, in the wizard you would put this: exec pr_rpt_myreport
@param1=whatever, @param2 = something

Now, for what you have below. No need for the temp table:

--I use the following naming to help me keep track of what stored procedure
goes with what report

create procedure pr_rpt_reportname
as
Select Distinct a.mpin, b.longdesc From dbo.PROV_SPECIALTIES a Inner Join
dbo.SPECIALTY_TYPES b on
a.spectypecd = b.spectypecd Inner Join dbo.PROF_LANGUAGE c ON
a.mpin = c.mpin
Where c.LanguageCd IN ('jap', 'kor', 'chi', 'man', 'can', 'vie') AND
a.DirectoryInd = 'Y' AND
a.PractInSpecInd = 'Y'
Order by a.mpin
return


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"jfredel via SQLMonster.com" <u10039(a)uwe> wrote in message
news:a4e8efa67b0ed(a)uwe...
> Looking for some assistance. I need to create a stored procedure for
> SSRS.
>
> Here is the query
>
> Select Distinct a.mpin, b.longdesc INTO #Specialties
> From dbo.PROV_SPECIALTIES a Inner Join dbo.SPECIALTY_TYPES b on
> a.spectypecd = b.spectypecd Inner Join dbo.PROF_LANGUAGE c ON
> a.mpin = c.mpin
> Where c.LanguageCd IN ('jap', 'kor', 'chi', 'man', 'can', 'vie') AND
> a.DirectoryInd = 'Y' AND
> a.PractInSpecInd = 'Y'
> Order by a.mpin
>
> Any assistance is greatly appreciated.
>
> --
> Message posted via http://www.sqlmonster.com
>
From: Uri Dimant on 14 Mar 2010 03:30
Hi
I think you do not need to create a temporsary table unless there is some
logic behind the scene.
CREATE PROCEDURE spyourname
AS
SET NOCOUNT ON

Select Distinct a.mpin, b.longdesc
From dbo.PROV_SPECIALTIES a Inner Join dbo.SPECIALTY_TYPES b on
a.spectypecd = b.spectypecd Inner Join dbo.PROF_LANGUAGE c ON
a.mpin = c.mpin
Where c.LanguageCd IN ('jap', 'kor', 'chi', 'man', 'can', 'vie') AND
a.DirectoryInd = 'Y' AND
a.PractInSpecInd = 'Y'
Order by a.mpin


--Usage
EXEC spyourname

"jfredel via SQLMonster.com" <u10039(a)uwe> wrote in message
news:a4e8efa67b0ed(a)uwe...
> Looking for some assistance. I need to create a stored procedure for
> SSRS.
>
> Here is the query
>
> Select Distinct a.mpin, b.longdesc INTO #Specialties
> From dbo.PROV_SPECIALTIES a Inner Join dbo.SPECIALTY_TYPES b on
> a.spectypecd = b.spectypecd Inner Join dbo.PROF_LANGUAGE c ON
> a.mpin = c.mpin
> Where c.LanguageCd IN ('jap', 'kor', 'chi', 'man', 'can', 'vie') AND
> a.DirectoryInd = 'Y' AND
> a.PractInSpecInd = 'Y'
> Order by a.mpin
>
> Any assistance is greatly appreciated.
>
> --
> Message posted via http://www.sqlmonster.com
>


 | 
Pages: 1
Prev: Report call times out when called through web service but not through Report Manager
Next: Help: Creating a Variance column within a Column Group