|
CREATE PROCEDURE .InsertTwoTables
@UserName nvarchar(50),
@ShopName nvarchar(50),
@Email nvarchar(300) ,
@time1 datetime,
@CategoryNameE nvarchar(50),
@ItemKey nvarchar(50),
@ItemKeyNameE nvarchar(50),
@CurrentQty nvarchar(50),
@SalesPrice nvarchar(50),
@Quantity nvarchar(50),
@Total nvarchar(50)
AS
BEGIN TRANSACTION
Set Nocount On
DECLARE @OrderID int
//primary key table
INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1)
SELECT @OrderID=@@IDENTITY
//foreign key table
INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total)
VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total)
COMMIT
when iam inserting values with these procedure
it is saving ordersno also repeated
i want one orderid with multiple values in TblDetails
can you give me example which helps me
|
|
|
|
|
The way the stored procedure is currently there will only be a one to one relationship. I would suggest removing the time from TblOrder then before an insert check to see if the values UserName, ShopName, Email already exist.
Something like (not tested)
DECLARE @there INT
SELECT * FROM TblOrder
WHERE UserName = @username
AND ShopName = @shopname
AND Email = @email
SET @there = @@rowcount
IF @there = 0
BEGIN
-- Your insert
END
Hope this helps
djj
|
|
|
|
|
Try using SCOPE_IDENTITY rather than IDENTITY - check BOL for the (significant) differences.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi guys,
I'm trying to get 2 values from a stored procedure: one telling me the execution was successful and another for telling me what the newly inserted ROW ID is. I currently have the following:
<br />
<br />
USE [xyz]<br />
GO<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
ALTER PROCEDURE [dbo].[spName]<br />
(<br />
@vchName VarChar (50),<br />
@intSuccess Numeric(9) OUT<br />
)<br />
AS<br />
SET XACT_ABORT ON<br />
BEGIN TRANSACTION <br />
<br />
INSERT INTO Table<br />
(<br />
vchName <br />
)<br />
VALUES<br />
(<br />
@vchName<br />
)<br />
<br />
IF @@ERROR <> 0<br />
BEGIN<br />
SELECT @intSuccess = 0<br />
ROLLBACK<br />
END<br />
ELSE<br />
SELECT @intSuccess = 1 <br />
<br />
<br />
COMMIT TRANSACTION<br />
SET XACT_ABORT OFF<br />
<br />
RETURN @intSuccess<br />
<br />
Currently it returns the value "@intSuccess" ONLY. I want to add the line "SELECT @@IDENTITY AS inLatestID" right under the insert statement so I can get the latest ID value of the record that was just newly inserted.
I think I will need to return another value alongside @intSuccess, but I'm not sure how that works in the Stored Procedure
|
|
|
|
|
You can have multiple out parameters, but you do not need to return success/failure as an out parameter. You can use standard try/catch (sql 2005/8) or raiseerror for earlier versions to check for errors. Check out BOL for more details
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I suppose that makes sense. Normally I check if the return value is > 0, I suppose if a Stored Procedure executes successfully, then my parameter should be > 0 and I can just check that.
Thanks for your reply.
|
|
|
|
|
A stored proc (or any other sql query) returns zero for success and non-zero for an error
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm playing with a hypothetical company with several branch offices and one main office. Said company has limited IT resources (ie, no budget), but would like to keep local office sales data in SQL Server Express, then update nightly to the main office SQL Server Standard edition. Is this even possible? Can the Express edition participate in replication?
And yes, it's for a homework assignment I'm assisting with... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
we don't do homework assistance assistance. you want rentacoder.
|
|
|
|
|
So do we do looped assistance or is it like nested assistance and is below the assistance threshold.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Luc Pattyn wrote: you want rentacoder
Too expensive! 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
looks like Express does not support replication info[^]
Another aspect to consider is the records identity information, integer identity format no longer works. Look into uniqueidentifier/GUID. Horrible, ugly canankerious bastard things that they are. One gotcha is that a GUID is not a nullable type.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Maybe you could use integer keys locally, and apply an integer-to-GUID conversion table before exporting it all.
|
|
|
|
|
If you only need to update nightly, you should look into a simple application that creates xml files, and sends them by ftp or something like that, and an application on the other side that imports the data from these files.
It's a robust system that we use ourselves, and built without a budget 
My advice is free, and you may get what you paid for.
|
|
|
|
|
That's a good thought, and I may do it just for the learning experience. But I'm just looking for what's possible - only the design is required for homework, not the implementation.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I would maintain a transaction table. And send the unsent transactions periodically.
I would also include a field that indicates where the transaction came from.
|
|
|
|
|
That would make a lot of sense, as one of the requirements I made up is that the stores have to be autonomous during comm failures, then update the home server when a link is available.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Just a tip, you should include a status flag in that table while you are at it. This flag can have separate values for (for example) "Handled", "ImportError", "Corrupted", etc
The most common problems when transferring data are data corruption, transfer failures, and import failures (usually because some data is in the wrong format or missing entirely, say a date may have the year 1900 instead of 2010, which happens astoundingly often).
Based on the flag you can then have the import application take different actions (correct data with default values, alert someone, try to retrieve the data again from the source, etc.)
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi I have an Sql Table Like this
MatTable
type = 1 Is IN Qty ( Buy )
type = 0 Is Out Qty ( Sell )
mat qty type
-------------------------------------------------- ---------------------- -----
mat1 10 0
mat2 2 0
mat3 10 0
mat1 5 1
mat2 5 1
mat2 7 1
mat4 4 0
mat4 4 0
mat4 8 1
mat3 6 1
mat1 10 0
How I can Get a result Like
mat Current qty
-------------------------------------------------- ----------------------
mat1 15
mat2 -10
mat3 4
mat4 0
Thank you In Advance
I know nothing , I know nothing ...
|
|
|
|
|
Try:
"Select mat, sum(case type
when 0 then qty * -1
else qty) [Current qty] from MatTable
group by mat"
or if you don't like to use a case statement then -
select mat, sum(qty) from (
(Select mat, sum(qty * -1) qty from MatTable group by mat
where type = 0) t1
union
(Select mat, sum(qty) qty from MatTable group by mat
where type = 1) t2
)
group by mat
I didn't check either of these for syntax so let me know if you have problems running these...
|
|
|
|
|
Thank you so much ,
it;s worked like charming ,
However I added a little "END" word to the first statement ,
SELECT MAT, SUM(CASE TYPE WHEN 0 THEN Qty ELSE -Qty END) AS CurrentQty FROM MatTable GROUP BY Mat
thank you , for you code , it's so simple and brilliant
I know nothing , I know nothing ...
|
|
|
|
|
Hi,
I have a bit of a problem with a Forms App using Access for the DB.
I have a Calendar Control that has date ranges bolded from records in a table in the DB.
I have the Calendar Control populated and now the user can select a date and find the event associated to the date. The date may be the "StartDate", "EndDate", or a date within the range of the "StartDate" & "EndDate" in a row.
What I need help with is wrighting the SQL to bring back the record ID where the selected date falls between the StartDate & End Date and return the Record ID value.
SELECT E_ID FROM Events WHERE "SelectedDate" is Between StartDate AND EndDate
OR something like that???
|
|
|
|
|
Try
SELECT E_ID FROM Events
WHERE (SelectedDate >= StartDate) AND (SelectedDate <= EndDate)
Make sure you replace all the date variables with their string representations for the SQL, of course.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Hi Walt,
Thanks for the sample code... It works great. But I have another issue that maybe you can help me with.
The Value I am passing is a string with the DateTimeValue in it. I have put single quotes around the value and it works when I run the SQL manually... but when I run it in the app it rejectes it because the "Time" is still attached.
My issue is how can I convert the string value to a Date ONLY value?
I have tried Convert.ToDateTime(Value)... but that still has a Time in it.
I tried DateTime.ParseExact(Value,... ,...) but I couldn't find a Date only filter.
I am using this in a Forms App in C# on an Access DB, I build my SQL in the App and pass it over.
Got any suggestions on how to convert the string value of "DateTime" to a "Date" only value?
Thanks
|
|
|
|
|
Have your tried DateTime.ToShortDateString?
A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
