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?
VBBRettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
post your stored proc on here.
0
VBBRettAuthor Commented:
USE [EmployeeScreening]
GO
/****** Object:  StoredProcedure [dbo].[InsertMemberFamilyInfo]    Script Date: 07/14/2009 14:18:26 ******/
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].[InsertMemberFamilyInfo]
      -- 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 @MemberFamilyID = @ID OUT

SELECT @ID ID;
0
VBBRettAuthor Commented:
MemberFamilyID is the primary key in this stored procedure.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

chapmandewCommented:
is a record getting inserted?
0
VBBRettAuthor Commented:
no, no records getting inserted.  In fact, I will show the error message after this
0
chapmandewCommented:
I think the proc is fine....it is the way you're calling it that is causing you fits.
0
VBBRettAuthor Commented:
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
0
VBBRettAuthor Commented:
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?
0
VBBRettAuthor Commented:
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!*!
0
chapmandewCommented:
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.
0
VBBRettAuthor Commented:
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?
0
VBBRettAuthor Commented:
Shall I create another thread until I get the answer?
0
VBBRettAuthor Commented:
Doesn't anybody know what is wrong?  It's a very simple stored procedure.
0
chapmandewCommented:
First, settle down...we'll figure this out.
0
VBBRettAuthor Commented:
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.
0
chapmandewCommented:
OK...this works fine for me....
create table memberfamilyinfo
(
MemberFamilyID INT IDENTITY(1,1),
EmployeeID INT, MemberFamilyNumber  INT, Allergies nvarchar(50),
      Weight INT, Age INT, DateOfBirth datetime, MemberFamilyCreatedDate datetime, MemberFamilyCreatedBy nvarchar(50)
)
 
GO
 
-- =============================================
-- Author:            <user1>
-- Create date: <07/13/09>
-- Description:      <Insert Employee Family Information>
-- =============================================
CREATE PROCEDURE [dbo].[InsertMemberFamilyInfo]
      -- 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;
GO
DECLARE @ID INT;
EXEC dbo.InsertMemberFamilyInfo @MemberFamilyID = @ID OUT, 
      @EmployeeID                               = 2,
      @MemberFamilyNumber                   = 2,
      @Allergies                             = 'none',
      @Weight                                  =  5,
      @Age                                   = 5,
      @DateOfBirth                        = '1/1/2009',
      @MemberFamilyCreatedDate      = '3/1/2009',
      @MemberFamilyCreatedBy         =   'chapman'
 
SELECT @ID ID;
 
GO
 
select * from MemberFamilyInfo

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VBBRettAuthor Commented:
Hi chapman, how would you do an update and a delete stored procedure also?
0
chapmandewCommented:
Woudl work the same way....close this question out (yours is a new one), and Id be happy to answer....
0
VBBRettAuthor Commented:
Thanks chapman!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.