Hi. Try with this example:

-- creating procedure...

drop procedure XYZ
go

create procedure XYZ
@value1 varchar(8000),
@value2 varchar(8000),
@value3 varchar(8000) = null,
@sql varchar(8000) output
as
set @sql = 'update'
set @sql = @sql + ' field1 = ''' + @value1 + ''''
set @sql = @sql + ',field2 = ''' + @value2 + ''''
If Len(@value3) > 0 set @sql = @sql + ',field3 = ''' + @value3 + ''''
set @sql = @sql + ' where ....'
go

-- testing procedure...

declare @arg1 varchar(8000)
declare @arg2 varchar(8000)
declare @arg3 varchar(8000)
declare @ret varchar(8000)

set @arg1 = 'v1'
set @arg2 = 'v2'
set @arg3 = 'v3'

execute XYZ @value1=@arg1, @value2=@arg2, @sql=@ret output
print @ret


execute XYZ @value1=@arg1, @value2=@arg2, @value3=@arg3, @sql=@ret output
print @ret




rkelso@kelsointeractive.com (Robert) wrote in message news:<4b78754b.0310071635.757466ad@posting.google. com>...
> I'm enhancing a large ASP application by replacing raw SQL statements
> in the code with calls to stored procedures. I've seen this
> recommended in many places to increase SQL Server effieicency.
> Replacing select statements is going fine but I hit a sticking point
> when trying to replace an update statement.
>
> Currently, I use this kind of statement a lot in my ASP:
>
> sql = "update"
> sql = sql & " field1 = value1"
> sql = sql & ",field2 = value2"
> If Len(value3) Then sql = sql & ",field3 = value3"
> sql = sql & " where ...., etc, etc
>
> The important part here is checking if "value3" has a value before
> inserting it into my SQL statement. So how can I put this update
> statement into a stored procedure, pass in values for value1, value2,
> and value3, and leave off the value3 part of the update if value3
> comes in as null.
>
> Thanks all.