June 21, 2013 at 8:47 am
I'm writing a stored procedure to update an employee in the employee table. Each employee in the table has 17 fields. The data is coming from a web form where 3 fields are used to identify the employee. If any other fields contain data, the employee needs to be updated, but only for those fields.
In my .net page, I concatenate together the SQL string based upon which form fields contain data. IE:
sql = "UPDATE notitiae.dbo.tblpeople SET"
If Request.Form("title") <> "" Then
sql = sql & " notitiae.dbo.tblPeople.Title = @title"
end if...
How do I duplicate this in my stored procedure? Do I create a local variable like @SQL and concatenate the string the same way? How then would I tell the procedure to process the string in the variable @SQL?
June 21, 2013 at 9:28 am
it.web (6/21/2013)
I'm writing a stored procedure to update an employee in the employee table. Each employee in the table has 17 fields. The data is coming from a web form where 3 fields are used to identify the employee. If any other fields contain data, the employee needs to be updated, but only for those fields.In my .net page, I concatenate together the SQL string based upon which form fields contain data. IE:
sql = "UPDATE notitiae.dbo.tblpeople SET"
If Request.Form("title") <> "" Then
sql = sql & " notitiae.dbo.tblPeople.Title = @title"
end if...
How do I duplicate this in my stored procedure? Do I create a local variable like @SQL and concatenate the string the same way? How then would I tell the procedure to process the string in the variable @SQL?
I am a little confused. If you are building your string in the front end why do you need to do it again in the backend? I would recommend using a stored procedure as your update and pass the parameters for all the columns. Be VERY careful if you are building these string in either the front end or the back end. If not handled correctly it is vulnerable to sql injection attack.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 9:31 am
I'm moving the code from front end to backend as stored procedure. I'm building these strings but they will not be vulnerable to SQL injection because the are parameterized.
what I'm trying to understand is how to correctly code the SQL in backend as I have in the front.
June 21, 2013 at 10:00 am
RCDAWebmaster (6/21/2013)
I'm moving the code from front end to backend as stored procedure. I'm building these strings but they will not be vulnerable to SQL injection because the are parameterized.what I'm trying to understand is how to correctly code the SQL in backend as I have in the front.
The values might be parameterized in your front end but if you are building a dynamic string in sql the result is no longer parameterized. Why do you need dynamic sql for an update? I would pass in all the columns and handle the logic in your update statement.
If you are deadset on using dynamic sql for this here is an example that will parameterize a variable.
declare @sql nvarchar(max)
declare @name sysname = 'tempdb'
set @sql = 'select * from sys.databases where name = @name'
exec sp_executesql @sql, N'@name sysname', @name
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 11:14 am
The reason I wanted to use dynamic SQL was to avoid making and running up to 14 seperate updates depending on which fields were completed on the web form. With dynamic SQL I can combine the multiple updates into one update.
if (not isdbnull("title"))
BEGIN
UPDATE notitiae.dbo.tblpeople SET notitiae.dbo.tblpeople.title = @title
WHERE notitiae.DBO.tblPeople.ID = @Employee_ID
END
if (not isdbnull("mname"))
BEGIN
UPDATE notitiae.dbo.tblpeople SET notitiae.dbo.tblpeople.mname = @mname
WHERE notitiae.DBO.tblPeople.ID = @Employee_ID
END
if I shouldn't use dynamic SQL then how do I handle this. What is Best Practice?
June 21, 2013 at 12:50 pm
RCDAWebmaster (6/21/2013)
The reason I wanted to use dynamic SQL was to avoid making and running up to 14 seperate updates depending on which fields were completed on the web form. With dynamic SQL I can combine the multiple updates into one update.if (not isdbnull("title"))
BEGIN
UPDATE notitiae.dbo.tblpeople SET notitiae.dbo.tblpeople.title = @title
WHERE notitiae.DBO.tblPeople.ID = @Employee_ID
END
if (not isdbnull("mname"))
BEGIN
UPDATE notitiae.dbo.tblpeople SET notitiae.dbo.tblpeople.mname = @mname
WHERE notitiae.DBO.tblPeople.ID = @Employee_ID
END
if I shouldn't use dynamic SQL then how do I handle this. What is Best Practice?
Something like this should work:
create procedure tblpeople_Update
(
@title varchar(20),
@fname varchar(25),
@mname varchar(25),
@lname varchar(25)
)
update tblpeople
set title = case when @title = '' then title else @title end,
fname = case when @fname = '' then fname else @fname end,
mname = case when @mname = '' then mname else @mname end,
lname = case when @lname = '' then lname else @lname end
where [SomeCondition]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 1:06 pm
so does that code not update the fields in the DB table if the parameter sent to the stored procedure coming from the web form is blank? if so, then that's much less code but will take some time to wrap my head around.
June 21, 2013 at 1:08 pm
RCDAWebmaster (6/21/2013)
so does that code not update the fields in the DB table if the parameter sent to the stored procedure coming from the web form is blank? if so, then that's much less code but will take some time to wrap my head around.
It does update those columns but it updates it to the current value. All it does is uses a case expression to determine if the "new" value should come from the parameter or the existing value in the column.
--edit--
fixed fat fingers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 21, 2013 at 1:11 pm
That makes sense. I am liking the stored procedures.
thanks for the help.
June 21, 2013 at 1:13 pm
RCDAWebmaster (6/21/2013)
That makes sense. I am liking the stored procedures.thanks for the help.
You're welcome. Glad that makes sense. It is a zillion times less complicated than building up a big nasty string to execute. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply