May 10, 2016 at 3:43 pm
Hi All,
have the follwoing stored prcoedure
Create procedure Test
(
@TestDate1 smallDatetime = NULL,
@TestDate2 smallDatetime = NULL,
@TestDate3 smallDatetime = NULL,
)
AS
Update TestTable
Set
@Col1 = COALESCE(@TestDate1, @TestDate2, @TestDate3)
I am passing
Exec Test 12/23/2011, '', ''
Nothing is getting updated. I am passing the date as a string from my .net code. Do I need to pass it as a datetime since there is no smallDatetime .net side.
May 10, 2016 at 3:44 pm
anjaliagarwal5 (5/10/2016)
Hi All,have the follwoing stored prcoedure
Create procedure Test
(
@TestDate1 smallDatetime = NULL,
@TestDate2 smallDatetime = NULL,
@TestDate3 smallDatetime = NULL,
)
AS
Update TestTable
Set
@Col1 = COALESCE(@TestDate1, @TestDate2, @TestDate3)
I am passing
Exec Test 12/23/2011, '', ''
Nothing is getting updated. I am passing the date as a string from my .net code. Do I need to pass it as a datetime since there is no smallDatetime .net side.
Try this:
Exec Test '20111223', '', ''
WARNING: Also, there is NO WHERE CLAUSE on your UPDATE statement. This will update EVERY ROW in your table.
Actually, it won't update your table as you aren't setting a column in the table.
May 11, 2016 at 3:08 am
You should take another look at the syntax for the UPDATE statement.
As Lynn says, you're missing quite a bit there. Your statement should look something more like:
UPDATE dbo.MyTable
SET Column1 = '42'
WHERE Column2 = '242';
I wouldn't worry about manipulating variables until you have the basic syntax down properly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2016 at 9:56 am
My update statement is working if I am passing NULL instead of empty strings.
May 11, 2016 at 10:24 am
anjaliagarwal5 (5/11/2016)
My update statement is working if I am passing NULL instead of empty strings.
An empty string is not the same as a NULL string. An empty string is converted to '1900-01-01 00:00:00'. You should either pass in NULL strings instead of empty strings or convert empty strings to NULL using NULLIF().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 11, 2016 at 10:33 am
anjaliagarwal5 (5/11/2016)
My update statement is working if I am passing NULL instead of empty strings.
The UPDATE statement you have shown is wrong. And again, what you have shown as no WHERE clause and will update EVERY ROW in the target table if the SET clause is done correctly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply