Solved

Stored Procedure Parameters not being passed

Posted on 2009-07-14
19
166 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:VBBRett
  • 12
  • 7
19 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852377
post your stored proc on here.
0
 

Author Comment

by:VBBRett
ID: 24852472
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
 

Author Comment

by:VBBRett
ID: 24852485
MemberFamilyID is the primary key in this stored procedure.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24852493
is a record getting inserted?
0
 

Author Comment

by:VBBRett
ID: 24852501
no, no records getting inserted.  In fact, I will show the error message after this
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852517
I think the proc is fine....it is the way you're calling it that is causing you fits.
0
 

Author Comment

by:VBBRett
ID: 24852549
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
 

Author Comment

by:VBBRett
ID: 24852567
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
 

Author Comment

by:VBBRett
ID: 24852854
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852875
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
 

Author Comment

by:VBBRett
ID: 24852907
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
 

Author Comment

by:VBBRett
ID: 24852916
Shall I create another thread until I get the answer?
0
 

Author Comment

by:VBBRett
ID: 24852920
Doesn't anybody know what is wrong?  It's a very simple stored procedure.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24852934
First, settle down...we'll figure this out.
0
 

Author Comment

by:VBBRett
ID: 24852956
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24853004
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
 

Author Comment

by:VBBRett
ID: 24853413
Hi chapman, how would you do an update and a delete stored procedure also?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24853435
Woudl work the same way....close this question out (yours is a new one), and Id be happy to answer....
0
 

Author Closing Comment

by:VBBRett
ID: 31603398
Thanks chapman!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Review MS SQL cluster diagram 9 89
Delete from table 6 47
How can I get this column in my query? 2 47
Help with SQL - TOP 10 by date and by group 13 40
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question