Dynamically determine datatype on an INSERT INTO statement
-
Newbie question...
I'm working on an INSTEAD OF trigger that needs to insert a varbinary() value if the trigger has determined the column is varbinary() or an nvarchar() value if the trigger has determined the datatype of the column is nvarchar().
In the code below, the @ciphertext variable is varbinary(300) and [inserted].[Name] is nvarchar(32). So, what I'd like to have happen is if there is non-NULL value in @ciphertext the trigger has previously determined the column is varbinary() and @ciphertext should be inserted; otherwise; otherwise the datatype of the column is nvarchar(32) and [inserted].[Name] should be inserted.
The insert croaks with the 'Implicit conversion from datatype...' error. I understand why, but I'm not sure how to work around this issue... Any ideas?
Tom
INSERT INTO [dbo].[Employees] (ID, [Name])
SELECT
--ID
[ID],
--Name
CASE WHEN @ciphertext <> NULL
THEN
@ciphertext
ELSE
[Name]
END
FROM inserted
-
try this
INSERT
INTO [dbo].[Employees] (ID, [Name])
SELECT
--ID
[ID],
--Name
CASE WHEN @ciphertext is not NULL
THEN
@ciphertext
ELSE
[Name]
END
FROM inserted
Siva
-
Your problem is CASE and implicit conversions..
A CASE expression can only return one datatype. Which datatype to be returned is determined by the datatype presedence. If you have multiple datatypes within the same CASE, and they are not implicitly convertable, you'll receive an error.
The 'workaround' is to decide which datatype the CASE should return, and explicitly cast/convert all deviating types to the preferred reurntype.
If returning a single datatype isn't possible, you can't use CASE, but instead find another formulation for your purpose.
/Kenneth
-
Not sure I understand this
First, use IS NOT NULL instead of <> NULL
Second, is the column [Name] nvarchar or not
if it is then
INSERT INTO [dbo].[Employees] (ID, [Name])
SELECT
--ID
[ID],
--Name
CASE WHEN @ciphertext IS NOT NULL
THEN
CAST(CAST(@ciphertext as varchar) as nvarchar)
ELSE
[Name]
END
FROM inserted
Third, if there is multiple inserts and @ciphertext IS NOT NULL then all the inserts will have the same [Name] value
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply