Passing Tablename To Proc From Trigger
Mar 23, 2007
--------------------------------------------------------------------------------
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 ?
thanks
View 3 Replies
ADVERTISEMENT
May 6, 2015
I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.
So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROM ? ......
Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:
[Task Execute SQL] Error:
Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract
the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.
how to pass a tablename as a variable to a query?
View 5 Replies
View Related
Jul 19, 2004
possible to pass in a query as a parameter to a stored proc?
Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help.
Code:
CREATE PROCEDURE [dbo].[s_DeletePeople]
@PeopleID int
/* single id to be deleted, tried just passing
* in a query that resembled a string and using it
* but it didn't work either.
*/
AS
Deletefrom tProjectManager
whereManager_ID in (@PeopleID)
Deletefrom tMerchandiser
whereID in (@PeopleID)
Deletefrom tProjectCall
wheremerchandiser_id in (@PeopleID)
Delete from tManager
whereID in (@PeopleID)
Deletefrom tDistrictManager
whereID in (@PeopleID)
Deletefrom tPeople
whereID in (@PeopleID)
....and on and on
GO
View 2 Replies
View Related
Jan 10, 2007
Guys
I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)
create proc upGetDates
as
declare @start datetime ,
@end datetime
select@start = '2001-01-01' ,
@end = '2007-12-31'
go
I want to reuse this proc many times for different reports and using the date values in the calling sp
an example in pseudo code would be
create proc usedates
as
declare@rstart datetime ,
@rend datetime
exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc
select * from tablename where datecreated >= @rstart and datecreated < @rend
go
Not sure how to 'trap' the dates from the called proc
Can it be done ?
Thanks in anticipation
View 3 Replies
View Related
Sep 7, 2006
I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance
View 2 Replies
View Related
Oct 23, 2007
I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :
Where actv.ProjID + '-' + actv.Activity = @project
This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!
View 3 Replies
View Related
Nov 8, 2007
Hi,
I have a stored proc which accepts a varchar(255) as a parameter and when I call the proc using a concatenised string I get an error i.e.
-- Proc
CREATE PROCEDURE #proc_param_test
@p_param1 varchar(40) = NULL
, @p_param2 varchar(40) = NULL
AS
BEGIN
SELECT @p_param1, @p_param2
END
EXEC #proc_param_test 'test', 'test 2'
returns
---------------------------------------- ----------------------------------------
test test 2
but EXEC #proc_param_test 'test', 'test 2' + ' - the rest'
gives a Incorrect syntax near '+'. error
The solution must be a real doddle but it's a 'mare to find anywhere.
Cheers,
John
View 4 Replies
View Related
May 2, 2008
What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!
View 5 Replies
View Related
Mar 17, 2004
Hi I was hoping that someone might be able to help me with this.
I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0
myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)
I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.
Thanks in advance for any advice.
View 4 Replies
View Related
Nov 6, 2002
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.
TIA,
View 3 Replies
View Related
Jan 11, 2005
Hi,
I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.
Any suggestions?
Thanks
My plan is to have the same ability as under the 'Optional' section of this page:
http://search1.workopolis.com/jobshome/db/work.search_cri
View 1 Replies
View Related
Jun 12, 2006
Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
View 3 Replies
View Related
Jul 30, 2007
Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
Thanks.
View 3 Replies
View Related
Aug 8, 2002
i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.
for example i want to use
select p1 = sp_diff d1,d2 from table1
sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value
View 1 Replies
View Related
Jul 21, 2015
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Now I have to pass multiple param values to it. I was trying this but didnt get any success
exec
[SP_test]'LOC','LOP'
View 10 Replies
View Related
Jun 12, 2008
Hi all,
I have a store procedure which updates one row of a table. I need to use a trigger, so that it will also update another row in the same table. Like Debit and Credit. Somehow I am not able to pass variables from SP to trigger. Below are my SP and Trigger codes.
STORE PROCEDURE ALTER PROCEDURE dbo.Transfer
(@online varchar(50),
@amount smallmoney,@toaccount varchar(10)
)
AS
Begindeclare @total smallmoney declare @total1 smallmoney
set @total1=1
select @total=TotalAmount from Customers where OnlineID=@online
if @total>=@amount
set @total=dbo.Calulator(@total,@amount)
else
set @total1=0
End
update Customers set TotalAmount=@total where OnlineID=@online
RETURN @total1
TRIGGER ALTER TRIGGER dbo.Updcust ON Customers
FOR update
AS
BEGINdeclare @total2 smallmoney
declare @total3 smallmoneydeclare @amount smallmoney
declare @toaccount varchar(10)
select @total2=TotalAmount from Customers where AccountNumber=8776450012
set @total3=@total2+@amount
UPDATE Customers set TotalAmount=@total3 where AccountNumber=8776450012
END
please help
View 4 Replies
View Related
Jan 10, 2007
How do i pass parameter to trigger.iam using trigger to delete from multiple tables here is the code
Create trigger DeletePartyName on PartyMaster_Gen
(@PartyName varchar(50))
for delete
as
DELETE FROM PartyMaster_Address WHERE PartyName=@PartyName
DELETE FROM PartyMaster_DEPB WHERE Party_Name=@PartyName
DELETE FROM PartyMaster_Gen WHERE Party_Name=@PartyName
I want to pass @partyname parameter to this query. how do i do it.
View 4 Replies
View Related
Jul 20, 2015
I wanted to execute a proc whenever a new login or a login is droped is created on the server. I was trying for CREATE_LOGIN in the following code but not able to capture the loginname and pass it to the proc.
Server object Trigger:-
USE [master]
GO
/****** Object: DdlTrigger [LoginCreateTrigger] Script Date: 7/20/2015 3:41:06 AM ******/
SET ANSI_NULLS ON
GO
[code]...
View 3 Replies
View Related
Mar 28, 2008
s it poaaible? as if so how can i trigger a job from a stored procedure?
View 2 Replies
View Related
Nov 29, 2007
I am fairly new to using triggers and stored procs in SQL Server and could use some help.
Scenario:
I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.
Questions:
1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?
2) Can a Stored Proc be used in a trigger?
3) Can a Stored Proc call an outside function, such as an API call for third party software?
Thanks in advance.
View 1 Replies
View Related
Sep 17, 2004
Is it possible to call an external web service from a SQL Server trigger or stored procedure?
View 6 Replies
View Related
Dec 7, 2004
Hello SQL Server programming gurus:
I am trying to create a trigger that fires after a user logon and logoff and does the following:
creates a new user
deletes data from old temp table
Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access
We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?
View 1 Replies
View Related
Jul 23, 2005
I feel like I'm missing something obvious here, but I'm stumped...I have a stored procedure with code that looks like:INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)IF (@@ERROR <> 0)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen
View 1 Replies
View Related
May 7, 2008
I have a database trigger that is set to call a CLR trigger/stored proc when a certain field in a table is updated. The issue is that if i execute the stored proc manually in enterprise studio, it works perfectly but the same call made through the trigger does not go through. A few more details -
I have CLR integration enabled on the sql server.
The dbo has UNSAFE ASSEMBLY rights
I have the both the assembly and the serialized dll imported in the database.
Here's the definition of the stored proc -
CREATE PROCEDURE [dbo].[WriteXMLNotification]
@TaskID [nvarchar](20)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DataInterfaceWebServices].[TaskUpdateXMLWriter.WriteXMLNotification].[run]
and the trigger -
CREATE TRIGGER [dbo].[tr_Task_U]
ON [dbo].[_Task]
FOR UPDATE, INSERT AS
IF UPDATE (TaskType_Status) OR UPDATE (TaskType) OR UPDATE (TaskType_SubType1)
BEGIN
SET NOCOUNT ON;
DECLARE @status AS INT
DECLARE @taskType AS INT
DECLARE @taskSubType AS INT
DECLARE @taskID as sysname
DECLARE @cmd as sysname
DECLARE @parentTask as sysname
DECLARE @NotificationXMLTaskID as sysname
SELECT @status = [TaskType_Status] FROM inserted
SELECT @taskType = [TaskType] FROM inserted
SELECT @taskSubType = [TaskType_SubType1] FROM inserted
SELECT @taskID = [TaskID] FROM inserted
SELECT @parentTask = [Parent_TaskID] FROM inserted
SELECT @NotificationXMLTaskID = [MCCTaskID] FROM _TaskNotificationXML WHERE [MCCTaskID] = @parentTask
IF (@status = 2602) AND (@taskType = 2282) AND (@taskSubType = 19500)
BEGIN
exec WriteXMLNotification @taskID;
END
ELSE IF (@taskType = 2285) AND (@parentTask IS NOT NULL) AND (@NotificationXMLTaskID IS NOT NULL)
BEGIN
exec WriteXMLNotification @parentTask;
END
END
I stepped into the trigger and it seems to execute the line " exec WriteXMLNotification @taskID;" but nothing happens, but if I run that same line manually, it works. Could it be that the impersonation by the EXECUTE AS clause is causing it to fail?
Please advise!
Thanks in Advance,
-Mihir Sonalkar.
View 1 Replies
View Related
Feb 16, 2005
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
View 4 Replies
View Related
Feb 18, 2008
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
INSERT #Scorecard_Returns(
NAME_COL ,
ACCT_ID
ACCT_NUMBER ,
ENTITY_ID,
NAME,
ID,
NUM_ACCOUNT,
A_OFFICER,
I_OFFICER,
B_CODE,
I_OBJ ,
LAST_MONTH
LAST_3MONTHS,
IS
)
EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns
@nId = 1,
@sSUser = 'SELECTED USER',
@sUName = 'VALID USER'
View 4 Replies
View Related
Jul 17, 2001
Hello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
View 1 Replies
View Related
Feb 12, 2005
i need to develop a stored procedue, in ehich i have to use variable table name.. as Select * from @tableName but i m unable to do so..
it says u need to define @tablename
heres da code
CREATE PROCEDURE validateChildId
(
@childId int,
@tableName varchar(50),
@fid int output
)
AS
(
SELECT @fid=fid FROM @tableName
where @childId= childId
)
if @@rowcount<1
SELECT
@fid = 0
GO
View 4 Replies
View Related
Aug 2, 2007
Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
View 2 Replies
View Related
Jul 20, 2000
Does anyone know the syntax to reference a table in a stored procedure using a parameter? I'm trying to make a stored procedure that will "SELECT INTO" a table as the passed parameter. I keep getting an syntax error when I use this:
CREATE PROCEDURE make_table
@tablename varchar(20)
AS
SELECT * FROM old_table
INTO @tablename
I've tried it several ways, but I can't get it to work, and I haven't found any examples of this anywhere. Thanks in advance for any tips.
View 1 Replies
View Related
Apr 30, 2004
Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working
Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b
Can anyone let me know.
TIA,
Ravi
View 2 Replies
View Related
Sep 16, 2006
I know there has already been a thread on this, but I want to push some about it.
In SQL Server, there is a command "SET IDENTITY_INSERT tablename ON".
That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.
In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornum and the year form the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.
The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.
Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)
Thanks.
View 4 Replies
View Related
Nov 15, 2007
I use IDENTITY(1,1)
and the help says:
SET IDENTITY_INSERT tablename ON
But if the table is an variable(virtual) table how to make it work?
Like this:
DECLARE @TEMPtable table ( ID int Identity(1,1), invar nvarchar(255)
It does not work with
SET IDENTITY_INSERT @TEMPtable ON
I get this
'Incorrect syntax near '@TEMPtable '
View 12 Replies
View Related
