optional parameter to sp - 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

General SQL Server Forums

New to SQL Server Programming

optional parameter to sp

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-07-29 : 07:46:03

Hi all,

I need to modify following sp to have optional parameter. How can I add it so that if it is not provided then field is not updated otherwise update it: sp is:

ALTER PROCEDURE [dbo].[Update_Users] (
@UserId int,
@UserName varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Gender varchar(50),
@Email varchar(200),
@Fax varchar(50),
@PrefferedLanguage varchar(50),
@ReceiveEmails bit,
@ModifiedBy varchar(50),
@ModifiedOn datetime),
@IsWholesaler bit ( an optional parameter)

AS

SET NOCOUNT ON

UPDATE
[Users]
SET
[UserName] = @UserName,

// if @IsWholesaler has value then // how to check this
IsWholesaler = @IsWholesaler // how to add this
[FirstName] = @FirstName,
[LastName] = @LastName,
[Gender] = @Gender,
[Email] = @Email,
[FAX] = @Fax,
[PrefferedLanguage] = @PrefferedLanguage,
[ReceiveEmails] = @ReceiveEmails,
[ModifiedBy] = @ModifiedBy,
[ModifiedOn] = @ModifiedOn
WHERE
[UserId] = @UserId

Andre412
Starting Member

11 Posts

Posted - 2009-07-29 : 07:55:00

[code]
ALTER PROCEDURE [dbo].[Update_Users] (
@UserId int,
@UserName varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Gender varchar(50),
@Email varchar(200),
@Fax varchar(50),
@PrefferedLanguage varchar(50),
@ReceiveEmails bit,
@ModifiedBy varchar(50),
@ModifiedOn datetime),
@IsWholesaler bit = NULL

AS

SET NOCOUNT ON
IF @IsWholesaler IS NOT NULL
BEGIN
print 'provideed'
UPDATE
[Users]
SET
[UserName] = @UserName,
[IsWholesaler] = @IsWholesaler,
[FirstName] = @FirstName,
[LastName] = @LastName,
[Gender] = @Gender,
[Email] = @Email,
[FAX] = @Fax,
[PrefferedLanguage] = @PrefferedLanguage,
[ReceiveEmails] = @ReceiveEmails,
[ModifiedBy] = @ModifiedBy,
[ModifiedOn] = @ModifiedOn
WHERE
[UserId] = @UserId
END
ELSE
BEGIN
print 'not provided'
UPDATE
[Users]
SET
[UserName] = @UserName,
[FirstName] = @FirstName,
[LastName] = @LastName,
[Gender] = @Gender,
[Email] = @Email,
[FAX] = @Fax,
[PrefferedLanguage] = @PrefferedLanguage,
[ReceiveEmails] = @ReceiveEmails,
[ModifiedBy] = @ModifiedBy,
[ModifiedOn] = @ModifiedOn
WHERE
[UserId] = @UserId
END
[/code]

Check the syntax before you run it

me http://drayblog.gotdns.com
company http://www.lowcarboneconomy.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 07:55:12

[code]
SET IsWholesaler = isnull(@IsWholesaler , IsWholesaler )
[/code]

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-29 : 09:51:28

quote:
Originally posted by khtan

SET IsWholesaler = isnull(@IsWholesaler , IsWholesaler )

KH
[spoiler]Time is always against us[/spoiler]

COALESCE...COALESCE.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 18:46:56

Lazy ... lazy . . .

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources