SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 problem in Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jasmen
Starting Member

11 Posts

Posted - 02/11/2014 :  22:18:25  Show Profile  Reply with Quote
ALTER procedure [dbo].[ManageUserTypes]
(
@check nchar(1),
@UserTypeID nvarchar(10),
@TypeName nvarchar(10)

)
AS
if @check ='i' begin
INSERT INTO UserTypes
(UserTypeID ,TypeName )
VALUES (@UserTypeID ,@TypeName)
end


if @check='u' begin
UPDATE UserTypes
SET TypeName = @TypeName
where (UserTypeID=@UserTypeID)
end




if @check='d' begin
DELETE FROM UserTypes
where (UserTypeID=@UserTypeID)
end

when i execute the stored procedure it works fine on insert and update
the problem in delete i just want del by UserTypeID
its keep saying
Procedure or Function 'ManageUserTypes' expects parameter '@TypeName', which was not supplied.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2014 :  00:22:19  Show Profile  Reply with Quote
make the other parameter as optional in that case
like below

ALTER procedure [dbo].[ManageUserTypes]
(
@check nchar(1),
@UserTypeID nvarchar(10),
@TypeName nvarchar(10) = NULL

) 
AS
if @check ='i' begin
INSERT INTO UserTypes
(UserTypeID ,TypeName )
VALUES (@UserTypeID ,@TypeName)
end


if @check='u' begin 
UPDATE UserTypes
SET TypeName = @TypeName
where (UserTypeID=@UserTypeID)
end




if @check='d' begin 
DELETE FROM UserTypes
where (UserTypeID=@UserTypeID)
end

and while calling execute like this

EXEC [dbo].[ManageUserTypes] 'd',<value for usertypeid>


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jasmen
Starting Member

11 Posts

Posted - 02/12/2014 :  01:40:20  Show Profile  Reply with Quote
sorry do u mean like that CREATE TABLE [dbo].[UserTypes](
[UserTypeID] [nchar](10) NOT NULL,
[TypeName] [nchar](10) NULL,
CONSTRAINT [PK_UserTypes] PRIMARY KEY CLUSTERED
or how can i make it optional in t-sql

Edited by - jasmen on 02/12/2014 01:56:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/12/2014 :  05:26:55  Show Profile  Reply with Quote
quote:
Originally posted by jasmen

sorry do u mean like that CREATE TABLE [dbo].[UserTypes](
[UserTypeID] [nchar](10) NOT NULL,
[TypeName] [nchar](10) NULL,
CONSTRAINT [PK_UserTypes] PRIMARY KEY CLUSTERED
or how can i make it optional in t-sql


I meant stored procedure not the table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jasmen
Starting Member

11 Posts

Posted - 02/12/2014 :  12:32:12  Show Profile  Reply with Quote
pls can u write it
because i did it like this and i get the same error
DELETE FROM UserTypes
WHERE (UserTypeID = @UserTypeID OR @TypeName IS NULL)
end

Edited by - jasmen on 02/12/2014 12:57:49
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 02/12/2014 :  12:54:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us your EXEC code. The error is indicating you didn't pass the @UserTypeID column. If you don't intend to pass it for a delete, then you need to make the input parameter optional as viaskh16 mentioned. He showed how to do it already in his first reply. Check the input parameter section of his code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jasmen
Starting Member

11 Posts

Posted - 02/12/2014 :  13:18:54  Show Profile  Reply with Quote
tkizer,visakh16 thanks its working now
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 02/12/2014 :  17:15:34  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2014 :  08:08:58  Show Profile  Reply with Quote
quote:
Originally posted by jasmen

tkizer,visakh16 thanks its working now



so where did you go wrong?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36841 Posts

Posted - 02/13/2014 :  16:55:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by jasmen

tkizer,visakh16 thanks its working now



so where did you go wrong?




Jasmen missed your code change for the input parameter.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000