July 12, 2006 at 6:12 am
Hi
I have to insert multiple rows in a database with the following fields
1) ServiceTadId of the type varchar
2) UserId int
3) Duration int
4) Start Date DateTime
5) End Date DateTime
6)Comments char
I require a stored procedure that inserts multiple rows into a database basing on the ServiceTagId i.e., ServiceTagId is the primary key. ServiceTagId is a column name in the table.
I will be thankful to any help provided.
Regards
Mahathi.
July 12, 2006 at 6:17 am
I am not sure of your requirements, please supply sample input and desired output.
Far away is close at hand in the images of elsewhere.
Anon.
July 13, 2006 at 3:26 am
Sample input is in the form:
6XXC81S.
This is the ServiceTag Id of a computer. Basing on this I require an output of the form
Service_Tag_Id User_Id start_time End_Time Comments
6XXC81S 20 4/4/2006 4/5/2006 It is a sample system
1T2671S 58 4/4/2006 5/5/2006 Reserved by me
July 13, 2006 at 7:02 am
I really don't understand what is the proble. You said you needed to put the data in a database. Do you mean you need to put these data in a table? Otherwise you just create a table in this format and do a insert.
July 14, 2006 at 2:21 am
I agree with the others - your question doesn't seem to be expressed very clearly.
You stated that you need a stored proc to accept a parameter and based on this parameter you want to insert some database into the database - we assume this means stick the data into a table.
But where would you get this other data from? Perhaps you mean to return the data from a table that matches the given parameter - this seems to make more sense and certainly fits in better with your second post where you included two result lines; altough one of these didn't even match your input...
Could you please elaborate further? Perhaps spell out in very simple terms exactly what you want the user & software systems interaction to be.
July 14, 2006 at 2:49 am
My actual requirement is there is an application which is used to reserve computers in the lab. The user selectes the required computers and then clicks a button called "Reserve".When the user clicks on this button all the computers that are selected by the user are added into a table called "Transactions". The required output is as given in the above table.
Sample input is in the form:
6XXC81S,1T2671S
This is the ServiceTag Id of a computer. Basing on this I require an output of the form
Service_Tag_Id User_Id start_time End_Time Comments
6XXC81S 20 4/4/2006 4/5/2006 It is a sample system
1T2671S 58 4/4/2006 5/5/2006 Reserved by me
Getting other values is done through the application. So I require a stored procedure that takes as input an array of Service_Tag_Ids and then inserts them into the table.
July 14, 2006 at 6:31 am
There are samples of a split function on this site, so you can do this
CREATE PROCEDURE MyProc
@ServiceTadId varchar(8000),
@userid int,
@Duration int,
@StartDate DateTime,
@EndDate DateTime,
@Comments char
AS
INSERT INTO [Transactions]
(ServiceTadId,UserId,Duration,StartDate,EndDate,Comments)
SELECT a.[splitvalue],
@Duration,
@StartDate,
@EndDate,
@Comments
FROM dbo.udf_split(@ServiceTadId) a
However my preferred method would be to get the app to insert the records one at a time using the following proc
CREATE PROCEDURE MyProc
@ServiceTadId varchar,
@userid int,
@Duration int,
@StartDate DateTime,
@EndDate DateTime,
@Comments char
AS
INSERT INTO [Transactions]
(ServiceTadId,UserId,Duration,StartDate,EndDate,Comments)
VALUES (@ServiceTadId,@UserId,@Duration,@StartDate,@EndDate,@Comments)
and having done this both ways, I now use the second method.
Far away is close at hand in the images of elsewhere.
Anon.
July 14, 2006 at 8:11 am
Thank you for the help provided. But when I execute the stored procedure I getting the following error:
"Error converting data type varchar to datetime"
Please tell me where may be the problem. Thanks in advance.
July 14, 2006 at 8:19 am
One of the dates input to the proc contains invalid characters or is in the wrong format, ie dd/mm/yy when sql is expecting mm/dd/yyyy or vice versa. Check the format of the date input with the language properties of the login.
To avoid issue like this use the iso standard format for dates of yyyymmdd
Far away is close at hand in the images of elsewhere.
Anon.
July 14, 2006 at 8:21 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply