Help With Insert Stored Procedure

Nov 17, 2007

I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below

ALTER PROCEDURE [dbo].[AddNewFundAllocation]

@Payment_ID Int,
@Receipt_ID Int,
@Fund_ID Int,
@Amount_allocated money,
@DateEntered datetime,
@EnteredBy nvarchar(50)

AS

SELECT     (SUM(tblReceiptsFunds.Amount_allocated) + @Amount_allocated) AS total_allocations, Sum(tblReceipts.AmountPaid) as total_payments
FROM         tblReceiptsFunds INNER JOIN
                      tblReceipts ON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_ID
WHERE tblReceipts.Payment_ID=@Payment_ID

IF (total_allocations<total_payments)
INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)

Values (@Receipt_ID,@Fund_ID,@Amount_allocated,@DateEntered,@EnteredBy)

ELSE BEGIN
PRINT 'You are attempting to allocate more to funds than your total payment.'
END I get the following error when I try and save the stored procedure:

Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26

Invalid column name 'total_allocations'.

Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26

Invalid column name 'total_payments'.

View 6 Replies


ADVERTISEMENT

Stored Procedure Insert

Nov 7, 2006

Hi,
I am having trouble inserting 2 fields in a row using a stored procedure.
This works fine:
Exec ('Insert Into NumbersPull (Number)'+ @SQL)
but when I try to insert another value into field 2 it errors out:
I try this:
Exec ('Insert Into NumbersPull
(Number,resultID) Select ('+ @SQL
+ '),' + @resultID'
)
and get this error:
ERROR: Line 2: Incorrect syntax near ')'.
Thanks,
Doug

View 3 Replies View Related

INSERT INTO With Stored Procedure

Feb 27, 2007

Having problem do INSERT values to my SQL DB with an StoredProcedure. SELECT works fine.
My StoredProcedure :
CREATE PROCEDURE insert_test
@id  int ,@Rubrik char(25), @Info  char(60) , @Datum datetime
ASINSERT INTO test_news(ID, Rubrik, Info, Datum) VALUES (@id, @Rubrik, @Info, @Datum)GO
The StoredProcedure  works fine in the SQL Query Analyzer
 
My Code;
int num= 1234; string rub = "KLÖKÖLKÖLKÖL"; string ino = "slökdjfkasdkfjsdakf";SqlConnection myConnection = new SqlConnection("server='SOLDANER\DAER'; trusted_connection=true; database='SeWe'");
SqlCommand myCommand = new SqlCommand("insrt_test", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add( "@id",num);myCommand.Parameters.Add( "@Rubrik",rub );myCommand.Parameters.Add( "@Info", ino);myCommand.Parameters.AddWithValue( "@Datum", DateTime.Now );
 Even tried AddWithValues
Dont get any error.....

View 3 Replies View Related

Insert Stored Procedure Help

Oct 31, 2007

OK I have a stored procedure that inserts information into a database table.  Here is what I have so far:
I think I have the proper syntax for inserting everything, but I am having problems with two colums.  I have Active column which has the bit data type and a Notify column which is also a bit datatype.  If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns.  I tried using the set command, but it will give me a xyntax error  implicating the "=" in the Active = 1 How can I set these in the stored procedure?1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:xxxxxxxx
7 -- Create date: 10/31/07
8 -- Description:Insert information into Registration table
9 -- =============================================
10 ALTER PROCEDURE [dbo].[InsertRegistration]
11
12 @Name nvarchar(50),
13 @StreetAddress nchar(20),
14 @City nchar(10),
15 @State nchar(10),
16 @ZipCode tinyint,
17 @PhoneNumber nchar(20),
18 @DateOfBirth smalldatetime,
19 @EmailAddress nchar(20),
20 @Gender nchar(10),
21 @Notify bit
22
23 AS
24 BEGIN
25 -- SET NOCOUNT ON added to prevent extra result sets from
26 -- interfering with SELECT statements.
27 SET NOCOUNT ON;
28
29 INSERT INTO Registration
30
31 (Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify)
32
33 VALUES
34
35 (@Name, @StreetAddress, @City, @State, @ZipCode, @PhoneNumber, @DateOfBirth, @EmailAddress, @Gender, @Notify)
36
37 --SET
38 --Active = 1
39
40 END
41 GO
 

View 8 Replies View Related

Using Insert Stored Procedure With C#

Dec 5, 2007

I'm trying to insert the details in the "registration form" using stored procedure to my table. My stored procedure is correct, but I dunno what is wrong in my code or I dunno whether I've written correct code. My code is below. Please let me know what is wrong in my code or my code is itself wrong..... protected void RegisterSubmitButton_Click(object sender, EventArgs e)    {               SqlConnection conn = new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds;Integrated Security=SSPI");        SqlCommand cmd;        cmd = new SqlCommand("registeruser", conn);        SqlParameter par = null;        par= cmd.Parameters.Add("@fname", SqlDbType.VarChar, 30);        par.Value = RegisterFirstNameTextBox;        par = cmd.Parameters.Add("@lname", SqlDbType.VarChar, 30);        par.Value = RegisterLastNameTextBox;        par = cmd.Parameters.Add("@uname", SqlDbType.VarChar, 30);        par.Value = RegisterUserNameTextBox;        par = cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 20);        par.Value = RegisterPasswordTextBox;        par = cmd.Parameters.Add("@email", SqlDbType.VarChar, 40);        par.Value = RegisterEmailAddressTextBox;        par = cmd.Parameters.Add("@secque", SqlDbType.VarChar, 50);        par.Value = RegisterSecurityQuestionDropDownList;        par = cmd.Parameters.Add("@secans", SqlDbType.VarChar, 40);        par.Value = RegisterSecurityAnswerTextBox;        try        {            conn.Open();            cmd.ExecuteNonQuery();        }        catch (Exception ex)        {            throw new Exception("Execption adding account. " + ex.Message);        }        finally        {            conn.Close();    } }

View 2 Replies View Related

Two Insert In One Stored Procedure

Jan 28, 2008

Hi,
I have two tables "people' and "dept". What I need is a stored procedure to insert into both tables.
I need first, insert into "people" table, and then, insert into "dept" table since the first insert returns people id(peo_id), which
is an input parameter for "dept" table.
Here is my stored procedure, but I got error:Create PROCEDURE [dbo].[insert_people]
@peo_last_name varchar(35),
@peo_mid_initial varchar(1) = null,@peo_first_name varchar(10),
@peo_address1 varchar(50) = null,
@peo_city varchar(30) = null,
@peo_state varchar(2) = null,
@peo_zip varchar(10) = null,
@peo_ph1 varchar(30) = null,
@peo_ph2 varchar(30) = null,
@peo_email varchar(40) = null,
@dept_id int, @peo_id int
 
AS
SET @peo_id = (INSERT INTO people (peo_last_name, peo_mid_initial, peo_first_name, peo_address1, peo_city, peo_state, peo_zip, peo_ph1, peo_ph2, peo_email)
VALUES (@peo_last_name, @peo_mid_initial, @peo_first_name, @peo_address1, @peo_city, @peo_state, @peo_zip, @peo_ph1, @peo_ph2, @peo_email))
INSERT INTO dept (dept_id, peo_id)
VALUES (@dept_id, @peo_id)
GO
Could somebody help out?
Thanks a lot!

View 3 Replies View Related

Insert Using Stored Procedure

Apr 9, 2008

I have a table with UserID, UserName, UserPassword
I have a stored procedure as follows:ALTER PROCEDURE UserInsert
@UserName varchar(50),
@UserPassword varchar(50)
AS
BEGIN
INSERT Users (UserName, UserPassword)
VALUES (@UserName, @UserPassword)
END
I have a GridView bound to the Users Table and seperate textboxes (UserName, UserPassword) on a webform. 
Couple of Questions...
1. how and/or what code do I use to execute the Stored Procedure to insert what is in the textboxes into the Table using a button click event?
2. Since UserID is autogenerated on new records....does UserID need to be in the code?
Many Thanks

View 1 Replies View Related

Using Insert Into ... Stored Procedure...

Feb 21, 2006

Hi,
I need to insert a new user if the user (user_login) does not exist in the table (abcd_user) using a stored procedure.
I created a stored procedure called "insert_into_abcd_user". Here is the complete strored procedure...
CREATE PROCEDURE [dbo].[insert_into_abcd_user] (     @first_name  [VARCHAR](30),      @last_name  [VARCHAR](30),      @email  [VARCHAR](60),      @user_login [VARCHAR](50))  AS INSERT INTO [dbo].[abcd_USER] ([first_name],  [last_name],  ,  [user_login])VALUES (@first_name,  @last_name,  @email,  @user_login)
I need to to insert a new user if the user (user_login) does not exist int the table (abcd_User).
Any one shade on my code?
I appreciate your help in advance.
 
-- Srinivas Gupta.

View 2 Replies View Related

INSERT INTO ... From A Stored Procedure

Sep 17, 2004

For example:

INSERT INTO Table_1
SELECT
Source_Table.Field_1,
Source_Table.Field_2,
Source_Table.Field_3,
???? OUTPUT parameter returnet from a stored procedure ???????
FROM Source_Table

Is posible this ?

View 2 Replies View Related

INSERT INTO.. A Stored Procedure!

Aug 30, 2006

I have created a stored procedure which simply inserts two records into a table. Here is my stored procedure:-
//BEGIN
ALTER PROCEDURE [dbo].[pendingcol]
@cuser varchar(100)
AS
Declare @sqls nvarchar(1000)

SELECT @sqls = 'INSERT INTO' + @cuser + '(UserName, Pending) VALUES ("recordone", "recordtwo")'

EXECUTE sp_executesql @sqls

RETURN
//END

This is the code i am using to call my stored procedure using VB.NET:-
//BEGIN
'variables
Dim user As String
user = Profile.UserName

'connection settings
Dim cs As String
cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)

'parameters
Dim cmd As New SqlCommand("pendingcol", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuser", SqlDbType.VarChar, 1000)
cmd.Parameters("@cuser").Value = user

'execute
scn.Open()

cmd.ExecuteNonQuery()

scn.Close()
//END

Now when i execute this code i get an error point to cmd.ExecuteNonQuery() that says
" The name "recordone" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. "

As far as i can see theres nothing wrong with the VB code, im guessing that the problem lies somewhere in my stored proc!

Can anyone please enlighten me on where i may be going wrong?
Cheers

View 10 Replies View Related

Can I Use A Stored Procedure For INSERT In SqlDataSource?

Jun 20, 2006

Hello,
I have created a web page with a FormView that allows me to add and edit data in my database.  I configured the SqlDataSource control and it populated the SELECT, INSERT, UPDATE and DELETE statements.  I've created a stored procedure to do the INSERT and return to new identity value.  My question is this: Can I configure the control to use this stored procedure?  If so, how?  Or do I have to write some code for one of the event handlers (inserting, updating???)
Any help would be appreciated.
-brian

View 1 Replies View Related

Select + Insert Into + Stored Procedure. Please Help Me.

Oct 14, 2006

So I have simply procedure: Hmmm ... tak sobie myÅ›lÄ™ i ...

Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w
ten sposob co napisalem kilka postow wyzej (select ID as UID from
aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest
wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic    @user_id int,    @topic_katId int,    @topic_title nvarchar(256),    @post_content nvarchar(max)as    insert into [forum_topics] (topic_title, topic_userId,topic_katId)    values (@topic_title, @user_id, @topic_katId)       insert into [forum_posts] (topic_id, user_id,post_content)    values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id".  Can anyone help me ? I tried lots of ways but nothing works.

View 6 Replies View Related

Insert Or Update With Stored Procedure

Dec 27, 2006

I'm doing this more as a learning exercise than anything else.  I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key.  If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.for starters can this even be done with a stored procedure?if so, can someone provide some guidance as to how?thanks in advance,Burr

View 5 Replies View Related

Can I INSERT INTO A Temp Tbl Twice In One Stored Procedure

Mar 15, 2007

In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl?
Any help here is appreciated.

View 4 Replies View Related

Need Help In Creating Stored Procedure Insert

Apr 2, 2007

Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert
2
3 @companyid int,
4 @companyname varchar(20),
5 @address1 varchar(30)
6
7 AS
8
9 INSERT INTO companymaster
10 ( companyname, address1)
11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which
was not supplied.

The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....    

View 5 Replies View Related

How To Invoke Insert Stored Procedure

Apr 3, 2007

Feeling really dumb tonight - below is my stored procedure and code behind which completes (it puts "completed" in TextBox4) but does not insert anything into database.
Questions:1) do in need to include the primary key field in the insert stored procedure?2) do I need a DataAdapter to actually get it running and open and close the connection? STORED PROCEDURE running on SQL 2000 server: ______________________________________
CREATE PROCEDURE newuser003.InsertCompanyInfo
@CS_CompanyName nchar(100),@CS_City nchar(500),@CS_Phone varchar(20)
AS
INSERT into tblCompanyInfo_Submit(CS_CompanyName, CS_City, CS_Phone)VALUES ('@CS_CompanyName', '@CS_City', '@CS_Phone')RETURN
C# CODE BEHIND: ______________________________________________________ 
public partial class ContractorSubmision : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {       }    protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("CompanyInfoSubmit", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.AddWithValue("@CS_CompanyName", TextBox1.Text);        cmd.Parameters.AddWithValue("@CS_City", TextBox2.Text);        cmd.Parameters.AddWithValue("@CS_Phone", TextBox3.Text);        TextBox4.Text = "Completed";    }}
 

View 5 Replies View Related

Insert Does Not Take Place If Stored Procedure Is Run From ASP.NET

May 8, 2007

Hello,
I am having an issue with the insert command in a stored procedure in SQL Server 2005. The procedure is designed to generate a Customer number and insert an initial value (a delivery charge) into an order table. Finally, it returns the customer number back to the caller. When I execute the stored procedure from SQL Server Management Studio, it works perfectly. However, when I call it from ASP.net, the procedure properly generates a Customer Number and returns it to my application, but the insert never takes place. Can someone help me figure out why it won't insert?
My ASP.Net (C#) code is here: SqlDataSource dsSelect = new SqlDataSource();
dsSelect.ConnectionString = ConfigurationManager.ConnectionStrings["obeConn"].ConnectionString;
dsSelect.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
dsSelect.SelectCommand = "getTempCustNo";

DataView reader = (DataView)dsSelect.Select(DataSourceSelectArguments.Empty);

Session["CustNo"] = reader[0]["CustNumber"].ToString().Trim();
Session["PriceCode"] = reader[0]["PriceCode"].ToString().Trim();
Session["orderNo"] = reader[0]["OrderNo"].ToString().Trim();  
  My stored procedure is here:ALTER PROCEDURE [dbo].[getTempCustNo]
AS
BEGIN
declare @CustNo int;
declare @prodDesc VARCHAR(max);
declare @prodNo int;
declare @stdPrice float;
declare @orderNo int;
declare @priceCode int;

-- populate values
SELECT @custNo = ISNULL(MAX(OD_CUSTNO)+1, 100000000), @prodNo = 999, @orderNo = 1, @priceCode = 1000
FROM [dbo].[tblOrderDet]
WHERE OD_CUSTNO >= 100000000;

-- get the price of our product
SELECT @stdPrice = PR_RATE_1
FROM tblPrices
WHERE PR_PRODNO = @ProdNo
AND PR_RCDCOD = @priceCode;

-- get the description
SELECT @ProdDesc = PM_PRDESC + ' ' + PM_VOLUME FROM dbo.tblPromas WHERE PM_PRODNO = @ProdNo;

--Add a delivery charge to the customer's order table
INSERT INTO tblOrderDet (OD_ORDRNO, OD_CUSTNO, OD_PRODNO, OD_QUANTITY, OD_ORG_QUANTY, OD_PRICE, OD_DESCR, OD_LOW)
VALUES (@orderNo, @CustNo, @prodNo, 1, 0, @stdPrice, @prodDesc, 'N');

--Return the customer values
SELECT @custNo AS 'CustNumber', @orderNo AS 'OrderNo', @priceCode AS 'PriceCode';
END 

View 3 Replies View Related

Execute Insert Stored Procedure

Jan 22, 2008

Hi,
 I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a  temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
 
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
 
please can someone point me in the right direction

View 13 Replies View Related

Using A Stored Procedure To Insert A New Record

Apr 7, 2004

ok I have a stored procedure......
I pass in the variables that are requried....What is the best way to add a record
using my stored procedure in VB.net code in a button click event......
How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn




CREATE procedure dbo.Appt_AddAppt
(
@ClinicID int,
@AccountNum nvarchar(10),
@DOS nvarchar(12),
@POSCODE nvarchar(5)
)
as
Insert into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID)
Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)



GO

View 4 Replies View Related

Stored Procedure To Insert Record

Jul 27, 2004

I have an SP to add a record to the database but i get the error shown below. Any help appreciated.

Stored Procedure:

CREATE PROCEDURE addUser
@username char(15),
@password char(12)

AS
INSERT INTO users(username, password) VALUES (@username, @password)

GO


Code calling SP:

Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)

Dim cmd As New SqlCommand("addUser", myConnection)
MyDataAdapter = New SqlDataAdapter()
'MyDataAdapter = New SqlDataAdapter("addUser", myConnection)

With cmd 'MyDataAdapter
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@username", SqlDbType.Char).Value = username
.Parameters.Add("@password", SqlDbType.Char).Value = password
End With

Try
myConnection.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Finally
myConnection.Close()
End Try


I get an error message on the line: cmd.ExecuteNonQuery()

System.FormatException: Input string was not in a correct format.

View 2 Replies View Related

Update/Insert Stored Procedure

Nov 16, 2004

I am trying to take some SQL queries written by Visual Studio, one insert and one update and combine them into a single stored procedure. The insert procedure should be included in the update procedure and a check should be done for an existing record based upon the primary key. If it exist, an update command should be performed, else an insert. I also need to wrap the procedure in a transaction and rollback if any errors have occurred, else commit the transaction. If I have the following Insert and Update statements, can anyone help me write the stored procedure I need? Again, the current statements were automatically created and could be modified as needed.

INSERT INTO tblClub(ClubPKID, ClubName) VALUES (@ClubPKID, @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @@IDENTITY)

UPDATE tblClub SET ClubPKID = @ClubPKID, ClubName = @ClubName WHERE (ClubPKID = @Original_ClubPKID) AND (ClubName = @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @ClubPKID)

Thanks!

View 2 Replies View Related

Stored Procedure - Insert Not Working

Jun 23, 2005

Having a little trouble not seeing why this insert is not happening.... --snip--  DECLARE c_studId CURSOR FOR  SELECT studentId  FROM students FOR READ ONLY   OPEN c_studId  FETCH NEXT FROM c_studId INTO @studentId  IF( @@FETCH_STATUS = 0 )  BEGIN   SET @studRec = 'Found'  END CLOSE c_studId DEALLOCATE c_studId
 BEGIN TRAN IF (@studRec <> 'Found')  BEGIN  INSERT INTO students  (studentId)  VALUES  (@studentId)    END  Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath

View 6 Replies View Related

Stored Procedure To Insert Data

Nov 30, 2000

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

Stored Procedure To Run Query And Then Insert

Oct 11, 2006

I have a query that select rows from the employees,salary_head and salary_group tables
this is the query
SELECT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
FROM dbo.employees INNER JOIN
dbo.salary_head INNER JOIN
dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
i also have a table called payrollers1 with the following fields
payroll_id int auto
payperiod_id int
employee_id
level_id
designation_id
banks_id
bankaccount_no
salarygroup_id
Amount
I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table.
As for the payperiod_id i want the Stored procedure to look up the max payperiod value.
I am totally new to stored procedure and do not know how to write this code.
Can somebody help me with this code.

View 2 Replies View Related

Stored Procedure Insert Containing A SubSelect

Feb 27, 2006

Im not sure the best way to go about doing this

i want to pass 2 values to the procedure

one passed value will be a direct insert value

the other passed value will be used within a subselect
and the result from the subselct will be the second inserted value.



Code:



Create procedure InsertSubSelect
(
@Value1 nvarchar(50),
@Value2 nvarchar(50)
)
AS

INSERT INTO Mytable (field1, field2)
VALUES (@Value1,
(select Value2
from Mytable2
where Value2 = @Value2 ) )

Go

View 5 Replies View Related

Insert With A If Statement. But Yet Not Using A Stored Procedure. Is That Possible?

Mar 12, 2004

my table:

FoodID Integer PRIMARY KEY,
FoodName varchar(255),
FoodDesc text

How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?

Thanks in advance to the people that replied to my request! Thanks so much...

View 4 Replies View Related

Stored Procedure To Insert Records Into Next Row

Sep 20, 2014

Creating a stored procedure to insert records into multiple rows.

Let's say we have 3 colums in procedure to insert, col3 has a max limit of 1000, if col3 data exceeds limit it has insert into next row and vice versa.

View 1 Replies View Related

Insert/update In One Stored Procedure

Jul 30, 2007

Hi:

I am trying to write a stored procedure that when passed a name, value it will

1. update the record if the name exists
2. create a new record if it doesnt

how would i do this?

View 5 Replies View Related

Insert Query With Stored Procedure

Jul 20, 2005

Hi,is it possible to create an "INSERT INTO ..... "Select from storedprocedure" Query?I want to create an temporary table. In this table I want to enter the data,which I can get from an stored procedure.But in the FROM-clause a stored procedure is not allowed?

View 1 Replies View Related

Stored Procedure - INSERT/UPDATE

Aug 27, 2007

HI,

I€™m trying to do a Stored Procedure (SP) that stores data from a form in ASP.NET.
If the record does not exist, it will create a new record first.

This is my first SP, so if best practise is done another way, I would like to hear about it.

Code:




Code Snippet
CREATE PROCEDURE dbo.StorUserRecord

/* some of these are for future use */
@UpdateMetode Int,
@UserIndex Int,
@UserFirstName NVarChar,
@UserLastName NVarChar,
@UserPassword NVarChar,
@UserPrivateMail NVarChar,
@UserCompanyMail NVarChar,
@UserTitle Int,
@UserSecLevel NVarChar,
@UserAddressLine1 NVarChar,
@UserAddressLine2 NVarChar,
@UserZipCode Int,
@UserPrivateMobilNumber NVarChar,
@UserCompanyMobilNumber NVarChar,
@UserHomeNumber NVarChar,
@UserCompanyDirectNumber NVarChar,
@UserCPR NVarChar,
@UserWorkerID NVarChar,
@UserDepartment Int,
@UserActive Bit,
@UserAccess Bit,
@UserDeleted Bit
AS

BEGIN


DECLARE @UseThisUserIndex Int;
IF NOT @UserIndex > 0

/* Create new record and return the "auto id" from UserIndex */
BEGIN

INSERT INTO [User] (UserFirstName) VALUES (@UserFirstName)

OUTPUT INSERTED.UserIndex

INTO @UseThisUserIndex
GO
END
ELSE

/* Use the "auto id" from the @UserIndex */
BEGIN

@UseThisUserIndex = @UserIndex
END

END
RETURN





I'm getting this error:


Msg 102, Level 15, State 1, Procedure StorUserRecord, Line 33

Incorrect syntax near 'OUTPUT'.

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'ELSE'.

View 6 Replies View Related

Stored Procedure BULK INSERT

Jan 26, 2006

Firstly, I am new to sql so please excuse my ignorance!

I have a stored procedure that tests the import of about 100 text files. This is to screen for file errors prior to processing. The results for each file are held in a table. The procedure script is as follows:

<snip>

insert into st_fileimport (dealernumber, handheld_file)
values ('01', 'Fail')


BULK INSERT dbo.filetesthandheld
FROM 'c:inetpubwwwrootS_Datadealers1foo.csv'
WITH
(
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '' ,
ROWS_PER_BATCH = 500
)


update st_fileimport
set handheld_file = 'Pass' where dealernumber = '01'


</snip>

This is then repeated for each file. This works fine, but as soon as an error is encountered the script terminates which means that you need to re-run this procedure iteratively until all files pass.

Is there a way of structuring the stored procedure so it will fail on one import and then move onto the next? Or, a better methodology all together?

Any help or advise would be greatly appreciated.

Karen

View 1 Replies View Related

Insert Image Using Clr Stored Procedure

Sep 1, 2006

I tried to insert a row using a clr stored procedure where a field was a varbinary(max) and the data was a jpg file. The data was truncated to 8000 bytes. A similar T-SQL sp did not truncate the data even though the parameters were identical. With the clr sp I tried varbinary, variant and image for the parameter type. The variant gave an exception. The others worked but the data was truncated.

I used sqlpipe.executeandsend. Someone asked elsewhere if the pipe had an 8000 byte limit but was told it had not.

Any ideas?

Thanks, John

View 5 Replies View Related

Stored Procedure And Insert Data...

Feb 22, 2008

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







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