September 17, 2012 at 12:42 pm
Has anyone come into a situation like this?
I have an application that is passing in 2 parameters, a string with column names and a string with values
I parse out the columns names and parse out the values and dynamically create an UPDATE statement from them.
The problem I'm having is, when the user removes a date in a field, the application sends back an empty string and when I update the datefield with an empty string, it defaults to 1/1/1900.
I can't figure out how to get that empty string to a NULL in dymanic SQL and I dont' want to convert the date fields to varchar b/c then they don't sort properly.
Any ideas?
Thanks!
September 17, 2012 at 12:47 pm
TecheeGirl (9/17/2012)
Has anyone come into a situation like this?I have an application that is passing in 2 parameters, a string with column names and a string with values
I parse out the columns names and parse out the values and dynamically create an UPDATE statement from them.
The problem I'm having is, when the user removes a date in a field, the application sends back an empty string and when I update the datefield with an empty string, it defaults to 1/1/1900.
I can't figure out how to get that empty string to a NULL in dymanic SQL and I dont' want to convert the date fields to varchar b/c then they don't sort properly.
Any ideas?
Thanks!
That is because '' converts to 1/1/1900 in the implicit conversion to a datetime datatype. Whenever possible you should use datatypes with the proper datatype. From your description I would be pretty careful because the type of thing you are describing is a typical process that is open to sql injection attack. If you want some help in that regard you will need to post your procedure code.
To directly answer the question and not provide my own shuddering at what sounds like a scary piece of code, you can get around this using a case expression.
declare @ParamWithWrongDatatype varchar(500)
update tablename set datecolumn = case when @ParamWithWrongDatatype IS null then null else @ParamWithWrongDatatype end
_______________________________________________________________
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/
September 17, 2012 at 1:03 pm
You make a good point. I was trying to create a stored procedure that could be used on multiple forms for multiple tables instead of creating a procedure for each table. You can see my case statement commented out below trying different ideas to bypass the '1/1/1900'
The execution would look something like this:
EXEC sp_UpdateRecord 'tbLTABLENAME','SubmittedDate,ReviewedAftDate,Coordinator,Impacted,RecordsImpacted,UpdatedOn,UpdatedBy','12/2/2011,,,test,,9/17/2012 10:59:20 AM,40','XXXX-10421'
DECLARE@N INT
, @TableNVARCHAR(50)
, @ColumnsNVARCHAR(4000)
, @ValuesNVARCHAR(4000)
, @keynvarchar(100)
SET NOCOUNT ON
--===== Add start and end commas to the Parameter so we can handle
SET @Columns = ','+@Columns +','
SET @Values = ','+@Values +','
DECLARE @sql nvarchar(MAX)
DECLARE @SQLUPDATE nvarchar(MAX)
SET @N = 1
SELECT @SQLUPDATE = ''
SELECT @sql = ''
/******************************************/
-- Get the values
/******************************************/
;WITH cteVALUES AS (
SELECT ID
, Value
FROM (
SELECT row_number() over (order by N) as ID, SUBSTRING(@Values,N+1,CHARINDEX(',',@Values,N+1)-N-1) AS Value
FROM dbo.Tally
WHERE N < LEN(@Values)
--Don't include the last comma
AND SUBSTRING(@Values,N,1) = ',' --Notice how we find the comma
)A
), -- End cte
/*****************************************/
-- Get the columns
/*****************************************/
cteCOLUMNS AS (
SELECT ID
, ColumnName
FROM (
SELECT row_number() over (order by N) as ID, SUBSTRING(@Columns,N+1,CHARINDEX(',',@Columns,N+1)-N-1) AS ColumnName
FROM dbo.Tally
WHERE N < LEN(@Columns)
--Don't include the last comma
AND SUBSTRING(@Columns,N,1) = ',' --Notice how we find the comma
)A
) -- End cte
/***** Create the UPDATE portion of the SQL String ****/
SELECT @SQLUPDATE = @SQLUPDATE +
c.ColumnName + ' = ''' + v.Value
--CASE
--WHEN LEN(v.Value) = 0 THEN coalesce(v.Value, NULL)
--WHEN ISDATE(v.Value) = 1 AND v.Value <> '1/1/1900' THEN v.Value
--WHEN v.Value = '1/1/1900' THEN
--Convert(Varchar, '', 101)
--ELSE v.Value
--END
+ ''','
FROM cteColumns c
JOIN CteValues v
ON C.ID = V.ID
ORDER BY c.ID
/***** Create the SQL String ****/
SELECT @sql = 'UPDATE ' + @Table + ' ' +
' SET ' + LEFT(@SQLUPDATE, LEN(@SQLUPDATE) - 1)+
' WHERE CNRRNumber = ''' + @key + ''''
/************* EXECUTE THE SQL *******************/
EXECUTE sp_executesql @sql
September 17, 2012 at 1:15 pm
Yeap this is wide open to sql injection. Consider passing in "SomeRealTableName; drop table SomeRealTableName; --". You will be far better off creating a different update sproc for each table. Not only will you not have to create the "one sproc to rule them all", you will get far better performance.
_______________________________________________________________
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/
September 17, 2012 at 1:22 pm
Thank you!
Here I thought I was being slick!
Oh well!
September 17, 2012 at 1:27 pm
Well you can build dynamic sql so that is uses parameters to help prevent sql injection but given the nature of what you are doing I think you will find the multiple procedures faster, safer, and far easier to maintain.
_______________________________________________________________
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply