VBBRett
asked on
Stored Procedure Parameters not being passed
I am trying to write a stored procedure to insert data into a table in SQL Server 2005. The stored procedure seems fine, except no matter what, it won't enter any values into the table. What it seems is, after the stored procedure runs and if I enter information into the text box of the stored procedure module, no data gets passed. But, if I were to assign values to the stored procedure ahead of time, it appears to hold the values. What should I do to fix this problem?
chapmandew
post your stored proc on here.
ASKER
USE [EmployeeScreening]
GO
/****** Object: StoredProcedure [dbo].[InsertMemberFamilyI
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: <user1>
-- Create date: <07/13/09>
-- Description: <Insert Employee Family Information>
-- ==========================
ALTER PROCEDURE [dbo].[InsertMemberFamilyI
-- Add the parameters for the stored procedure here
@MemberFamilyID int = NULL OUTPUT,
@EmployeeID int = 2,
@MemberFamilyNumber int = 2,
@Allergies nvarchar(50),
@Weight int,
@Age int,
@DateOfBirth datetime,
@MemberFamilyCreatedDate datetime,
@MemberFamilyCreatedBy nvarchar(50)
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO MemberFamilyInfo (EmployeeID, MemberFamilyNumber, Allergies,
Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
(@EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy);
SET @MemberFamilyID = SCOPE_IDENTITY();
END;
DECLARE @ID INT;
EXEC dbo.InsertMemberFamilyInfo
SELECT @ID ID;
GO
/****** Object: StoredProcedure [dbo].[InsertMemberFamilyI
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: <user1>
-- Create date: <07/13/09>
-- Description: <Insert Employee Family Information>
-- ==========================
ALTER PROCEDURE [dbo].[InsertMemberFamilyI
-- Add the parameters for the stored procedure here
@MemberFamilyID int = NULL OUTPUT,
@EmployeeID int = 2,
@MemberFamilyNumber int = 2,
@Allergies nvarchar(50),
@Weight int,
@Age int,
@DateOfBirth datetime,
@MemberFamilyCreatedDate datetime,
@MemberFamilyCreatedBy nvarchar(50)
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO MemberFamilyInfo (EmployeeID, MemberFamilyNumber, Allergies,
Weight, Age, DateOfBirth, MemberFamilyCreatedDate, MemberFamilyCreatedBy) VALUES
(@EmployeeID, @MemberFamilyNumber, @Allergies, @Weight, @Age, @DateOfBirth,
@MemberFamilyCreatedDate, @MemberFamilyCreatedBy);
SET @MemberFamilyID = SCOPE_IDENTITY();
END;
DECLARE @ID INT;
EXEC dbo.InsertMemberFamilyInfo
SELECT @ID ID;
ASKER
MemberFamilyID is the primary key in this stored procedure.
is a record getting inserted?
ASKER
no, no records getting inserted. In fact, I will show the error message after this
I think the proc is fine....it is the way you're calling it that is causing you fits.
ASKER
Here is the stored procedure executable after I ran the stored procedure, see picture and below is the error:
(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@Allergies', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
executestoredprocedure.jpg
(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@Allergies', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
executestoredprocedure.jpg
ASKER
chapmandew, I am running this directly from Sql Server 2005. All I am doing is executing the stored proc and no values are getting inserted. Also when I try to insert it using a web form, no new records get created. If it doesn't work in SQL Server Management Studio Express, it won't work anywhere. So how do I get this to work?
ASKER
I bet you are clueless right? Don't know the answer to this one? Well, this has everyone stumped. Why am I unable to pass paramters into the database? It's almost like SQL Server turned into a big steamy pile of Sh!*!
I thought yous aid that when you passed them in directly it worked fine...
put some select statements in your proc to see if the variables get in OK
select @var1, @var2, @var3....
it will print them to the screen when you run it.
put some select statements in your proc to see if the variables get in OK
select @var1, @var2, @var3....
it will print them to the screen when you run it.
ASKER
I even recreated the same stupid stored procedure under a different name and I'm still getting the same obnoxious stupid error!
(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@EmployeeID', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
WHAT DO I HAVE TO DO TO INSERT A STINKIN RECORD?
(1 row(s) affected)
Msg 201, Level 16, State 4, Procedure InsertMemberFamilyInfo, Line 0
Procedure or function 'InsertMemberFamilyInfo' expects parameter '@EmployeeID', which was not supplied.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
WHAT DO I HAVE TO DO TO INSERT A STINKIN RECORD?
ASKER
Shall I create another thread until I get the answer?
ASKER
Doesn't anybody know what is wrong? It's a very simple stored procedure.
First, settle down...we'll figure this out.
ASKER
Sorry, I have been working on this all day and it is really starting to irk me. This is something so simple to accomplish and Microsoft throws a monkey wrench in the works.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi chapman, how would you do an update and a delete stored procedure also?
Woudl work the same way....close this question out (yours is a new one), and Id be happy to answer....
ASKER
Thanks chapman!