Re: Can I Pass a Table Name as a Parameter?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Bob

You specified that the column names would be different.

I did, yes, but I could certainly live with fixed column names. My primary
interest is the table/table name.

-Inserted refers to the table the trigger is created on.

Yes, but my understanding is that it only contains inserted records. What I
would want to be able to do is test the inserted records against existing
records to ensure that the inserted records did not break any rules.

-udf's can be used from within a trigger.

Are you saying, then, that I can refer to inserted in a udf and it will
return the inserted rows?

Thanks

Charles


"Bob McClellan" <bobmcc777@xxxxxxxxx> wrote in message
news:F4D6C0E8-2F39-451E-B384-CE3746BEAED7@xxxxxxxxxxxxxxxx
You specified that the column names would be different.
.. I would think that you would need to handle each table differently.

not clear on the rest of your post.
-Inserted refers to the table the trigger is created on.
-udf's can be used from within a trigger.



"Charles" <blank@xxxxxxxxxxx> wrote in message
news:OKPsDqt2JHA.4412@xxxxxxxxxxxxxxxxxxxxxxx
I haven't tried this, and I suspect it won't work, but wouldn't it be
useful if inserted and deleted were accesible to a UDF called from a
trigger. The trigger could call a generic function, which would be able to

SELECT * FROM inserted

and it would reference the appropriate table. All we would need then is a
'current' object that pointed to the table on which the trigger was
called, and it's job done.

Charles


"Bob McClellan" <bobmcc777@xxxxxxxxx> wrote in message
news:D2600232-2D55-4710-9C20-5A1A0281207F@xxxxxxxxxxxxxxxx
Dave,
If you know the tables that will be targeted in the
stored procedure, perhaps something like this would work..

Create
Proc SearchFromSomeTable
@tableName nvarchar(50)
as

if @tableName = 'TableA'
Begin
Select *
From TableA
End

if @tableName = 'TableB'
Begin
Select *
From TableB
End




"Charles" <blank@xxxxxxxxxxx> wrote in message
news:uc26wGt2JHA.4632@xxxxxxxxxxxxxxxxxxxxxxx
Hi Dave

Thanks for the reply. I realised after I had posted that the
dynamic-SQL solution would probably be suggested, but you have also
answered the question, I think, by saying that it is the _only_ way.

I have read Erland's article on this subject before, and take on-board
the reasons for avoiding dynamic-SQL if possible.

I have re-read his bit about "Common Cases when to (Not) Use Dynamic
SQL: SELECT * FROM @tablename", and don't entirely agree. I think there
are still occasions when, despite the fact that all tables are
different entities, the definition and purpose of the table is not
unique, by design. The only difference is that the table name and a
couple of column names differ, but the rules governing what is legal in
the table are common. In such a case it would seem much more efficient,
and less error-prone to be able to call common code in a trigger or
check constraint.

Perhaps Erland will reply and I can have the discussion with him too.

Cheers

Charles


"Dave Ballantyne" <symlink@no5p323mmer> wrote in message
news:uUOucts2JHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
Basically , no. But yes with dynamic SQL

See this link for more info :
http://www.sommarskog.se/dynamic_sql.html

--
Dave Ballantyne
http://sqlandthelike.blogspot.com/
Charles wrote:
Hi all

I realise this doesn't work as I have written it, but it demonstrates
what I would like to be able to do:

declare @TableName varchar(MAX)
set @TableName = 'MyTable'

select *
from @TableName

I am writing a trigger for a table insert, and want to put the actual
detail in a function. This function would be called from many
triggers because there are many tables that require this type of
check.

Is there a way in which I can pass a table name (and, indeed, column
name) to a function so that the actual code is then generic?

It seems to me that this is not an uncommon thing to want to do, but
I haven't found any reference yet. I will continue to look, but if
anyone has a quick answer I would be most grateful.

TIA

Charles








.

Relevant Pages

  • Re: Can I Pass a Table Name as a Parameter?
    ... but my understanding is that it only contains inserted records. ... Select * from @TableName ... it would be quite a mess of dynamic SQL pulling various Column names and definition from syscolumns to create more dynamic SQL. ... -Inserted refers to the table the trigger is created on. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I Pass a Table Name as a Parameter?
    ... I'm not even sure a check constraint is the right way to go. ... Surely any rule you create writing a trigger can be replicated within a SP ... I have re-read his bit about "Common Cases when to Use Dynamic ... SELECT * FROM @tablename", ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I Pass a Table Name as a Parameter?
    ... but my understanding is that it only contains inserted records. ... Select * from @TableName ... -Inserted refers to the table the trigger is created on. ... on-board the reasons for avoiding dynamic-SQL if possible. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I Pass a Table Name as a Parameter?
    ... if inserted and deleted were accesible to a UDF called from a trigger. ... I realised after I had posted that the dynamic-SQL ... I have re-read his bit about "Common Cases when to Use Dynamic SQL: ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I Pass a Table Name as a Parameter?
    ... The reason I want to use a trigger is so that I can check other rows in the ... I have re-read his bit about "Common Cases when to Use Dynamic SQL: ... SELECT * FROM @tablename", and don't entirely agree. ...
    (microsoft.public.sqlserver.programming)