September 23, 2010 at 12:20 pm
Hello,
is it possible to use a "variable" default value on a field.
I'll try to make it clear with an example.
I have a table "x" with the following fields:
company_id int
field1 char(20)
field2 varchar(100)
From within my application, I'm doing the following insert query:
INSERT INTO x (field1, field2) VALUES (?value1, ?value2)
I want the field "company_id" to be filled from a value that comes from a temporary table (company_id is either 1 or 2, based on the user that is logged in...)
I know you can use "functions" as a default value of a field BUT in functions you can't use temporary tables 🙁
Is there a workaround? (is there no such thing as "user global variables" like @@ROWCOUNT ?)
If no, I need to use "INSTEAD OF" triggers but I don't like them and I try to avoid them as much as possible...
Thanks in advance
September 23, 2010 at 12:25 pm
Why don't you use a stored procedure, pass in these values, and then let it look up based on the user and do the insert?
September 23, 2010 at 12:25 pm
INSERT INTO x (company_id, field1, field2)
SELECT company_id, @value1, @value2 FROM @TempTable
?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2010 at 12:37 pm
@ steve jones: how can a stored procedure help me? I can't use the return value of a stored procedure as default constraint, can I?
PS: I know what value to insert (it's in my temp table) but I don't know how to use it as a default value
@GilaMonster: I know that's a solution for one query but the problem is, I need to do fill up the new field for an existing application so I don't have to change only one query but about a million (I guess) 🙂
(background: an existing application works for holding data from one company but now a customer wants to store multiple companies in one database)
A lot of programming has been done but the only problem left are our insert queries.
Thanks for the replies so far!
September 23, 2010 at 12:52 pm
What Steve's suggesting is moving the query from the front end into a stored proc and calling that instead of running the query. Same amount of work as my solution I guess.
I don't think there's a quick fix for you here. You can change the front end to query the temp table and pass the value back, or move the inserts to stored procedure and do the check/insert there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2010 at 1:00 pm
Darn, that's what I was afraid of (no quick solution).
I allready have this (working) "INSTEAD OF" trigger:
DECLARE @company_id INT
IF OBJECT_ID('tempdb..#cyn_vars') IS NOT NULL
BEGIN
SELECT @company_id = (SELECT CAST(param_value AS INT) FROM #cyn_vars WHERE param_name = 'company_id')
END
ELSE
BEGIN
SELECT @company_id = 0
END
IF @company_id != 0
BEGIN
SELECT * INTO #INSERTED FROM INSERTED
UPDATE #INSERTED SET company_id = @company_id where company_id is null or company_id = 0
INSERT INTO mytablename
SELECT * FROM #INSERTED
DROP TABLE #INSERTED
END
ELSE
BEGIN
INSERT INTO mytablename
SELECT * FROM INSERTED
END
If I add such an "INSTEAD OF" trigger to every table, my problems are over.
Can you guys (experts? :-)) give me a reason why not to use an "INSTEAD OF" trigger?
I know every insert will be slightly slower but are there other consequences of using this "INSTEAD OF" trigger instead of a "normal" INSERT?
Thanks in advance!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply