SEO

#1 (**permalink**) 02-28-2008, 06:14 PM

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.

#2 (**permalink**) 02-28-2008, 06:14 PM

If conditional WHERE clause predicates is what you are looking for, you can
refer to Erland's article on the topic :
http://www.algonet.se/~sommar/dyn-search.html

--
--- Anith

#3 (**permalink**) 02-28-2008, 06:14 PM

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.

#4 (**permalink**) 02-28-2008, 06:18 PM

[posted and mailed, please reply in news]

Robert (rkelso@kelsointeractive.com) writes:
> 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.

UPDATE tbl
SET field1 = @value1,
field2 = @value2,
field3 = CASE len(@value3) > 0 AND @value IS NOT NULL
THEN @value3
ELSE field3
END
WHERE ...

No reason to even think about dynamic SQL.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Stored Procedure Syntax Help