Building dynamic update sql string
-
Hi,
I'm trying to write a dynamic sql string where a user selects a number of values on the form to update in the database. The form values get passed to a stored procedure. The sp checks which ones are not null and then builds the sql accordingly. The problem I am having is how to determine the comma delimter between the columns and values that need to be updated.
Many thanks
Reet
-
Reet - I think you will get more accurate/useful/quick answers if you post an example.
Post an example table structure, some example data for it, some example inputs, and how you want your data to look at the end...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Reet,
Is this a syntax issue? This is a skeleton sproc for dynamic SQL I've been using for awhile:
I rule of thumb that I follow is that when I have many selections it's better to use ranges and ensure that they are INTs if you have control over that.
Hope this helps
CREATE PROCEDURE dbo.Name_Of_Storedprocedure
(
@BDate VARCHAR(8),
@EDate VARCHAR(8),
@BCustomer VARCHAR(50) = NULL -- this represents optional parameter
)
AS
/***********************************************************************
************************************************************************
** Copyright Notice
**
** Procedure Name: dbo.Name_Of_Storedprocedure
** Description:
**
**
** Return values:
** Input Params:
** Output Params:
** Author: <>
** Date: <>
************************************************************************
************************************************************************
** Change History
************************************************************************
************************************************************************
** Date: Author: Description:
** -------- --------
---------------------------------------
**
************************************************************************
********************************************************/
DECLARE @Error INT
DECLARE @SQL NVARCHAR(nth)
DECLARE @WHERE VARCHAR(nth)
SET NOCOUNT ON
SET @WHERE = ''
IF RTRIM(@BDate) <> '' AND RTRIM(@EDate) <> ''
BEGIN
SET @WHERE = ' AND c.duedate BETWEEN ' + @BDate + ' AND ' + @EDate
END
IF RTRIM(LTRIM(@BCustomer)) <> CHAR(39) + '00000' + CHAR(39) -- CHAR(39) Represent sinlge quote
BEGIN
SET @WHERE = @WHERE + ' AND RTRIM(LTRIM(a.customernumber)) IN (' + @BCustomer + ')'
END
IF RTRIM(@WHERE) <> ''
BEGIN
SET @SQL = 'YOUR SELECT STATEMENT + @WHERE + ' ORDER BY CLAUSE '
--PRINT @SQL - Use for Debugging purposes
EXEC sp_executesql @SQL
END
RETURN
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
RETURN @Error
END
RETURN @Error
-
Hi,
Thanks for the info Joe but i'm not sure whether your code can help me on this occassion. Sorry, it's my fault as Ryan said I should have included code and examples.
What I am trying to do is build an update string like the following:
UPDATE tableA SET fieldA = @p1, fieldB = @p2, fieldC = @p3, fieldD = @p4 (this is the bit that I am having issues building dynamically)
WHERE.......so on and so on
There will be four parameters which will be passed to the stored procedure these can be of null value in which case I don't want to include them in the sql string.
@p1 = 'String 1'
@p2 = ''
@p3 = ''
@p4 = 'String 2'
declare @sql
@sql = 'UPDATE TableA SET'
I can fiter null values with the following:
if isNull(@p1)
begin
select @sql = @sql + ' fieldA =' @p1
end
However, my issue is the comma which has to follow if there is another value following.
I could add it in the string before the next input as follows:
if isNull(@p4)
begin
select @sql = @sql + ', fieldD =' @p4
end
This would work. However when @p4 is the only not null value the sql would end up as:
UPDATE tableA SET , field4 = 'String 2' The comma would cause an error.
This is what my issue is and I hope that I have explained it a bit better. CAn anyone help?
Many thanks
Reet
-
You would just need to chop off the comma at the end. e.g.
--objects
create proc #sp1 @p1 varchar(10), @p2 varchar(10), @p3 varchar(10), @p4 varchar(10) as
declare @s-2 varchar(100)
set @s-2 = 'UPDATE tableA SET '
+ isnull('fieldA = ''' + @p1 + ''', ', '')
+ isnull('fieldB = ''' + @p2 + ''', ', '')
+ isnull('fieldC = ''' + @p3 + ''', ', '')
+ isnull('fieldD = ''' + @p4 + ''', ', '')
set @s-2 = left(@s, len(@s) - 1)
print @s-2
go
--calculation
exec #sp1 'String 1', null, null, 'String 2'
--tidy up
drop proc #sp1
/*results
UPDATE tableA SET fieldA = 'String 1', fieldD = 'String 2'
*/
However, why not avoid dynamic SQL altogether (recommended), and just do this?
UPDATE tableA
SET
fieldA = isnull(@p1, fieldA),
fieldB = isnull(@p2, fieldB),
fieldC = isnull(@p3, fieldC),
fieldD = isnull(@p4, fieldD)
Anyway, here's some reading material for you...
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Reet,
My mistake, I thought you were stuck on syntax.
Not a problem, As Ryan mentioned by using the ISNULL function you can accomplished what you're asking for.
Avoid dynamic SQL whenever possible. Is this for .NET or Java?
Thanks,
-
Thanks Ryan,
You're a star!
To be honest I never even thought about just using:
UPDATE tableA
SET
fieldA = isnull(@p1, fieldA),
fieldB = isnull(@p2, fieldB),
fieldC = isnull(@p3, fieldC),
fieldD = isnull(@p4, fieldD)
I'll give that a go first, but thank you very much for giving me a solution for the other.
Cheers,
Reet
-
Hey Joe,
Thanks for your input too. I'm sure that code will come in handy at some point so thank you for taking the tiem to help me out.
Cheers
Reet
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply