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

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

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?
    ... I realised after I had posted that the dynamic-SQL ... I have re-read his bit about "Common Cases when to Use Dynamic SQL: ... SELECT * FROM @tablename", and don't entirely agree. ... I am writing a trigger for a table insert, and want to put the actual ...
    (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?
    ... but my understanding is that it only contains inserted records. ... -Inserted refers to the table the trigger is created on. ... I have re-read his bit about "Common Cases when to Use Dynamic ...
    (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)