October 9, 2014 at 4:34 pm
I'm starting to really dislike this stored procedure business...
So I got not so great grade on the last one because there was some other error that I just ended up giving up on and turning in as is because quite frankly, I am getting a little sick of database programming.
Here is my chance to improve my grade, but I can't even figure out this one! Here's the code:
CREATE PROC TempLetter
@type varchar(10),
@tempEmployeeCount INT,
@CurrentEmployeeID varchar(10),
@LastName varchar(20),
@FirstName varchar(15) ,
@VpOfOpName varchar(20),
@Date as varchar(12)
AS
SET @CurrentEmployeeID = ' '
SET @Date = convert(char(12),getDate(), 107)
****
SELECT @VpOfOpName = FirstName + ' ' + LastName
FROM Employee WHERE JobTitle = 'VP Operations'
SELECT @tempEmployeeCount = count(*) FROM Employee
WHERE Type = @type AND releasedate IS NULL
if @tempEmployeeCount > 0
BEGIN
WHILE @tempEmployeeCount > 0
BEGIN
-- *** the following two selects could most likely be combined into one
-- *** select and it would be more efficient, but I did two selects
-- *** to keep the logic easier to follow.
SELECT @CurrentEmployeeID = MIN(EmployeeID)
FROM Employee WHERE Type = @type AND releasedate IS NULL
AND EmployeeID > @CurrentEmployeeID
SELECT @LastName = LastName,
@FirstName=FirstName
FROM Employee WHERE EmployeeID = @CurrentEmployeeID
PRINT ' '
PRINT ' '
PRINT ' '
PRINT ' '
PRINT ' ' + @Date
PRINT ' '
PRINT ' Dear ' + @FirstName + ' ' + @LastName + ','
PRINT ' '
PRINT ' There will be three full-time positions posted within '
PRINT ' the next two weeks. I encourage you to apply '
PRINT ' for one of these full-time positions. '
PRINT ' '
PRINT ' '
PRINT ' Sincerely, ' + @VpOfOpName
PRINT ' VP Operations '
PRINT ' '
PRINT '*****************************************'
--PRINT cast(@tempEmployeeCount as varchar)
--PRINT @CurrentCustID
SET @tempEmployeeCount = @tempEmployeeCount - 1
END
END
ELSE
BEGIN
PRINT ' '
PRINT ' There are no employees of this type. '
PRINT ' '
END
GO
EXEC TempLetter @type = 'Temporary'
And when the code runs, I get this error:
Msg 201, Level 16, State 4, Procedure TempLetter, Line 0
Procedure or function 'TempLetter' expects parameter '@tempEmployeeCount', which was not supplied.
Okay, so what am I doing wrong now?
October 9, 2014 at 4:41 pm
All these variables:
@type varchar(10),
@tempEmployeeCount INT,
@CurrentEmployeeID varchar(10),
@LastName varchar(20),
@FirstName varchar(15) ,
@VpOfOpName varchar(20),
@Date as varchar(12)
you declared as part of the parameter set which must be supplied when you invoke the procedure. You should look up 'parameter defaults' to see how to make some of them optional.
http://technet.microsoft.com/en-us/library/ms189330(v=SQL.105).aspx
October 9, 2014 at 4:42 pm
You're not sending enough parameters to your stored procedure. You need to either execute your procedure with all the parameters or assign defaults to your parameters. Even better would be to remove unnecessary parameters and declare them as variables.
I'm sure you're not interested on following best practices, so I won't insist. If you are, please let me know.
October 9, 2014 at 4:44 pm
It comes down to the fact that you've built a large parameter list at the top, and you're expecting that to be the declaration for all local scopes. Think of a proc, at its core level, as nothing more than a sql side sub-routine.
So, when you do this:
CREATE PROC TempLetter
@type varchar(10),
@tempEmployeeCount INT,
@CurrentEmployeeID varchar(10),
@LastName varchar(20),
@FirstName varchar(15) ,
@VpOfOpName varchar(20),
@Date as varchar(12)
Since none of those have defaults, all of them would need to be provided when you call the proc, which you're not at the tail of your code. If only @type needs to be told to the proc, then you do this:
CREATE PROC TempLetter
@type varchar(10)
AS
DECLARE @tempEmployeeCount INT,
@CurrentEmployeeID varchar(10),
@LastName varchar(20),
@FirstName varchar(15) ,
@VpOfOpName varchar(20),
@Date as varchar(12)
That makes only @type settable from the call, and the rest are considered local variables.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2014 at 4:49 pm
Evil Kraig F (10/9/2014)
It comes down to the fact that you've built a large parameter list at the top, and you're expecting that to be the declaration for all local scopes. Think of a proc, at its core level, as nothing more than a sql side sub-routine.So, when you do this:
CREATE PROC TempLetter
@type varchar(10),
@tempEmployeeCount INT,
@CurrentEmployeeID varchar(10),
@LastName varchar(20),
@FirstName varchar(15) ,
@VpOfOpName varchar(20),
@Date as varchar(12)
Since none of those have defaults, all of them would need to be provided when you call the proc, which you're not at the tail of your code. If only @type needs to be told to the proc, then you do this:
CREATE PROC TempLetter
@type varchar(10)
AS
DECLARE @tempEmployeeCount INT,
@CurrentEmployeeID varchar(10),
@LastName varchar(20),
@FirstName varchar(15) ,
@VpOfOpName varchar(20),
@Date as varchar(12)
That makes only @type settable from the call, and the rest are considered local variables.
Oh.... Ohhhhh! This was what I was looking for! Here was my issue: I couldn't figure out how exactly to separate the parameter from the variables. Now it's working, thank you so much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply