Store procuder execution error

Question

0.00/5 (No votes)

See more:

this is my store procedure named adminDegree

SQL

USE [szicExam]
GO
/****** Object:  StoredProcedure [dbo].[AdminDegree]    Script Date: 09/12/2013 05:14:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AdminDegree]
	-- Add the parameters for the stored procedure here
		 @Mode varchar(50)
		,@DegreeID int
        ,@DegreeName varchar(50)
AS
	IF @Mode='Select'
	BEGIN
	SELECT [ID]
      ,[DegreeID]
      ,[DegreeName]
	FROM [szicExam].[dbo].[Degree]
	END
	
	IF @Mode='Insert'
	BEGIN
	INSERT INTO [szicExam].[dbo].[Degree]
           ([DegreeID] ,[DegreeName])
    VALUES (@DegreeID ,@DegreeName)
	END

	IF @Mode='Update'
	BEGIN
	UPDATE [szicExam].[dbo].[Degree]
	SET [DegreeID] = @DegreeID ,[DegreeName] = @DegreeName
	WHERE DegreeID=@DegreeId
	END

	IF @Mode='Delete'
	BEGIN
	DELETE FROM [szicExam].[dbo].[Degree]
    WHERE DegreeID=@DegreeId
	END

but when i try to execute using this code

SQL

exec dbo.AdminDegree @Mode = 'Select'

and error comes in

ASM

sg 201, Level 16, State 4, Procedure AdminDegree, Line 0
Procedure or function 'AdminDegree' expects parameter '@DegreeID', which was not supplied.

i dont know why it is showing becuase i didnot provid any where clause for DegreeID

Posted 11-Sep-13 2:25am

Muhamad Faizan Khan

Updated 11-Sep-13 2:27am

v2

Add a Solution

Comments

[no name] 11-Sep-13 8:27am

Well the obvious thing to do would be to supply the parameter that the SP expects to get.

ArunRajendra 11-Sep-13 8:30am

Try this exec dbo.AdminDegree @Mode = 'Select', null, null

Muhamad Faizan Khan 11-Sep-13 10:33am

exec dbo.AdminDegree @Mode= 'select',@DegreeID=null, @DegreeName=null
i did by it, its working but i don't want to do this

[no name] 11-Sep-13 8:47am

"i didnot provid any where clause for DegreeID" sure you did. It's used all thoughout your SP!

Muhamad Faizan Khan 11-Sep-13 10:36am

it means the variable i declare every time i call store procedures i need to provide all parameters

[no name] 11-Sep-13 10:46am

Yes you do..... that is the way it works.

Muhamad Faizan Khan 11-Sep-13 11:02am

;-(

4 solutions

Solution 4

You Declared three input parameters those are

SQL

@Mode varchar(50)
        ,@DegreeID int
        ,@DegreeName varchar(50)

but you are passing single parameter that is Mode so it's gives error message is what you got

Posted 11-Sep-13 3:12am

satyamkotakonda

Comments

Muhamad Faizan Khan 11-Sep-13 10:31am

i declare 3 variables it means i every time need to pass value for it, but for 'select' query i don't require remaining two. how i overcome this problem?? i don't want to make a separate procedure for that.

Solution 1

pass @DegreeID too

exec dbo.AdminDegree @Mode = 'Select' ,@DegreeID=2 like

Posted 11-Sep-13 2:27am

a.pkumar

Comments

Muhamad Faizan Khan 11-Sep-13 8:45am

why i provide degree id?? no where clause for that

satyamkotakonda 11-Sep-13 9:15am

Your question is correct but one thing you have to remember that is procedure accepts parameters and those parameters are use full or not to proceed further

Solution 2

pass @DegreeID too

exec dbo.AdminDegree @Mode = 'Select' ,@DegreeID=2

Posted 11-Sep-13 2:28am

a.pkumar

Comments

Thanks7872 11-Sep-13 8:35am

Remove the duplicate answer.

Muhamad Faizan Khan 11-Sep-13 8:45am

why i provide degree id?? no where clause for that

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-09-11T02:46:00

Your SP expects 3 parameters, which are obligatory:

@Mode varchar(50)
@DegreeID int
@DegreeName varchar(50)

Please, see:
Execute a Stored Procedure[^]
Specify Parameters[^]
How can I use optional parameters in a T-SQL stored procedure?[^]

[EDIT #1]
Please, read my question to the comment.

SQL

CREATE PROCEDURE Sales.uspGetSalesYTD
@SalesPerson nvarchar(50) = NULL  -- NULL default value
AS 
    SET NOCOUNT ON; 

-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
   PRINT 'ERROR: You must specify the last name of the sales person.'
   RETURN
END

SQL

-- Run the procedure without specifying an input value.
EXEC Sales.usp_GetSalesYTD;
GO
-- Run the procedure with an input value.
EXEC Sales.usp_GetSalesYTD N'Blythe';
GO

[/EDIT]

Store procuder execution error

4 solutions

Solution 3

Solution 4

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us