Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

variable columns in select statements? Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2008 1:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:42 PM
Points: 5, Visits: 23
Hi there,

Scenario:

3 tables:
- config (defines e.g. which columns in which table should be written to timestamp table)
- mydata
- timestamp

I have to create an insert trigger for table mydata which does the following:

fetch the values from last insert into mydata and write some mydata columns, which are defined in the config table, into table timestamp with the following structure:
timestamp.tablename = 'mydata'
timestamp.fieldname = columnname
timestamp.date = actual date
timestamp.value = value of column

In the final version this should be done by a stored procedure called with tablename as parameter.

The majo problem now is to fetch the desired columns, which I get out of the config table, from the inserted row.
In the first attempt I used a loop over the selected fieldnames and tried to use @fieldname in "SELECT @fieldname FROM inserted".

But this doesn't work because variable column names don't seem to be allowed.

Another point is, that the columns to written into the timestamp table have different data type (integer, string, date, binary, text, ...)
Can I use text for the timestamp.value column and convert the data before inserting?


Any suggestions how to solve this problem?


Best regards,
cew3

Post #530539
Posted Wednesday, July 9, 2008 3:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 8, 2010 9:11 AM
Points: 55, Visits: 28
Am I right in understanding that the Trigger that gets called will call your stored procedure?

If this is the case, all you need to do is get the identity (Good practise is to always have a Identity Column anyway) that was insterted into the mydata table and pass it to the Stored Procedure. Then you can build a SQL String that you can modify with Variables.


Regarding the the Type in the timestamp column, you can use varchar or nvarchar and cast your value to a string?

Or if you want to, you could use sql_variant.

The choice is up to you.

Let me know if this was helpful!

Pierre
Post #530590
Posted Wednesday, July 9, 2008 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:42 PM
Points: 5, Visits: 23
Hi Pierre,

at the moment I'm tesing the complete functionality just in the trigger.
But this is not the main problem.
I fetched the identity from 'inserted' and could fetch now the complete row from mydata instead of inserted.

The major problem is that I can't build a SELECT with variable column name. Using @fieldname in the where clause is possible.

I tried already to build a complete query string and execuse with EXECUTE. Won't work....

In Sybase Advantage Database Server the following works fine:

@tbname = 'mydata';

DECLARE fieldlist CURSOR AS SELECT fieldname FROM config WHERE tablename = @tbname AND triggered = True;

OPEN fieldlist;

WHILE FETCH fieldlist DO
@fieldname = fieldlist.[fieldname];
@s = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue)
SELECT
'''+@tbname+''',
'''+@fieldname+''',
emrn_id,
now(),
'+@fieldname+'
FROM __new';
EXECUTE IMMEDIATE @s;
END WHILE;

// __new is equivalent to mssql's inserted


Any ideas?

cew
Post #530598
Posted Wednesday, July 9, 2008 4:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 8, 2010 9:11 AM
Points: 55, Visits: 28
Can you post the error that you are getting? Also the code in the Trigger so I can have a look at it?

In theory you can write something like this:
DECLARE @s varchar(2000);

SET @s = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue)'
SET @s = @s + 'SELECT '''+ @tbname + ''','''+@fieldname+''', , now(), ' + @fieldname + ' FROM ' + @tbname
SET @s = @s + 'WHERE = ' + @Identity

EXECUTE @s;

Ok, i have excluded the cursor too loop through each field etc. If you wish to get the old value just make sure you sue the BEFORE instead of AFTER statement.

If you are still having issues, please post code and error for me :)
Post #530601
Posted Wednesday, July 9, 2008 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:42 PM
Points: 5, Visits: 23
Hi,

i tried the folloewing:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER TRIGGER [InsTrigger] ON [dbo].[emrn]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON

DECLARE @tbname varchar(100)
DECLARE @fieldname varchar(50)
DECLARE @id varchar(100)
DECLARE @s varchar(2000)

select @tbname = 'emrn'

DECLARE triggeredfields CURSOR
FOR
SELECT fieldname
FROM dbconfig
WHERE tablename =@tbname AND triggered = 1

OPEN triggeredfields

FETCH NEXT FROM triggeredfields INTO @fieldname



WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @id = (SELECT emrn_id FROM inserted)

SET @s = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue) '
SET @s = @s + 'SELECT '''+ @tbname + ''','''+@fieldname+''','+@id+', now(), ' + @fieldname + ' FROM ' + @tbname + ' '
SET @s = @s + 'WHERE emrn_id = ' + @id

EXECUTE @s;

FETCH NEXT FROM triggeredfields INTO @fieldname
END

CLOSE triggeredfields
DEALLOCATE triggeredfields

END


Inserting a row into emrn will still produce the (german) error message:

Der Name 'INSERT INTO timestmp(tablename,fieldname,recordid, [timestamp],oldvalue) SELECT 'emrn','emrn_name',3,now(), emr_name FROM emrn WHERE emrn_id=3' ist keine gültiger Bezeichner.


if I run this query on console it works...


Regards
cew




Post #530673
Posted Wednesday, July 9, 2008 8:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 8, 2010 9:11 AM
Points: 55, Visits: 28
Lucky you that I am fluent in German ;)

Try using sp_executesql instead of Execute. Otherwise I can only recommend to move the bulk of the functionality into a Stored Procedure and calling that from the Trigger.

Let me know if the sp_executesql worked and if the Error message Changed.
Post #530850
Posted Thursday, July 10, 2008 2:16 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:55 AM
Points: 1,615, Visits: 2,118
If I recall correctly, the EXECUTE statement needs the @s to appear in parentheses, as follows:

EXECUTE (@s);

Steve
(aka smunson)
:):):)


Steve
(aka sgmunson)

Internet ATM Machine
Post #531956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse