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

How To Automatically Populate A Field With A Value From A Previous Record?

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

Stored Proc Integer

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

Populate Table With Stored Proc

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

Populate A Table With Stored Proc.

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

Why Is There A Parameter That Returns An Integer In My Stored Proc?

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

Find Record Where 1 Field Value Has Changed

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

Find Most Recent Record In Table According To Date Field

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

Parsing A Field Into Another Field Using Stored Proc

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

Global Stored Proc Find And Replace

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

I Can't Find My Stored Proc In Query Analyser

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

I Need To Find Out Info On How To Schedule A Stored Proc To Run In Sql 7 Every Few Hours?

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

Need To Find Where In Db Record Is Stored

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

Find Stored In Server Vide Proc For Given Table And Column

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

How To Return A Numeric Value Based Upon If A Record Is Returned From My Query/Stored Proc.

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

How To Optionally Update Field In Stored Proc?

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

STORED PROC To UPDATE Ntext Field -- PLEASE!

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

SELECT A Field From Another Database (on Same Server) From Stored Proc

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

DB Engine :: Can Find A Record Of Stored Procedure Being Called?

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

Converting An Integer Field Into An Identity Field

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

Can't Convert Record To Integer

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

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

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

Help With Integer Field

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

Populate Field With NT Login

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

Populate Field From Another Table

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

Bit Field Vs. Integer With Nulls

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

Update On A Integer Field

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

Populate Field Dependant Upon 2 Tables

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

Populate DB Field From List In “storage� DB

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

Auto Populate A Field With A Value From Previous..

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

Need Help With Update Query To Populate New Field

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

Contains Keyword For Integer Field Type

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

Setting Field To Integer From SqlDataAdapter

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







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