September 22, 2016 at 3:12 pm
Hi Gurus,
We have bit of weird issue at hand. Assume, we have two tables A and B and I am trying to insert values from B to A. And I am processing one value of B using function before inserting. In following query (pardon incorrect syntax, I am not on machine with SSMS at the moment, but gist is correct).
[highlight="#ffff11"]insert into tableB
(Col1B,Col2B,Col3B)
value
select ( col1A, parse(select MyFunction(col2A) as int),col3A) where col3A='SomeValue'.[/highlight]
The function is actually just splitting the parameter passes and returns first part of it, which is supposed to be integer. Now, most of the time this is working fine. But only some time the value in Col2B (which is returned by function and parsed as integer) is storing NULL. That too only for one row out of many processed in above query where col2A value in TableA is same, which is beyond me.
Now, if the function is returning null or non-integer value, PARSE would have failed. The only conclusion I have reached is function is NOT returning anything i.e. parse(select somevalue from sometable where 1=2) would show null.
Unfortunately, that sometime that it is failing is when client used it. Same processing when we did for testing before and after gets correct value in TableB
can someone point me to some direction. I am utterly clue-less at the moment.
Thanks in advance
September 22, 2016 at 4:08 pm
You haven't given us the definition of your function, you haven't given us sample data, you haven't given us expected results, and the code you've given us won't even parse, let alone execute, so how do you expect us to help you?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 22, 2016 at 4:19 pm
milindthakkar (9/22/2016)
...Now, if the function is returning null or non-integer value, PARSE would have failed.
...
That first part is not true. If the function returns NULL, then PARSE will return NULL. PARSE only fails with a NULL if it is a NULL constant passed.
From https://msdn.microsoft.com/en-us/library/hh213316.aspx:
Null values passed as arguments to PARSE are treated in two ways:
1. If a null constant is passed, an error is raised. A null value cannot be parsed into a different data type in a culturally aware manner.
2. If a parameter with a null value is passed at run time, then a null is returned, to avoid canceling the whole batch.
Here's an easy example demonstrating it:
--Assuming of course you do not have a database with ID one million,
--DB_NAME() will return NULL. PARSE does not fail, but returns NULL itself
SELECT PARSE(DB_NAME(1000000) AS int);
Cheers!
September 23, 2016 at 1:21 am
You could give the TRY_PARSE function a try (pardon the pun)
September 25, 2016 at 11:15 pm
milindthakkar (9/22/2016)
Hi Gurus,We have bit of weird issue at hand. Assume, we have two tables A and B and I am trying to insert values from B to A. And I am processing one value of B using function before inserting. In following query (pardon incorrect syntax, I am not on machine with SSMS at the moment, but gist is correct).
[highlight="#ffff11"]insert into tableB
(Col1B,Col2B,Col3B)
value
select ( col1A, parse(select MyFunction(col2A) as int),col3A) where col3A='SomeValue'.[/highlight]
You have a bit weird syntax here.
1. You're saying:
I am trying to insert values from B to A
and do something different:
insert into tableB
(Col1B,Col2B,Col3B)
2. "FROM" part is totally missing from your query.
3. I do not think
INSERT ...
VALUE ...
is a valid syntax.
Must be at list VALUES.
4. "VALUES select" - i don't think it could ever work.
5. SELECT ( whatever inside the brackets)
will return a single column which you're trying to insert into 3.
Not gonna work.
6. Considering correct query looks like this:
INSERT INTO tableB
(Col1B,Col2B,Col3B)
SELECT col1A, parse(MyFunction(col2A) as int),col3A
FROM [TableA]
WHERE col3A='SomeValue'.
7. MyFunction(col2A) - what does it return?
What does this query :
select MyFunction(col2A)
from TableA
return?
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply