Stored Procedure

Question

Post reply

Stored Procedure

Mahathi

Old Hand

Points: 320
More actions
July 12, 2006 at 6:12 am

#114676

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

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.

Mahathi Old Hand Points: 320 More actions · Answer 2

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

Loner SSC-Insane Points: 21279 More actions · Answer 3

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.

Ian Yates SSCoach Points: 19738 More actions · Answer 4

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.

Mahathi Old Hand Points: 320 More actions · Answer 5

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.

David Burrows SSC Guru Points: 65142 More actions · Answer 6

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],

@userid,

@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.

Mahathi Old Hand Points: 320 More actions · Answer 7

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.

David Burrows SSC Guru Points: 65142 More actions · Answer 8

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.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 9

probably one of the data parameters is being passed in as a string

www.sql-library.com[/url]