stored procedure problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure problem

Author

Topic

san79
Starting Member

42 Posts

Posted - 2008-04-14 : 02:12:54

hi to all , this is my first experience with stored procedures,
i am trying to create a stored procedure which inserts a record in a table.

CREATE    Procedure InsertDepartment (
@dname varchar(50)
, @status char(10)
, @isSubdept char(1)
, @Maindept varchar(10)
, @Dtid varchar(50) output
)
AS

declare @did smallint
set @did=(select deptidcounter from tblcounter);
set @did=@did+1;
if @isSubdept='N' 
set @Maindept='-'
insert into tblDepartment (DepartmentName,status
,isSubDept,MainDptID) values (@dname,@status, @isSubDept,@Maindept);
select @Dtid=cast(@did as varchar(50))
update tblcounter set deptidcounter=@did;

which throws an error

Server: Msg 201, Level 16, State 4, Procedure InsertDepartment, Line 0
Procedure 'InsertDepartment' expects parameter '@Dtid', which was not supplied.
Stored Procedure: Employee.dbo.InsertDepartment

the department table contains, deptid as primary key varchar(50), department name,status, issubdept char(1),maindptid varchar(50).
the tblcounter has a field which constantly updated when an new department is created.
please explain me where i went wrong and how to rectify it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 02:35:02

You have to call the SP with 5 parameters, not just 4.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 02:35:51

Or at least default the @dtid to null.

CREATE Procedure InsertDepartment (
@dname varchar(50)
, @status char(10)
, @isSubdept char(1)
, @Maindept varchar(10)
, @Dtid varchar(50) = NULL output
)
AS

E 12°55'05.25"
N 56°04'39.16"

san79
Starting Member

42 Posts

Posted - 2008-04-14 : 02:50:40

If i supplied Null it tells me that Null cannot be given, because i have marked this field as primary key, as well as if i supplied some number like 1 then it bounce back like
[code]
DECLARE @RC int
DECLARE @pDeptID smallint
DECLARE @pDName varchar(50)
DECLARE @pStatus char(10)
DECLARE @pIsSubdept char(1)
DECLARE @pMaindptid varchar(10)
SELECT @pDeptID = 1
SELECT @pDName = 'Admin'
SELECT @pStatus = 'P'
SELECT @pIsSubdept = 'N'
SELECT @pMaindptid = NULL
EXEC @RC = [Employee].[dbo].[InsertDept] @pDeptID OUTPUT , @pDName, @pStatus, @pIsSubdept, @pMaindptid
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Employee.dbo.InsertDept'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
PRINT ' Output Parameter(s): '
SELECT @PrnLine = ' @pDeptID = ' + isnull( CONVERT(nvarchar, @pDeptID), '<NULL>' )
PRINT @PrnLine
Server: Msg 515, Level 16, State 2, Procedure InsertDept, Line 18
Cannot insert the value NULL into column 'Deptid', table 'Employee.dbo.tblDepartment'; column does not allow nulls. INSERT fails.[\code]
i am checking with query analyzer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 03:02:58

Well then. You have to supply a parameter to the fifth parameter.
And make sure you have the parameters in correct order since you do not call the SP with same parameter names as the SP is declared as.

E 12°55'05.25"
N 56°04'39.16"

san79
Starting Member

42 Posts

Posted - 2008-04-14 : 08:06:21

quote:
Originally posted by Peso

Well then. You have to supply a parameter to the fifth parameter.

sorry sir , i am unable to understand your explanation, and also i am supplying the parameters in order only.

san79
Starting Member

42 Posts

Posted - 2008-04-14 : 08:11:24

i am really confused, i will try to explain my stored procedure
table
1)Deptid varchar(50)
2)Departmentname varchar(50)
3)status char(10)
4)isSubdept char(1)
5)Maindptid varchar(10)
table 2
fields tablename,counterid
department, deptidcounter
i will give input to only name,status,issubdept and maindptid the deptid should be automatically generated using the deptidcounter from the table table2. how to write a sp for this. hope this is more clear

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 08:11:27

quote:
Originally posted by san79

quote:
Originally posted by Peso

Well then. You have to supply a parameter to the fifth parameter.

sorry sir , i am unable to understand your explanation, and also i am supplying the parameters in order only.

No you are not.

See this line:

EXEC @RC = [Employee].[dbo].[InsertDept] @pDeptID OUTPUT , @pDName, @pStatus, @pIsSubdept, @pMaindptid

It should be this way:

EXEC @RC = [Employee].[dbo].[InsertDept] @pDName, @pStatus, @pIsSubdept, @pMaindptid, @pDeptID OUTPUT

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 08:56:22

Also make sure the variable used for taking OUTPUT value from SP is of same type as the returning value.I see here that you've a varchar parameter for returning id and you're using an integer variable to retrive it from sp. may i know the intention behind that?

san79
Starting Member

42 Posts

Posted - 2008-04-14 : 09:06:45

to mr.visakh, actually i was willing it to be like d1 , d2 as department id, buy retriving the counter value in integer format from one table and then do a cast or convert and concatenate with the constant character like 'D' and assign it to a varchar variable. hope you understand .
to mr.harsh actually i am testing this sp in sql query analyzer, i am not defining the order of the input params, it actually displays the window and for each parameter i have to enter the value, so i think some thing i have to do to work promptly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 09:23:30

quote:
Originally posted by san79

to mr.visakh, actually i was willing it to be like d1 , d2 as department id, buy retriving the counter value in integer format from one table and then do a cast or convert and concatenate with the constant character like 'D' and assign it to a varchar variable. hope you understand .
to mr.harsh actually i am testing this sp in sql query analyzer, i am not defining the order of the input params, it actually displays the window and for each parameter i have to enter the value, so i think some thing i have to do to work promptly

Not sure i follow you here. What i asked was why you are using int variables to take value out even when you are casting it to nvarchar inside procedure?

san79
Starting Member

42 Posts

Posted - 2008-04-14 : 09:31:37

Not sure me too, i was just trying to find some solution based on a single stored procedure. the scenario is the user have to enter the department name , its status, if it has subdepartment if so then the maindepartment id. the stored procedure should create a unique id for each department with 'D' as prefix and the department count is place in another table called tblcounter. which is update whenever an department is inserted. "'D' + (deptcountid+1)" shall be the new department id. hope this is some what clear my position

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 09:59:21

i was asking why these variables are declared int
DECLARE @RC int
DECLARE @pDeptID smallint

as they are used to retrieve value of @Dtid varchar(50) output parameter

san79
Starting Member

42 Posts

Posted - 2008-04-15 : 00:35:20

the pDeptid is used to retrieve values from the tblcounter , and @RC int is automatically generated, i dont know how it is created. i never used such a parameter in my sp. something is beyond my understanding.

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-16 : 08:49:36

You should pass the value of @Dtid value to the procedure,means total 5 parameter values.

chandan

san79
Starting Member

42 Posts

Posted - 2008-04-18 : 02:14:07

hi all
i have found the problem , what a stupid am i, sorry for troubling you guys, the actual problem arise from the tblcounter table. it has a null value in the field deptidcounter, so when i tried to execute this code
declare @did smallint
set @did=(select deptidcounter from tblcounter);
set @did=@did+1;
the did returns null , which in turn refused to be inserted in to the table because the deptid is the primary key field which dosenot allow nulls.
any way thanks for all sorry for the annoyance caused

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources