January 29, 2008 at 1:24 pm
What is the trick for executing a select statement in a sproc that contains a variable?
In my sproc, I have the following and when I highlight everything from the 1st variable and execute it, I get Must declare the scalar variable "@datPosting" errors.
Thanks
Dean
ALTER PROCEDURE [dbo].[srptLaborHours]
@datPosting smalldatetime = '11/1/2007'
,@ResultGrouping varchar(10) = 'Summary'
AS
BEGIN
SET NOCOUNT ON;
If @ResultGrouping = 'Summary'
BEGIN
SELECT LD.Job_Number
, LD.Posting_Date
, CASE WHEN LEFT(JM.Office_Number, 1) = 1 THEN 'Company Management' WHEN LEFT(JM.Office_Number, 1) = 2 THEN 'Buildings'
WHEN LEFT(JM.Office_Number, 1) = 3 THEN 'Technology'
WHEN LEFT(JM.Office_Number, 1) = 4 THEN 'Life Sciences'
WHEN LEFT(JM.Office_Number, 1) = 5 THEN 'Technology'
WHEN LEFT(JM.Office_Number, 1) = 7 THEN 'Buildings'
WHEN LEFT(JM.Office_Number, 1) = 8 THEN 'Buildings'
WHEN LEFT(JM.Office_Number, 1) = 9 THEN
CASE WHEN HR.Division = 'Closed' THEN 'Technology'
ELSE rtrim(dbo.fProperCase((HR.Division),null,null)) END
END AS 'Division'
FROMPARFLS.dbo.LaborDetail AS LD INNER JOIN
PARFLS.dbo.JobMaster AS JM ON LD.Job_Number = JM.Job_Number INNER JOIN
PARFLS.dbo.HrMyData AS HR ON LD.Employee_Number = HR.Employee_Number
WHERE((JM.Office_Number BETWEEN '100' AND '899') OR
(JM.Office_Number BETWEEN '100' AND '899') OR
(JM.Office_Number BETWEEN '900' AND '999')) and
LD.Posting_Date > @datPosting
END
January 29, 2008 at 2:03 pm
You cant run the select statment with the parameters unless you declare the parameters. For a stored procedure you dont have to use the "DECLARE" statement to declare variables because they are in the variable declaration section.
To test the procedure either compile and execute it or actually declare the variable like.
DECLARE @datPosting smalldatetime
Additionally, your stored procedure is missing an "END". Formatting your code can make it much easier to work with.
ALTER PROCEDURE [dbo].[srptLaborHours]
@datPosting smalldatetime = '11/1/2007'
,@ResultGrouping varchar(10) = 'Summary'
AS
BEGIN
If @ResultGrouping = 'Summary'
BEGIN
SELECT LD.Job_Number
, LD.Posting_Date
, CASE
WHEN LEFT(JM.Office_Number, 1) = 1 THEN 'Company Management'
WHEN LEFT(JM.Office_Number, 1) = 2 THEN 'Buildings'
WHEN LEFT(JM.Office_Number, 1) = 3 THEN 'Technology'
WHEN LEFT(JM.Office_Number, 1) = 4 THEN 'Life Sciences'
WHEN LEFT(JM.Office_Number, 1) = 5 THEN 'Technology'
WHEN LEFT(JM.Office_Number, 1) = 7 THEN 'Buildings'
WHEN LEFT(JM.Office_Number, 1) = 8 THEN 'Buildings'
WHEN LEFT(JM.Office_Number, 1) = 9 THEN
CASE WHEN HR.Division = 'Closed' THEN 'Technology'
ELSE rtrim(dbo.fProperCase((HR.Division),null,null))
END
END AS 'Division'
FROM PARFLS.dbo.LaborDetail AS LD INNER JOIN
PARFLS.dbo.JobMaster AS JM ON LD.Job_Number = JM.Job_Number INNER JOIN
PARFLS.dbo.HrMyData AS HR ON LD.Employee_Number = HR.Employee_Number
WHERE ((JM.Office_Number BETWEEN '100' AND '899') OR
(JM.Office_Number BETWEEN '100' AND '899') OR
(JM.Office_Number BETWEEN '900' AND '999')) and
LD.Posting_Date > @datPosting
END
END
January 29, 2008 at 2:10 pm
thanks, i figured it was something simple.
It was formatted in SSMS, what do I need to do to keep the formatting when I post it here?
January 29, 2008 at 2:14 pm
You can maintain your formatting using the code tags. For some reason the code tags are not on the shortcut menu; I have no idea why :hehe:
e.g.
January 29, 2008 at 2:16 pm
Note for the above post:
You will need both opening and closing brackets for the code tags. I did not put them because I wanted to display what to type, without actually putting the code block on the screen.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply