February 8, 2012 at 10:44 am
My stored procedure runs fine, but when I attempt to run my SSRS report from the SP, I get the Subject error.
Error: Procedure or Function 'SDoinidis_Duluth_LicenseeActivity' expects parameter '@Address', which was not supplied.
Below is my SP, can you help me on why I am receiving the error?
CREATE PROCEDURE dbo.SDoinidis_Duluth_LicenseeActivity
@Address Varchar(163)
AS
SET NOCOUNT ON
Select
VSE1.Description AS 'Activity',
LBS.BusinessName AS 'Business',
ADDY.FullAddress AS 'Local Address',
MA2.AddressLineOne AS 'Non-Local Address',
MA2.City AS 'Non-Local City',
VSE3.Description AS 'Non-Local State',
MA1.Zip AS 'Non-Local Zip',
CN.FormalName AS 'Licensee',
LC.ContactName AS 'Contact',
LC.ContactDescription AS 'Description',
MA1.AddressLineOne AS 'Contact Address',
MA1.City AS 'Contact City',
VSE2.Description AS 'Contact State',
MA1.Zip AS 'Contact Zip',
LC.PhoneNumber1 AS 'Contact Phone Number 1',
LC.PhoneNumber2 AS 'Contact Phone Number 2',
LC.FaxNumber AS 'Contact Fax Number',
LC.EMailAddress AS 'Contact E-Mail'
From Licensee LICE
JOIN CentralName CN ON LICE.CentralNameID = CN.CentralNameID
JOIN LicenseeContact LC ON LC.LicenseeID = LICE.LicenseeID
JOIN MailingAddress MA1 ON MA1.MailingAddressID = LC.ContactAddressID
LEFT JOIN LicenseeBusinessSpecifications LBS ON LBS.LicenseeID = LICE.LicenseeID
LEFT JOIN LicenseeBusinessActivities LBA ON LBA.LicenseeBusinessID = LBS.LicenseeBusinessID
LEFT JOIN LicenseeBusinessAddress LBAD ON LBAD.LicenseeBusinessID = LBS.LicenseeBusinessID
LEFT JOIN ValidationSetEntry VSE1 ON VSE1.EntryID = LBA.vsBusinessActivity
LEFT JOIN ValidationSetEntry VSE2 ON VSE2.EntryID = MA1.vsState
LEFT JOIN PMCentralServiceAddress PMCSA ON PMCSA.PMCentralServiceAddressID = LBAD.PMCentralServiceAddressID
LEFT JOIN Address ADDY ON ADDY.AddressID = PMCSA.AddressID
LEFT JOIN MailingAddress MA2 ON MA2.MailingAddressID = LBAD.MailingAddressID
LEFT JOIN ValidationSetEntry VSE3 ON VSE3.EntryID = MA2.vsState
Where LBA.vsBusinessActivity is not null
and ADDY.AddressID like '%' + @Address + '%'
Order By VSE1.Description
Thank you,
Steve Doinidis
February 8, 2012 at 10:46 am
It failed because you didn't pass the parameter when called your stored proc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 10:57 am
How do I pass the parameter when executing my SP?
Thank you,
Steve Doinidis
February 8, 2012 at 11:09 am
exec SDoinidis_Duluth_LicenseeActivity @Address = 'Some Value'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 11:15 am
Alternatively, if the input parameter is allowed to be 'missing' you can get around this by including a default value in the stored procedure definition - eg.
CREATE PROCEDURE dbo.SDoinidis_Duluth_LicenseeActivity
@Address Varchar(163) = NULL
February 8, 2012 at 2:02 pm
Now, I am not getting any results when running my report.
I changed the Where statement at the bottom to include ADDY.FullyAddress because I don't want the users entering in AddressID's, I want them to enter in "Main St" and get all Addresses on "Main St"
CREATE PROCEDURE dbo.SDoinidis_Duluth_LicenseeActivity
@Address Varchar(163) = NULL
AS
SET NOCOUNT ON
Select
VSE1.Description AS 'Activity',
LBS.BusinessName AS 'Business',
ADDY.FullAddress AS 'Local Address',
MA2.AddressLineOne AS 'Non-Local Address',
MA2.City AS 'Non-Local City',
VSE3.Description AS 'Non-Local State',
MA1.Zip AS 'Non-Local Zip',
CN.FormalName AS 'Licensee',
LC.ContactName AS 'Contact',
LC.ContactDescription AS 'Description',
MA1.AddressLineOne AS 'Contact Address',
MA1.City AS 'Contact City',
VSE2.Description AS 'Contact State',
MA1.Zip AS 'Contact Zip',
LC.PhoneNumber1 AS 'Contact Phone Number 1',
LC.PhoneNumber2 AS 'Contact Phone Number 2',
LC.FaxNumber AS 'Contact Fax Number',
LC.EMailAddress AS 'Contact E-Mail'
From Licensee LICE
JOIN CentralName CN ON LICE.CentralNameID = CN.CentralNameID
JOIN LicenseeContact LC ON LC.LicenseeID = LICE.LicenseeID
JOIN MailingAddress MA1 ON MA1.MailingAddressID = LC.ContactAddressID
LEFT JOIN LicenseeBusinessSpecifications LBS ON LBS.LicenseeID = LICE.LicenseeID
LEFT JOIN LicenseeBusinessActivities LBA ON LBA.LicenseeBusinessID = LBS.LicenseeBusinessID
LEFT JOIN LicenseeBusinessAddress LBAD ON LBAD.LicenseeBusinessID = LBS.LicenseeBusinessID
LEFT JOIN ValidationSetEntry VSE1 ON VSE1.EntryID = LBA.vsBusinessActivity
LEFT JOIN ValidationSetEntry VSE2 ON VSE2.EntryID = MA1.vsState
LEFT JOIN PMCentralServiceAddress PMCSA ON PMCSA.PMCentralServiceAddressID = LBAD.PMCentralServiceAddressID
LEFT JOIN Address ADDY ON ADDY.AddressID = PMCSA.AddressID
LEFT JOIN MailingAddress MA2 ON MA2.MailingAddressID = LBAD.MailingAddressID
LEFT JOIN ValidationSetEntry VSE3 ON VSE3.EntryID = MA2.vsState
Where LBA.vsBusinessActivity is not null
and ADDY.FullAddress like '%' + @Address + '%'
Order By VSE1.Description
Thank you,
Steve Doinidis
February 8, 2012 at 2:44 pm
If you don't provide the parameter and you have the default of null your query will only return those where FullAddress is null.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 2:54 pm
Provide the parameter in the SP?
I have the parameter in my report and an integer that allows Nulls.
Thank you,
Steve Doinidis
February 8, 2012 at 2:59 pm
Steve Doinidis (2/8/2012)
Provide the parameter in the SP?I have the parameter in my report and an integer that allows Nulls.
Well you changed your parameter to be optional.
@Address Varchar(163) = NULL
Do you know you are getting the parameter passed in? You could check this with a trace when you load your report.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply