passing tablename to proc from trigger

------------------------

I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.

Can it be done ?

trigger x on table y
for update
as
declare @.table_name
select @.table_name = get underlying table_name 'y' from somewhere
exec stored proc (@.table_name)

where do I find the name of the table the current trigger is defined on ?

thanksI want to make a birthday cake out of nitroglycerin.
Sometimes the question is not "can it be done", but "should it be done".
Don't put this kind of crap in a trigger.|||Why don't you tell us what your trying to acccomplish instead of trying to force a technical solution that doesn't seem to be a "best practice" kind of thing

Blind dude, did they base rat on you?|||Huh? Rat? Base? Huh?

passing tablename as parameter to function and to use it dynamically

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

Quote:

Originally Posted by satish@.entech.us

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

I don't think you can do this because DynamicSQL has it's own scope. Which means it won't return anything to the function. It will just run. So you can't do

SET @.Return = EXEC @.Command

As they are two different scopes.

What you could do is store the results of the output to a temp table and interrogate that when the function completes.

Cheers
C

passing table name as parameter for SP - asap

Hi all,
i have SP where i need to pass the tablename and the date as the
parameter
i started like this
CREATE PROC SP_DEL_STG_TAB
@.TABLE_NAME varchar(50) ,
@.PRESENT_REC_LD_DT DATETIME
AS
EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
@.PRESENT_REC_LD_DT)
GO
when i complied i did not find error but during the execution by passing
the parameters using
EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
it gives an wiered error
Line 1: Incorrect syntax near '3'.
please help me on this as early as possible i am stuck at this point.
thanks in advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
Use YYYYMMDD format for dates.
"sushma B V" <bv.sushma@.iflexsolutions.com> wrote in message
news:%23msW6xBFFHA.624@.TK2MSFTNGP15.phx.gbl...
>
> Hi all,
> i have SP where i need to pass the tablename and the date as the
> parameter
> i started like this
> CREATE PROC SP_DEL_STG_TAB
> @.TABLE_NAME varchar(50) ,
> @.PRESENT_REC_LD_DT DATETIME
> AS
> EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
> @.PRESENT_REC_LD_DT)
> GO
> when i complied i did not find error but during the execution by passing
> the parameters using
> EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
> it gives an wiered error
> Line 1: Incorrect syntax near '3'.
> please help me on this as early as possible i am stuck at this point.
> thanks in advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Try to use a string variable and build the query, then execute it using exec
command
Babu M K
"sushma B V" <bv.sushma@.iflexsolutions.com> wrote in message
news:%23msW6xBFFHA.624@.TK2MSFTNGP15.phx.gbl...
>
> Hi all,
> i have SP where i need to pass the tablename and the date as the
> parameter
> i started like this
> CREATE PROC SP_DEL_STG_TAB
> @.TABLE_NAME varchar(50) ,
> @.PRESENT_REC_LD_DT DATETIME
> AS
> EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
> @.PRESENT_REC_LD_DT)
> GO
> when i complied i did not find error but during the execution by passing
> the parameters using
> EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
> it gives an wiered error
> Line 1: Incorrect syntax near '3'.
> please help me on this as early as possible i am stuck at this point.
> thanks in advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

passing table name and order by parameter to stored procedure

can i pass the name of the table and the "order by" column name to stored procedure?

i tried the simple way

(@.tablename varchar and then "select * from @.tablename)

but i get error massesges. the same for order by...

what is the right syntex for this task?

You have to use dynamic SQL for this task, unfortunately. You have to create a character string in your sproc that puts together the SQL statement and then you have to call exec(@.sql) on it.

Example:

@.sql = 'SELECT * FROM ' + @.tableName

exec(@.sql)

|||

well, i just started to use stored procedure, i think that i will leave dynamic sql to later on...