Passing Values To Trigger

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


ADVERTISEMENT

Passing Parameter To Trigger

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

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 View Related

Passing IN() Values As Parameter

Feb 17, 2005

Hi...

I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.

eg: procedurename @fieldvalue varchar(100)
as
begin
...

WHERE fieldname IN(@fieldvalue)


while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...

Tx in Advance...

View 2 Replies View Related

Passing Values To Package

Apr 28, 2008

Well is there a way to pass the hardcoded values in a package externally
like for example if in a file system task i have specified the location for moving my files after processing, this will be a harcoded value how can i pass it externally so i have not to go into the package if in case i have modify it to some other location.

View 9 Replies View Related

Passing Values From Database To Variables.

Nov 12, 2007

Hello.
I need to store 2 values that i retrive from a databse into variables so i can check them towards what is typed into the textboxes for my login script iv built. How can i do this?1 string myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
2
3 SqlConnection myConnection = new SqlConnection(myConnectionString);
4 SqlCommand cmd = new SqlCommand("SELECT [username], [password] FROM [company] WHERE (([username] = @username) AND ([password] = @password))");
5 cmd.Parameters.AddWithValue("username", TextBoxUsername.Text);
6 cmd.Parameters.AddWithValue("password", TextBoxPassword.Text);
7
8 myConnection.Open();
9
10 //how do i pass the values retrived into variables, like string usrname and string password?
11
12 myConnection.Close();
 

View 13 Replies View Related

Passing Values To A Stored Procedure

Mar 21, 2008

I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :


create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)


Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?

View 4 Replies View Related

Passing Multiple Values Into A Subreport

May 7, 2008

I am doing report development against OLAP (Cube). I have a parameter which is a multi-value parameter, and I need to pass this as a parameter into a sub-report (pass all the values selected in this multi-value from the main report to sub report parameter).

Is this even possible? If yes how do I achieve this.

Currenty I sent like Fields!Region.Value(0), but I want to send actually Fields!Region.Value() (meaning all selected).

Any help ASAP is appreciated.

View 3 Replies View Related

Passing Temp Table Values Into A UDF

Jul 20, 2005

This one is interesting...Is there any way to pass a joined parameter into a UDF as I'm attempting below?I have a temp table that I'm trying to create:create table #t3(bmkPerson int primary key,LangCode nchar(5),SName varchar(1000))insert into #t3select t2.bmkPerson, t2.LangCode,select SName from SName_trans_udf(t2.LangCode)from #t2 t2Thanks in advance,RenaeJoin Bytes!

View 1 Replies View Related

Passing Multiple Values To A Paramter

Apr 20, 2007

Hi,



I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

View 1 Replies View Related

Transact SQL :: Passing Several Values In One Parameter

Nov 12, 2015

In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast  and convert values and that does not work. The query that I am trying to use is the following:

DECLARE  @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)

Thus can you show me the t-sql 2012 that I can use to accomplish my goal?

View 12 Replies View Related

Passing Null Values To Stored Procedures

Aug 29, 2006

Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!

View 4 Replies View Related

Passing In Query Paramater Values Through WebUserControls

Jun 7, 2007

Hi, I don't want to pass in parameter values in the url to get a certain record from my database (for a better search engine result) so instead i'm using webuser controls (this is a must) with Public Values that should be read by the SqlDataSource. So in the page that contains the usercontrol there is something like:UserControl.Value = "myvalue"Now I want the SqlDataSource in the usercontrol to read this value and pass it into a query (in the parameter of the WHERE statement, eg.: @recordID = myvalue).Thanks in advance! 

View 1 Replies View Related

Passing Multiple Values To A Single Parameters

Dec 28, 2007

I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?

View 4 Replies View Related

Trouble With Passing Values To Stored Procedure

Feb 13, 2008

Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event:  Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC"
'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"




Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection
Dim conn As SqlConnection = New SqlConnection(connString) ' Create command

Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"

Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value
Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer

groupID = return_value.Value

If groupID < 0 Then
MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If

conn.Close()



End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here
@UserName nvarchar(50) = N'', @Password nvarchar(50) = N''
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists'
SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist'
RETURN (-1)ENDEND
 All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom  

View 16 Replies View Related

Getting Error While Passing Values As Special Chars

Mar 4, 2008

Hi friends, I m facing an error while i m passing the values in query as special characters like (',"). anyone can help me?code will be more preferable.Thank you. 

View 1 Replies View Related

Passing Values From A Gridview To A Stored Procedure

May 7, 2008

Hello. im tryinng to build an application that has a girdview with values from 2 different tables. The select query i have used in a stored procedure works great but when i try to write something to update into 2 different tables I cant figure out how to do it.
This is the first time im writing stored procedures but from what i can tell i need to do 2 seperate updates to insert mu values.
im using the AdventureWorksLT database provided by microsoft to experiment with and my gridview consists of 2 tables populated by the following stored procedure:ALTER PROCEDURE dbo.GetSomething AS
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, CustomerAddress.AddressID, CustomerAddress.AddressType FROM SalesLT.Customer, SalesLT.CustomerAddress
WHERE Customer.CustomerID = CustomerAddress.CustomerID

RETURN
What id like to do is to make the GridView editable and then send the all thee values back so the changes are saved in both tables. Could anyone help me write a stored procedure that gets the values from the fields in the gridview that is beeing changed and send them back to the tables?

View 4 Replies View Related

Passing Values -- Int32.Parse(Expr2)

Sep 16, 2004

I have written a function as below to pass some value and get aNOTHER VALUE DURING THE RUNTIME
-----------------------------------------------
Sub ExpiaryDate(ByVal Expr1 As String, ByVal Expr2 As String)

Dim strSQLDDL As String = "SELECT STATMENT "

Dim SQlCommand As New SQlCommand(strSQLDDL, mySqlConnection)
SQlCommand.CommandType = CommandType.Text

Dim Colleague_CN As String = Int32.Parse(Expr1)
Dim Course_ID As String = Int32.Parse(Expr2)

Dim parameterEventType_ID As SqlParameter = _
New SqlParameter("@Colleague_CN", SqlDbType.NVarChar, 50)
parameterEventType_ID.Value = Colleague_CN
SQlCommand.Parameters.Add(parameterEventType_ID)

Dim parameterCourse_ID As SqlParameter = _
New SqlParameter("@Course_ID", SqlDbType.NVarChar, 50)
parameterCourse_ID.Value = Course_ID
SQlCommand.Parameters.Add(parameterCourse_ID)

Dim myDataAdapter As SqlDataAdapter = New _
SqlDataAdapter(SQlCommand)
myDataAdapter.Fill(DivisionDataSet, "ExpiaryDate")
myDataAdapter.Update(DivisionDataSet, "ExpiaryDate")
DivisionDataSet.AcceptChanges()


End Sub

Everything was fine as far as i am passing an integer.
Now i need to pass a combination of numbers and letters( NVarChar)

HOW DO I GETAROUND THIS PROBLEM
WHEN I USE THIS FUNCTION I GET ERRORS
SOMETHING LIKE..... Input string was not in a correct format.
INDICATES --Dim Colleague_CN As String = Int32.Parse(Expr1.ToString)
AS AN ERROR

CAN ANYBODY PLEASE HELP
HOW DO I PASS A NVarChar instead
THANKS

View 4 Replies View Related

Passing Values In User Defined Functions

Apr 25, 2007

i want to pass a value from one user defined function to another how do i do it.E.g

My first function calulate a value which is to be used by another function for calculation
my 2nd function is given below

Create Function Avg_WLPD_CFS(@Res float,@TotDay int)
Returns float
As
Begin
Return(@Res/@TotDay)
end

the value @Res is calculated from first function, how do i pass this value to the above function.

View 1 Replies View Related

Passing Parms With No Values To A Stored Procedure

Feb 28, 2008

I'm want to pass empty parm value to a stored procedure. I'm new at this stuff and not sure how to do this. To make it work I can put values in parms and it works great. I don't want to populate some of the fields in the table with values. The fields are cnty,dist,beat,convertedkey. Anyone know how I can get it done? Code below. Thanks for your help!

int shift = Convert.ToInt16(dr.Cells[1].Value.ToString());

int prior = Convert.ToInt16(dr.Cells[2].Value.ToString());

string lname = Convert.ToString(dr.Cells[3].Value.ToString());

string fname = Convert.ToString(dr.Cells[4].Value.ToString());

string unit = Convert.ToString(dr.Cells[5].Value.ToString());

string handle = Convert.ToString(dr.Cells.Value.ToString());

int badge = Convert.ToInt16(dr.Cells[7].Value.ToString());

string cnty = Convert.ToString('1');

string dist = Convert.ToString('1');

int beat = Convert.ToInt16(1);

int convertedkey = Convert.ToInt16(1);

char action = 'I';

char assign = 'Y';

var db = new dpoffassignedDataContext();

var q = db.SP_DpAssignAllOfficers(action, convertedkey, shift, prior, lname, fname, unit, handle, cnty, dist, beat, badge, assign);





Stored procedure below;

ALTER PROCEDURE SP_DpAssignAllOfficers

(

@action nvarchar(1),

@int_id int,

@shift int,

@prior int,

@lname nvarchar(20),

@fname nvarchar(20),

@unit nvarchar(5),

@handle nvarchar(10),

@cnty nvarchar(10),

@dist nvarchar(10),

@beat int,

@badge int,

@assign char



)

AS

INSERT

INTO dpofficers(dpo_prior,dpo_lname,dpo_fname,dpo_unit,dpo_handle,dpo_cnty,dpo_dist,dpo_beat,dpo_badge,dpo_shift)

VALUES (@prior,@lname,@fname,@unit,@handle,@cnty,@dist,@beat,@badge,@shift)

View 6 Replies View Related

Passing A Column Of Values To The Stored Procedure

Apr 18, 2006

how can i send a column of values to the stored procedure for filtering that stored procedure values...?

View 1 Replies View Related

Passing List Values In Drill Through! URGENT

Aug 13, 2007



I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that)

and then there are tables inside this list. the report will be displayed based on the values of this custom list.

i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected..

what shud i do to select the whole grp??

thanks!

View 4 Replies View Related

Passing Multiple Values From A Listbox Into A Stored Procedure

Dec 9, 2007

hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)
{
try
{foreach (ListItem item in authorsListBox4.Items)
{if (item.Selected)
{
AddConfSqlDataSource.Insert();
}
}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";
}catch (Exception ex)
{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;
}
}

View 3 Replies View Related

Reporting Services :: Passing Values To SSRS Bookmark?

Jun 5, 2015

I am using 2 charts in my SSRS Report. Requirement is to load the Right side chart based on the click event from left chart.

I can achieve it using go to report option. But the issue is total page getting refreshed while using this option. I feel bookmark option will be the best. But I don't think I can pass a value to Bookmarks.

View 2 Replies View Related

Passing Compound Primary Key Values To A Stored Procedure Using ADO.NET

Apr 10, 2008

Hi all,
I've had this problem for a while now and I'm looking for a better solution.


I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.


I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.


Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?


Thanks for your help.

View 7 Replies View Related

Passing Multiple Values From Parent To Child Package

Dec 20, 2006

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

View 2 Replies View Related

Passing Values For Variables To An SSIS Package From C#/ ASP.NET Page.

May 30, 2007

I've got an SSIS package that works fine. It does extracts from a foreign ODBC source and moves it to SQL Server. It has 3 variables, a customer ID, a fromDate and a toDate. Those variables are used to complete a SQL statement expression. So far so good.



I now want to provide a web interface in a web page that will enable the user to provide values for those variables. How can I execute my package passing the user's input?



Thanks for any advice.



Lou

View 3 Replies View Related

Passing Subreport Matrix Values To The Main Report

Jul 24, 2007

Hello everybody,

I'm trying to pass the values of a subreport matrix to my main report.
For example, I have a matrix subreport like this:

USA CANADA FRANCE GERMANY
Client1Sales $100 $200
Client2Sales $600 $50 $300

What I would like to do is to pass the USA Sales Amount of Client1 (e.g $100) to my main report.
Is there any way to do this?

Thanks in advance for your answers!
Zoz

View 2 Replies View Related

Passing Variable Values Into A Nested SQL In An OLEDB Datasource

Aug 21, 2007

All:
I am trying to code the following SQL into an OLEDB data source but it is not allowing me to do so because I think the variables are nested in multiple SQL statements. I have seen other posts that suggest using a variable to store the SQL but I am not sure how it will work.

I would also like to mention that the OLEDB source executes from within a For Each loop that is actually passing the values for the variables, which was one of the reasons I got stumped on how I could have a variable store the SQL.

Here is the SQL:


select b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor,sum(a.Ordered) As Qty_Pruchased
From SXE..POLine a INNER JOIN
(SELECT VIR_Program.ProgramID, VIR_ActiveSKU.ProductCode, VIR_ActiveSKU.BuyerID, Vendor
FROM VIR_Program INNER JOIN
VIR_ActiveSKU ON VIR_Program.ProgramID = VIR_ActiveSKU.ProgramID
INNER JOIN Vendor ON VIR_Program.VendorID = Vendor.VendorID
WHERE ProgramFreq=?) b
ON a.ProductCode = b.ProductCode
WHERE a.TransDate >=? AND
a.TransDate ?
Group By b.ProgramID, b.ProductCode, b.BuyerID, b.Vendor

Thanks!

View 5 Replies View Related

Passing Multiple String Values Separted By A Comma As One Parameter

Oct 16, 2007



Hello,



I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.



Thanks a lot for any feedback!

View 6 Replies View Related

Passing Parameter Values To Full Text Search Stored Procedure

Oct 24, 2006

I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle

(@ProductTitle varchar(255))

AS

SELECT ProductTitle

FROM tbl_Product


WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle)
   My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R  

View 4 Replies View Related

SQL Server 2014 :: Passing Multiple Columns Values From Query To One Variable?

Aug 10, 2014

Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)

[code].....

View 1 Replies View Related

Transact SQL :: Passing Multiple String Param Values To Stored Proc

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







Copyrights 2005-15 www.BigResource.com, All rights reserved