How To Get The ID Of An Inserted Data In A Stored Procedure
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure.
for example:
alter procedure [dbo].[AddDetails]
(
@IndustryName nvarchar(50),
@CompanyName nvarchar(50),
@PlantName nvarchar(50),
@Address nvarchar(100),
@Createdby int,
@CreatedOn datetime
)
as
begin
insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn)
insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn)
insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp)
end
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this.
Please help me, i need the solution soon.
View Complete Forum Thread with Replies
Related Forum Messages:
Help Getting An ID, Back From A Record, That Has Just Been Inserted With A Stored Procedure
Hi, I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID This is my stored procedure:ALTER PROCEDURE dbo.quote (@Business_Name nvarchar(50), @Business_Type nvarchar(50),@Contact_Title nchar(10), @Contact_First_Name nvarchar(50),@Contact_Last_Name nvarchar(50), @Address_Line_1 nvarchar(MAX),@Address_Line_2 nvarchar(MAX), @City nvarchar(MAX),@Postcode nchar(7), @Tel_No nchar(11),@E_mail_Address nvarchar(50), @Distributor_ID int,@Profile_Class int, @Meter_Time_Code int,@Line_Loss_Factor int, @Unique_Identifier1 int,@Unique_Identifier2 int, @Check_Digit int,@Tariff nchar(20), @UnitRate1AnnualUsage nchar(10),@UnitRate2AnnualUsage nchar(10), @UnitRate1SubTotal money,@UnitRate2SubTotal money, @QuoteTotal money ) ASINSERT INTO client_details (Business_Name, Business_Type, Contact_Title, Contact_First_Name, Contact_Last_Name, Address_Line_1, Address_Line_2, City, Postcode, Tel_No, email_Address)VALUES (@Business_Name, @Business_Type,@Contact_Title, @Contact_First_Name, @Contact_Last_Name, @Address_Line_1, @Address_Line_2, @City, @Postcode, @Tel_No, @E_mail_Address) DECLARE @Client_ID INTSET @Client_ID = scope_identity() INSERT INTO meter_quote (Client_ID, Tariff, Meter_Distributor_ID, Meter_Profile_Class, Meter_Time_Code, Meter_Line_Loss_Factor, Unique_Identifier1, Unique_Identifier2, Check_Digit, UnitRate1AnnualUsage, UnitRate2AnnualUsage, UnitRate1SubTotal, UnitRate2SubTotal, QuoteTotal)VALUES (@Client_ID, @Tariff, @Distributor_ID, @Profile_Class, @Meter_Time_Code, @Line_Loss_Factor, @Unique_Identifier1, @Unique_Identifier2, @Check_Digit, @UnitRate1AnnualUsage, @UnitRate2AnnualUsage, @UnitRate1SubTotal, @UnitRate2SubTotal, @QuoteTotal) RETURN And this is the code I have in my asp page:<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="quote" InsertCommandType="StoredProcedure"> <InsertParameters><asp:ControlParameter ControlID="TextBoxBusinessName" DefaultValue="" Name="Business_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="DropDownBusinessType" Name="Business_Type" PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="DropDownListTitle" Name="Contact_Title" PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="TextBoxFirstName" Name="Contact_First_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLastName" Name="Contact_Last_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine1" Name="Address_Line_1" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine2" Name="Address_Line_2" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine3" Name="City" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxPostcode" Name="Postcode" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxTelNo" Name="Tel_No" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxEmail" Name="E_mail_Address" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxDistributorID" Name="Distributor_ID" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxProfileClass" Name="Profile_Class" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxMeterTimeCode" Name="Meter_Time_Code" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLineLossFactor" Name="Line_Loss_Factor" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxUniqueIdentifier1" Name="Unique_Identifier1" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUniqueIdentifier2" Name="Unique_Identifier2" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxCheckDigit" Name="Check_Digit" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="LabelTariff2" Name="Tariff" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUnitRate1Usage" Name="UnitRate1AnnualUsage" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate2Usage" Name="UnitRate2AnnualUsage" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate1Total" Name="UnitRate1SubTotal" PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelUnitRate2Total" Name="UnitRate2SubTotal" PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelQuoteTotal" Name="QuoteTotal" PropertyName="Text" Type="Decimal" /> </InsertParameters> </asp:SqlDataSource> And the following in the C# code: try { SqlDataSource3.Insert();//Insert quote details into the database using a stored procedure }catch (Exception ex) {LabelInsertException.Text = "Failed" + ex.Message; } Any help would be much appreciated Thanks, Hayley
View Replies !
Duplicate Record Inserted With Stored Procedure
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String 'Get the users IP address strIPAddress = Request.UserHostAddress Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub
View Replies !
Problem With Stored Procedure And Retrieving Inserted Identity Value
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
View Replies !
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source? Something like this: CREATE PROCEDURE TestHardDisk AS BEGIN DECLARE CURSOR HardDisk_Cursor FOR Exec xp_FixedDrives -- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source OPEN CURSOR HardDisk_Cursor FETCH NEXT FROM HardDisk_Cursor INTO @Drive, @Space WHILE @@FETCH_STATUS = 0 BEGIN ... END END
View Replies !
Getting Data From A Storeed Procedure In A Stored Procedure
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
View Replies !
Monitoring Inserted Data And Comparing Against Selected Data
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View Replies !
How WCF Can Get New Inserted SQL Data ?
dear all, I have a WCF service which is host in a console application for the time beeing. This service provide methods for retriving history data when request. So far so good. My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table. In other words my WCF servcie would send a callback event to my client when it as been notice for a change.. But my problem is how my WCF service can be notify from an update in my SQL table ? I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on. Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed.... What to do, how to do, what is the best way and thred safe method Thnaks for help and advise regards serge
View Replies !
Getting Inserted Data In Trigger
I am using SQL Server 2000.I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help. Thanks Jag
View Replies !
INSERT INTO - Data Is Not Inserted
hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View Replies !
Access Inserted Data
i have a oledb destination in my data flow pointing to table ABC and an error output if the insert failed..follow the error output, i have a lookup on table ABC which doesn't seem to work..is it possible to access new data in table ABC follow the error output? thanks
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
Stored Procedure For Getting Data Into Dropdownlist
Hi iam working with two dropdownlists,one gets the data dynamically when the pageload.later after selecting particular item in the dropdownlist1 i must get data to the dropdownlist2 depending on 1. For example: Dropdownlist1 is for industry and 2 is for company. when i select particual industry in ddl1 i must get companies based on this industry in ddl2.Both the Industry name and company name are maintained in two different tables industry and company with common field ID.please help me with a stored procedure to sort out this problem...
View Replies !
Data Repeating In Stored Procedure
Can someone tell me why my stored procedure is repeating the Name in the same column? Here's my stored procedure and output: select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name], Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate, Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate from LibraryRequest join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null Output: ID Title Name Request Date Ship Date 29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008 Going crazy ugh...
View Replies !
Getting Text Data Into XML Stored Procedure
Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a local variable because they can't be of type text. I can't pass it into the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt
View Replies !
Retrieving Data Using A Stored Procedure
I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks! 1 ALTER PROCEDURE [dbo].[GetCustomerById] 2 3 @CustId NCHAR(5), 4 @CustomerName NVARCHAR(50) OUTPUT 5 6 AS 7 BEGIN 8 9 SELECT @CustomerName = ContactName 10 FROM Customers 11 WHERE CustomerId = @CustId 12 13 END 14 15 RETURN 16 17 18 19 20 21 22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection 23 SqlCommand cmd = new SqlCommand(); 24 cmd.Connection = conn; 25 cmd.CommandType = CommandType.StoredProcedure; 26 cmd.CommandText = "GetCustomerById"; 27 28 SqlParameter paramCustId = new SqlParameter(); 29 paramCustId.ParameterName = "@CustId"; 30 paramCustId.SqlDbType = SqlDbType.NChar; 31 paramCustId.Direction = ParameterDirection.Input; 32 paramCustId.Value = "ALFKI"; 33 34 SqlParameter paramCustomerName = new SqlParameter(); 35 paramCustomerName.ParameterName = "@CustomerName"; 36 paramCustomerName.SqlDbType = SqlDbType.NVarChar; 37 paramCustomerName.Direction = ParameterDirection.Output; 38 39 cmd.Parameters.Add(paramReturn); 40 cmd.Parameters.Add(paramCustId); 41 cmd.Parameters.Add(paramCustomerName); 42 43 conn.Open(); 44 SqlDataReader reader = cmd.ExecuteReader(); 45 46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
View Replies !
Export XML Data - Stored Procedure
I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better... e.g Sample XML file required...<Person><Name>Raymond</Name><NickName>The Legend</NickName></Person><Person><Name>Peter</Name><NickName>The King</NickName></Person> sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Web Assistant Procedures', 1;GORECONFIGUREGOsp_makewebtask @outputfile = 'C:MyExportFile.xml', @query = 'SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS', @templatefile = 'C:Template.tpl'
View Replies !
Retrieve Data From A Stored Procedure
Hello Group I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code. CREATE PROCEDURE stpMyAuthentication ( @fldUsername varchar( 50 ), @fldPassword Char( 25 )--, --@fldFirstName char( 30 ) OUTPUT, --@fldLastName char( 30 ) OUTPUT ) As DECLARE @actualPassword Char( 25 ) SELECT @actualPassword = fldPassword FROM tbMembUsers Where fldUsername = @fldUsername IF @actualPassword IS NOT NULL IF @fldPassword = @actualPassword RETURN 1 ELSE RETURN -2 ELSE RETURN -1 SELECT fldFirstName, fldLastName, fldPassword FROM tbMembUsers Where fldUsername = @fldUsername GO ############### login page ################ Sub Login_Click(ByVal s As Object, ByVal e As EventArgs) If IsValid Then If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False) End If End If End Sub Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer Dim myConn As SqlConnection Dim myCmd As SqlCommand Dim myReturn As SqlParameter Dim intResult As Integer Dim sqlConn As String Dim strFirstName, strLastName As String sqlConn = ConfigurationSettings.AppSettings("sqlConnStr") myConn = New SqlConnection(sqlConn) myCmd = New SqlCommand("stpMyAuthentication", myConn) myCmd.CommandType = CommandType.StoredProcedure myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int) myReturn.Direction = ParameterDirection.ReturnValue myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername)) myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword)) myCmd.Parameters.Add("@fldFirstName", strFirstName) myCmd.Parameters.Add("@fldLastName", strLastName) myCmd.Parameters.Add("@fldPassword", strPassword) myConn.Open() myCmd.ExecuteNonQuery() intResult = myCmd.Parameters("RETURN_VALUE").Value myConn.Close() 'If strPassword = 55555555 Then ' Session("intDefaultPass") = 1 'End If Session("strFullName") = strFirstName & " " & strLastName Session("strPassword") = strPassword If intResult < 0 Then If intResult = -1 Then lblMessage.Text = "Username Not Registered!<br><br>" Else lblMessage.Text = "Invalid Password!<br><br>" End If End If Return intResult End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after? Michael
View Replies !
How Do I Use Pass Data To/from Stored Procedure
Hello,I read an article on how to use Yahoos API to GeoCode addresses. Basedon the article I created a stored procedure that is used as follows:SPGeocode '2121 15st north' ,'arlington' ,'va' ,'warehouse-test'Returns:Latitude Longitude GeoCodedCity GeoCodedState GeoCodedCountryPrecision Warning----------- ---------- ------------- ------------- ------------------------------ --------38.889538 -77.08461 ARLINGTON VA USPrecision Good No ErrorIt returns Latitude and Longitude and other information. Works great.In conjunction with Haversine formula, I can compute the distancebetween two locations if I know the Lat and Long of the two points.This can start to answer questions like "How many students do we havewithin a 10 mile radius of Location X?"(Marketing should go nuts over this :)My question is how can i use my data from a table and pass it to theSPGeocode via a select statement?The table I would use is:CREATE TABLE "dbo"."D_BI_Student"("STUDENT_ADDRESS1" VARCHAR(50) NULL,"STUDENT_ADDRESS2" VARCHAR(50) NULL,"STUDENT_CITY" VARCHAR(50) NULL,"STUDENT_STATE" VARCHAR(10) NULL,"STUDENT_ZIP" VARCHAR(10) NULL);This is so new to me, I am not even sure what to search.TIARob
View Replies !
Outputting Data From Stored Procedure
Hi AllIm generally a vb programmer and am used to referencing multiple recordsreturned from a query performed on an sql database and im trying to movesome functions of my software into sql stored procedures. So far ive beenable to move the functions relatively easily but im unsure about how tooutput multiple values from an sql stored procedure. By this i mean forexample one of the stored procedures may take your username and return thecontents of a single field in a record of one of the tables, but i wouldlike to be able to return for arguement sake the contents of a single fieldfrom two records if possible. Under VB im used to referencing the recordsetwith a (1) after it to reference the corresponding record from the query. Iwas wondering if there is a way to do something similar to this with storedprocedures if possible ?Thanks for any help
View Replies !
Stored Procedure To Insert Data
I have a table that has data and is indexed by the Visit #. Each month I receive new data from a text file that I have to import into this table. On all occasions the text file will have one of the existing Visit #'s contained in it. I wrote a simple stored procedure that has the INSERT/SELECT statement but the problem I am having is when I execute the Stored Procedure and I run into a record from the text file that already exist in my table the stored procedure errors and stops at that point. How do I write a stored procedure that will ignore that text record and continue reading the text file and insert records that do not exist?
View Replies !
Processing Data From Stored Procedure
I want to capture and process data inside of a stored procedure by executing another stored procedure. If proc1 calls proc2 and proc2 returns 1 or more rows, consisting of 2 or more columns, what is the best way to do this? Currently, I know I'm returning 1 row of 3 columns, so I concatenate the data and either return it with an OUTPUT parameter or using the RETURN stmt. TIA, mike
View Replies !
Retrieving Data From Stored Procedure
Hi, For a particular application i retrieve records using stored procedure and bind the same to the data grid and display the same. The data are fetched from a table say A joined with couple of tables to staisfy the requirement. Now this table A has around 3700000 + records. The Select statement is as Select column1, column 2, column 3...................... column 25 from table A inner join hash table D on A.Column3 = D.Column3 and A.Column4 = D.Column4 and a.nColumn1 = @ParameterVariable Inner join table B on a.nColumn1 =b.nColumn1 inner join table C on A.column2 = C.column2 Indices defined on A are IDX1 : Column 3, Column 4 IDX2 : Column 1 Around 350 records per second are inserted into the table A during the peak operation time. During this time when executing the procedure, it takes around 4 mins to fetch just 25000 records. I need to fine tune the procedure. Please suggest me on same. Thanks
View Replies !
Stored Procedure And Insert Data...
I have this stored procedure.. but it doesn't insert data... Why??? SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.DETTAGLIO_TURNI_DIFENSORI AS INSERT INTO Albo_Turno_Dettaglio ( idalboturno, idalbo, idturno, data ) VALUES ( '885261', -- ID chiave '15', -- da cursore '778', '2008-04-01 00:00:00.000' -- problemi inserimento data?? ) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
View Replies !
Stored Procedure Not Retieving Data
I have a stored prodecure that runs on my laptop (XP SP2) but does not run out on a production server (2003). The stored procedure takes 2 parameters. When I simply run the query I get a result set, but when I execute the stored procedure with the same parameters the data is not grabbed (no error). There are other stored procedures in this database that run fine, it's just this one that is giving me a problem. Any suggestions? Thx.
View Replies !
Encrypt Data In A Stored Procedure
I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table. Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data) My stored procedure is as follows. Please let me know what i am doing wrong! *************************************************************** ALTER PROCEDURE [dbo].[SP_InsertInfo] -- Add the parameters for the stored procedure here @FIRST_NAME varBINARY(100) ,@LAST_NAME varBINARY(100) AS OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert BEGIN SET NOCOUNT ON; -- Insert statements for procedure here Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME) Values ( encryptbykey( key_guid('key'),'@FIRST_NAME'), encryptbykey( key_guid('key'),'@LAST_NAME') ) close SYMMETRIC KEY key END ********************************************** EXEC sp_InsertInfo 'larry', 'Smith' when I run the SP, the data stored in the first_name, last_name fields are @FIRST_NAME', @LAST_NAME' instead of larry, smith respectively. Thanks
View Replies !
Getting Text Data Into A Stored Procedure
Hi, I've got some XML which exists as a text variable in a temp table in SQL Server 2000. I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax. If I try doing this: declare @idoc int exec sp_xml_preparedocument @idoc output, (select XmlResult from #cache) I get an error, with or without the brackets round the select statement. The temp table is created using an SP, but I can't call that directly either. This: declare @idoc int exec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchID Also throws an error. I can't put it into a local variable because they can't be of type text. I can't pass it into the SP somewhere as it's being generated on the fly. How can I get my xml into sp_xml_preparedocument? Cheers, Matt
View Replies !
Stored Procedure Returns Different Data When You Run It In T-SQL
I have a stored procedure Code Block CREATE PROCEDURE WEA_SelectEmployeeListByCourseOrProject @ProjectID int, @CourseID int, @blnIsSearch int, @strUserName nvarchar(20) AS SET CONCAT_NULL_YIELDS_NULL OFF DECLARE @strRole nvarchar(15), @ContactID int SELECT @strRole = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName Select DISTINCT Contact.ContactID ID, UPPER(Surname + 'gd ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR , UPPER(ISNULL(ContactReference,'')) ContactReference FROM Contact LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID WHERE RUEmployee=1 AND ( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID)) AND (@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID) AND ( (@strRole = 'MIS_TUTOR' AND (AssignedEmployee.ContactID = @ContactID OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1) OR AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID)) ) OR (@strRole <> 'MIS_TUTOR') ) SET CONCAT_NULL_YIELDS_NULL ON GO now if i run this stored procedure in Query Analyzer like so... exec wea_SelectEmployeeListByCourseOrProject 0,0,1,'K_T' i get 48 records returned. but if i lift the SQL out of the stored procedure and run it in Query Analyzer like so.... Code Block SET CONCAT_NULL_YIELDS_NULL OFF DECLARE @ProjectID int, @CourseID int, @blnIsSearch int, @strUserName nvarchar(20) SET @ProjectID = 0 SET @CourseID = 0 SET @blnIsSearch = 1 SET @strUserName = 'K_T' DECLARE @Role nvarchar(15), @ContactID int SELECT @Role = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName PRINT @ContactID Select DISTINCT Contact.ContactID ID, UPPER(Surname + ' ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR , UPPER(ISNULL(ContactReference,'')) ContactReference FROM Contact LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID WHERE RUEmployee=1 AND ( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID)) -- the above line was modified to make sure only employees explicitly assigned to a project are brought back. unless it's a search AND (@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID) AND ( (@Role = 'MIS_TUTOR' AND ( (AssignedEmployee.ContactID = @ContactID OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1))) OR AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID) ) OR (@Role <> 'MIS_TUTOR') ) SET CONCAT_NULL_YIELDS_NULL ON i only get 5 records returned??? so why do i get a difference when its the same SQL?? Username 'K_T' is of role 'MIS_TUTOR' therefore @Role = 'MIS_TUTOR' any help on unravelling this mystery is appreciated! Cheers, Craig
View Replies !
Storing Out Put Of Data From Stored Procedure In A
Hi i m new in sqlserver databases i need to know how to "store output of data from stored procedure in a text file " suppose i have a stored procedure which has to cuculate some out put from some tables and in the end i want that all out put in comma delimited text file. my databse name is check1 i need help please thanks in advance take care bye
View Replies !
Converting Data To Be Inserted Into A Database
Hi,I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"dim test as integer test = Convert.ToInt32(txtAmountSold.text)Here is the whole of the function I am using:Function AddItemToStock() As Integer dim test as integer test = Convert.ToInt32(txtAmountSold.text) Dim connectionString As String = "server='(local)Matrix'; trusted_connection=true; database='HawkinsComputers'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _ "], [Description], [image], [OnOffer], [OfferPr"& _ "ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _ ", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _ ".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _ "tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End FunctionAny help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.
View Replies !
Check Inserted Data In A SQL Database
Hi im having problems as im new to ASP.NET C# i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes can anyone help....... this is what i have to insert into the database........i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name Thanks private void Button1_Click(object sender, System.EventArgs e) { string connectionString = "server='(local)'; trusted_connection=true; database='tester'"; //System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); conn.Open(); string commandString = "INSERT INTO Users (UserName, Password) " + "Values(@UserName, @Password)"; //SqlCommand dbCommand = new SqlCommand (commandString, dbconn); System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); //System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(queryString, conn); dbCommand.CommandText = commandString; dbCommand.Connection = conn; SqlParameter unParam = new SqlParameter ("@UserName", SqlDbType.NVarChar, 60); unParam.Value = txtUser.Text; dbCommand.Parameters.Add(unParam); SqlParameter paParam = new SqlParameter ("@Password", SqlDbType.NVarChar, 60); paParam.Value = txtPassword.Text; dbCommand.Parameters.Add(paParam); dbCommand.ExecuteNonQuery(); conn.Close(); Response.Redirect ("WebForm1.aspx"); }
View Replies !
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table. Any help would be greatly appreciated. Current Table Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours --------------------------------------------------------------------------------------------------------- Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40 Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20 Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35 Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40 Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40 Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40 Proposed Table Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year --------------------------------------------------------------------------------------------------------------------------------------------------- Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007 Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007 Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008 Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008 Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008 Thanks, Mike Misera
View Replies !
Stored Procedure Retrieving Duplicate Data
Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance Pmillio Jones Pmillio Jones Pmillio Jones Robert Walsh Here is my stored procedure; ALTER PROCEDURE [dbo].[stream_UserFind] -- Add the parameters for the stored procedure here @userName varchar(100), @subCategoryID INT,@regionID INT AS SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName, Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON Users.userID= UserSubCategories.userIDINNER JOIN SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);
View Replies !
Linking Tables In Data Set Or Stored Procedure
Im currently in the process of developing a new system in ASP.net. The system uses multiple tables from a SQL database. To link information from say the products table to the suppliers table a unique key from the suppliers table is stored in the products table to show which products each supplier has bought...simple so far. From this I can then pull out any of the information relating to that product instead of just displaying the ID. Currently this is done by creating a stored procedure and then dragging the stored procedure onto the dataset layer to create the data adapter. Im not 100% this is the best way to be doing this. Is it possible to simply just link the tables in the dataset layer to display a string from another table instead of an ID referencing number?! any help on this would be much appreciated as I cant really continue untill I have sorted out the data structure problems.Thanks in advancedmowfo
View Replies !
Getting Error When Retrieving Data From Stored Procedure
Hi, I'm at my wit's end with this. I have a simple stored procedure: PROCEDURE [dbo].[PayWeb_getUserProfile] ( @user_id_str varchar(36), @f_name varchar(50) OUTPUT)AS BEGINDECLARE @user_id uniqueidentifier; SELECT @user_id = CONVERT(uniqueidentifier, @user_id_str); select @f_name=f_name FROM dbo.tbl_user_profile WHERE user_id = @user_id; ENDRETURN The proc runs perfectly from the Management Console. In my ASP.net form (C#), the following always results in: "Invalid attempt to read when no data is present." (I'll highlight the line where the error results): SqlConnection conn = new SqlConnection(<connection string data>);SqlDataReader reader; SqlCommand comm = new SqlCommand("dbo.PayWeb_getUserProfile", conn);comm.CommandType = System.Data.CommandType.StoredProcedure; SqlParameter f_name = new SqlParameter("@f_name", SqlDbType.VarChar, 50); f_name.Direction = ParameterDirection.Output;comm.Parameters.Add(f_name); comm.Parameters.Add("@user_id_str", SqlDbType.VarChar, 36).Value = Membership.GetUser().ProviderUserKey.ToString().ToUpper();reader = comm.ExecuteReader();Response.Write("Num: " + reader["@f_name"].ToString()); // ERROR: Invalid attempt to read when no data is present. reader.Close();conn.Close(); The data is there. When I put a watch on 'Reader', I see the correct first_name data there. Your help is appreciated.
View Replies !
Saving Datetime Data In A Stored Procedure
Hello everyone I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime)); cmdPublish.Parameters["@datPublishDate"].Value = ???? Thanks
View Replies !
Stored Procedure For Verifying Data Existence
Hello All, I'm trying to develop a stored procedure that would do one of TWO things: 1. Return a 'status' that a value does not exist, if I were to provide the parameter via an ASP.NET2.0 page 2. If it does exists, to return the row data associated with that value (id number) The stored procedure would search a SQL Server table within it self first. It that fails it would look at an Oracle table (work order table). And if that fails to return a 'row' to look through another Oracle table (work request table). If that doesn't occur, then it would throw the result as described in #2. If the result exists in one of the TWO Oracle tables it would then insert that row into the first SQL Server table that the stored procedure searched through AND would return the row set to the ASP.NET page. While all this is happening, I was hoping to get some insight as to how to create a "Please Wait..." feedback and then moving to the final result. Looking forward to the wise words of the many on this forum, as I have experienced in the past! :)
View Replies !
Problem With Stored Procedure - Getting Data For The Last 5 Week
Hi, I have this stored procedure that gives me the result by week for the last 5 week.SELECT DateDiff(week,Datesold,GetDate()),SUM(Nb)FROM TSalesWHERE DateDiff(week,Datesold,GetDate())>=0 AND DateDiff(week,Datesold,GetDate())<=5 and project=136GROUP BY DateDiff(week,Datesold,GetDate())The only problem is if there is no data for one week, I am going to have only 4 result.I would like to 5 results all the time even if the SUM(nb)=0.I tried coalesce but with no success.Thank you
View Replies !
Creating A Stored Procedure To Insert Data?
Hello all, I am having a lot of trouble with stored procedures. Could anyone help me out. I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today. I can select the values, and I can insert the values. But how do I store the values so that i can pass the results of the select to the insert? Im also having a lot of trouble with storing date values. ANy help would be greatly appreciated. Regards, Padraic Hickey
View Replies !
|
TRACKER
