In Stored Proc - How Do I Find The Next Key Value (integer) And Use It To Populate A Field In The New Record.
Nov 27, 2006
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005.
What I want is that -
1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)
2). and set COMPANY_ID = (new) ID
Any thoughts?
Thanks
ALTER PROCEDURE usp_tbl_Company_Insert
@Company_ID int,
@Name varchar(200),
AS
<FIND THE NEW ID of the new row in the database>
@Company_ID = (new ID)
INSERT INTO tbl_Company (Company_ID, Name,)
VALUES (@Company_ID, @Name)
View 1 Replies
ADVERTISEMENT
Mar 16, 2008
Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?
For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.
TIA
Todd
View 3 Replies
View Related
Jul 5, 2007
I have this block of code that is supposed to go out to a SQL2k5 Express DB, get the number value from a record based on a request.querystring containing the table ID number.I get the request.querystring and store it in a variable. By nature this is stored as a String data type.Then I try to convert the string into an integer using both the "Convert.ToInt32(IDNum)" and the "Int32.TryParse(Request.QueryString("ID"), IDNum)" methods.The IDNum variable is to be used to inject into a stored proc as shown below. conn = New SqlConnection(ConfigurationManager.ConnectionStrings("database_connection").ConnectionString)conn.Open()cmd = New SqlCommand("GetMyNumber", conn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.Add("@MyNumber", Data.SqlDbType.Int).Value = IDNumSession("EndResult") = cmd.ExecuteScalarcmd.Dispose()conn.Dispose()As you can see the result is to be stored into a session variable to be used later.My query works just fine when created as an actual query and I supply the numerical value of the record. So I know that's working. I know it is connecting to the db based on the debugging I've done. I just, for some reason, cannot get that data type to convert. So my questions are:1. How do I convert the queried ID number to an integer so that I can use it in my stored proc?2. In the end, can the result be stored in the session variable as I have shown or are the session variables only for string types?
View 4 Replies
View Related
Apr 24, 2007
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?
View 4 Replies
View Related
Apr 25, 2007
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off. Anysuggestions?
View 2 Replies
View Related
May 9, 2008
I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.
One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.
Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.
Any help is appreciated.
View 1 Replies
View Related
Apr 2, 2015
I need to find all items in an inventory table where a field has been unticked in the last 24 hours (there is no audit trail), as well as the contract number of the contract it has been added to (it will not have existed in that table before).
These are the two table querys in their basic form:
select item (nvarchar(20)), inactive (bit) from inventory
select item (nvarchar(20) , contract (nvarchar(20)) , original_start_rent (datetime) from deltickitem
I would like to see just the item number and the contract number it has been added to.
View 11 Replies
View Related
Sep 19, 2015
The "Last" function in the query below (line 4 & 5) is not exactly what I'm after. The last function finds the last record in that table, but i need to find the most recent record in the table according to a date field.
Code:
SELECT
tblinmate.statusid,
tblinmate.activedate,
Last(tblclassificationhistory.classificationid) AS LastOfclassificationID,
Last(tblsquadhistory.squadid) AS LastOfsquadID,
tblperson.firstname,
tblperson.middlename,
tblperson.lastname,
[Code] ....
The query below finds the most recent record in a table according to a date field, my problem is i dont know how to integrate this Query into the above to replace the "Last" function
Code:
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,
[Code] .....
View 1 Replies
View Related
Sep 28, 2005
Hi,I am running SQLServer2000 with ASP.NET. I have 3 fields:DATE_TIME (date time 8)DATEF ()TIMEF ()...when my default.aspx page is called, i need to execute a stored procedure that will parse my DATE_TIME field by the space in between, then update my DATEF and TIMEF fields with the appropriatevalues. NOw i have 2 fields to query from instead of 1. How would i write a SQL expression to do this? and what datatypes should the DATEF and TIMEF fields be?thxj
View 3 Replies
View Related
Jan 29, 2004
Hello all,
Being a relative newbie to large scale MSSQL development, I'd like to try and find out if there is some sort of utility, command or stored proc that I can use to globally change all text within my database's stored procedures.
For example... I would like to change a table name from dbo.xtable... to dbo.ytable... Is this possible?
Actually, I'd be happy with some way to search through all my stored procs to find a specific string (i.e. xtable in the example above).
Any help provided will be greatly appreciated!
Jordan Stradtman
View 7 Replies
View Related
Jun 4, 2006
1/
I create stored procedure in Query Analyser using:
ALTER PROCEDURE dbo.unshippedtotal
@name char,
@ytd int output
AS
select * from vwaccount
select @ytd=sum(AccAccnnmb) from vwaccount
return
GO
But When I try to expand the list of stored procedures under the DB and the server name, I can t see my Stored Proc called "unshippedtotal"
Any ideas
2/ Another question pls:
Can we use Query Analyser to do the same tasks we do with Entreprise Manager like creating and modifying tables, Creating Stored Procedures, Modifying Views... (I m more familiar with Entreprise Manager but somebody told me it s better to use Query Analyser)
Thanks for coaching :)
View 9 Replies
View Related
Aug 24, 1999
I have 2 stored proc's that i want to run an append query every 4 or 8 hours.
how does a person do this in ms sql 7?
View 2 Replies
View Related
Feb 8, 2005
I have a mystery database on my hands...it is part of an application that my company bought, and it uses SQL server for its backend. The reporting features built in are not good enough, so I need to write some queries by hand...trouble is I am having a hard time figuring out how the schema works...using the front end they gave us I put a value of "12345" for a field I need to get to, but I can not locate where in the db it gets stored....can anyone tell me a way to query that will look at every single record and every single field in the db to find the value "12345"??
View 14 Replies
View Related
Jul 1, 2004
Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi
View 2 Replies
View Related
Oct 2, 2007
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
View 3 Replies
View Related
Mar 22, 2008
Hello,,,I need to update table but not all fields in stored proc have to be with value (some of fields will be NULL), so i need to avoid updating any field that his parameter with null value.I need the syntax for (IF) statement to optionally update that field.something like this :CREATE PROCEDURE [dbo].[MyUpdate]
(
@ID int,
@Field1 nvarchar(50),
@Field2 nvarchar(50)=null,
@Field3 nvarchar(50)=null,
@Field4 nvarchar(50)
)
AS
UPDATE MyTable
SET Field1 = @Field1,
if (@Field2<>null) Field2 = @Field2,
if (@Field2<>null) Field3 = @Field3,
Field4 = @Field4
WHERE ID = @ID
I saw example before but i can't remember where.
Thank you in advance
View 4 Replies
View Related
Jul 20, 2005
Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy
View 2 Replies
View Related
Jul 12, 2007
Hi
Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?
thanks
View 10 Replies
View Related
Jul 15, 2015
I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name],
qs.last_execution_time,
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
View 3 Replies
View Related
Sep 14, 2006
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
TIA,
Barkingdog
View 1 Replies
View Related
Jan 11, 2007
Hi All
I'm getting an error that says that this can't be converted to an integer.
Here is the line that gets the error. dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()
I have also tried. dt = (DataView)(EventDataSource1.Select(dssa);
I am programming in VB
here is teh rest of my code.
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()
View 4 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Oct 7, 2004
hi all,
i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.
View 2 Replies
View Related
Apr 25, 2006
I need to know who is entering data in my SQL database. I use Access Project as a front end and would like to know if it is possible to have an 'ID' field auto-populate with a user NT Login. For example, if user checks a check-box or enters data in field, I would like an ID field next to the checkbox or data field to populate with his user ID.
Any assistance is much appreciated!
Thanks.
View 5 Replies
View Related
Aug 20, 2007
I have 2 tables in a parent->child relationship. The primary key for the parent table is moving to a new column (that is an Identity). I'd like to relink the tables using the identity field.
Here's the structure:
Parent
refid int (Identity) - NEW PK
orderid char(9) - OLD PK
Child
orderdetailid int - PK
orderid char(9) - Old link to parent
refid int - New link to parent
I've populated refid in the parent. I just need to populate the refid column in the child. I have a VFP background. In VFP I would have looped through the child, found the matching record in the parent, then transfered the data over. I don' t have a clue how to do this in TSQL.
Brian
View 4 Replies
View Related
Aug 27, 2004
If I have a field where 99.9% of the time the answer is going to be "No", would I be better, in terms of disk space, using:
A bit field
OR
A tiny int field, with a NULL for the 99.9% that are "NO", and a 1 for those that are "YES".
I'm using SQL Server 7.0.
My application developer has no preference.
Thanks.
View 4 Replies
View Related
May 29, 2006
Hi everybody,
Could someone help me ???
Iam starting with sql
My problem ..
Ihave a table Table_customer
In this table a column Table_customer.no
This column contain 8000 rows.
with customer numbers: like 30789
I would like to modify once all this row adding just before the number
a value to all these rows.This value will be a 60
So will have instaed of 30789 a 6030789
This column is an integer.
I have try a simple select wich give me this result
How i can do this with update ?
probably i have to convert fisrt to caracters
select kunr,nov_kunr=
'30'+ltrim(str(kunr))
from event
where kunr is not null
Thank
View 3 Replies
View Related
Aug 9, 2006
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
View 1 Replies
View Related
Aug 10, 2006
I am fairly new to SQL programming and would like to see if you can provide me with some direction on a small project I would like to accomplish. I have experience working with databases (mainly in with different versions of FoxPro) and reporting with Crystal reports, etc. but not much with SQL.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and delete the assigned codes from the storage DB or mark them as used possibly the book code.
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL) and date assigned. The original idea was to put a Y in the used field and insert the date the code was assigned to a book, but this is not entirely necessary, we have 90k unused codes and it really does not matter if they are simply deleted from the DB.
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine (something like the list below) but what I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB
Go to the books DB and update it with this classcode
Go back to the classcode DB and mark the used record with a Y in the Used field
Loop though the books DB and perform the same operation until no more records exist with an empty classcode field.
I will greatly appreciate your feedback you can provide.
Mike.
UPDATE [dbo].[Books]
SET dbo.Books.Classcode = [dbo].[classcode].[classcode]
From dbo.classcode
On [dbo].[Books].[classcode]= [NULL]
Populate DB field from list in “storage� DB
Mike Campos - CA
View 2 Replies
View Related
Mar 15, 2008
record.
For example. I have a table with fields named "From" and "To" which track drill hole measurements. When I add a new record I would like it to autopopulate the "To" field with the "From" value from the previous record.
Example Recordset
Record 1, From=0, To=500, Lithcode=AB1
Record 2, From=500, To=1000, Lithcode=CR4
Record 3, From=1000<- trying to figure out how to auto populate previous record's from value here
TIA
Todd
View 6 Replies
View Related
Oct 31, 2006
Hi folks:
I have added a new field to an established table, and am having trouble figuring out how to populate its values:
Two tables are involved: Jobs and Parts
There is a one-to-one relationship between each JobID and its PartID
Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.
The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:
UPDATE Jobs
SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))
WHERE (JobID = [the job in question])
My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,
Matt
View 3 Replies
View Related
Feb 17, 2007
I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.Something like a CONTAINS keyword...If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.
View 1 Replies
View Related
Apr 1, 2008
How do i get the result 'puzzle' from the SQL and assign it to Integer and use it to compare with Request.Form ?
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim connString As String = _ ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString
Dim strsql As String = _
"Select CustomerID,CustomerNo,dbo.LA_DEC(Passkey) as passcode,dbo.LA_DEC(PuzzleKey) As puzzle, PuzzleFlag, NickName from Customers Where CustomerNo = '" & myAccount & "'"
Using myConnection As New SqlConnection(connString) Dim myCommand As New SqlCommand(strsql, myConnection)
myConnection.Open()
Dim PuzzleDataSet As New DataSet
Dim PuzzleAdapter As New SqlDataAdapter(myCommand) // <---- Coding here
myConnection.Close()
End Using
End Sub
View 7 Replies
View Related