how do you make a parameter in an update sp optional

Question

how do you make a parameter in an update sp optional

adam spencer

Hall of Fame

Points: 3926
More actions
August 16, 2010 at 7:04 pm

#97251

how do you make a parameter in an update sp optional
I am trying to make a general update sp for a table.
i will never know in advance which fileds the users will want to update.
If they choose to update column3 for example, but not the others which do not allow NULLS,
how can I handle things. I have done a lot of google since I know the answer is simple.
or set the parm to the default of what it already was as some weird work around.
ok, so thanks in advance for the easy answer.
EXEC Sp_FansUpdate @Fan_ID=6,@LName='Noodlburgh',@Gender='female';
select * from tblFans
ALTER Proc Sp_FansUpdate
@Fan_ID int
,@FName varchar(50)
,@LName varchar(100)
,@Gender varchar(10)
,@Age tinyint
,@EmailAddress varchar(150)
,@FavSportsTeam varchar(50)
,@Favorite_Chapter varchar(50)
,@Fan_Type varchar(100)= NULL
,@FanComments varchar(max)= NULL
AS
SET NOCOUNT ON
UPDATE [GZK].[dbo].[tblFans]
SET [FName] = @FName
,[LName] = @LName
,[Gender] = @Gender
,[Age] = @Age
,[EmailAddress] = @EmailAddress
,[FavSportsTeam] = @FavSportsTeam
,[Favorite_Chapter] = @Favorite_Chapter
,[Fan_Type] = @Fan_Type
,[FanComments] = @FanComments
WHERE Fan_ID=@Fan_ID
RETURN
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

adam spencer Hall of Fame Points: 3926 More actions · Answer 1

someone wrote on another forum to just use ISNULL in case nothing is being updated in that column, but this still is not workinf for me? STill get an error that "FName expects a parm and none was givin"

ALTER Proc Sp_FansUpdate

@Fan_ID int

,@FName varchar(50)

,@LName varchar(100)

,@Gender varchar(10)

,@Age tinyint

,@EmailAddress varchar(150)

,@FavSportsTeam varchar(50)

,@Favorite_Chapter varchar(50)

,@Fan_Type varchar(100)

,@FanComments varchar(max)

AS

UPDATE [GZK].[dbo].[tblFans]

SET [FName] = ISNULL(@FName,[FName])

,[LName] = ISNULL(@LName,[LName])

,[Gender] = ISNULL(@Gender,[Gender])

,[Age] = ISNULL(@Age,[Age])

,[EmailAddress] = ISNULL(@EmailAddress,[EmailAddress])

,[FavSportsTeam] = ISNULL(@FavSportsTeam,[FavSportsTeam])

,[Favorite_Chapter] = ISNULL(@Favorite_Chapter ,[Favorite_Chapter])

,[Fan_Type] = ISNULL(@Fan_Type,[Fan_Type])

,[FanComments] = ISNULL(@FanComments ,[FanComments])

WHERE Fan_ID=@Fan_ID

RETURN

can someone help?

thanks in advance

adam spencer Hall of Fame Points: 3926 More actions · Answer 2

ANSWERED and works fine:

I needed to declare the parms as = NULL up top, then use the ISNULL at the bottom where values are being passed in at the SET. exmaple: SET [FName] = ISNULL(@FName,[FName])

ALTER Proc Sp_FansUpdate

@Fan_ID int

,@FName varchar(50) = NULL

,@LName varchar(100) = NULL

,@Gender varchar(10) = NULL

,@Age tinyint = NULL

,@EmailAddress varchar(150) = NULL

,@FavSportsTeam varchar(50)= NULL

,@Favorite_Chapter varchar(50)= NULL

,@Fan_Type varchar(100)= NULL

,@FanComments varchar(max)= NULL

,@SmallDateTimeofInsert smalldatetime = NULL

AS