Stored Procedure Syntax Help

[Robert]

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.

[Anith Sen]

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

[Mauro]

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


rk****@kelsointeractive.com (Robert) wrote in message news:<4b**************************@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.

[Erland Sommarskog]

[posted and mailed, please reply in news]

Robert (rk****@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, so****@algonet.se

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