Using a stored procedure to insert multiple dates.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using a stored procedure to insert multiple dates.

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2003-09-24 : 17:19:45

Hi,

I'm calling an sp from a asp.net page which utilizes an article I found on this site. ([url]http://www.sqlteam.com/item.asp?ItemID=637[/url]).

I am passing the SP a comma delimited list of the dates in this format. "dd/mm/year,"

My goal is to write logic in my SP that if it violates the Primary Key which would be userID,Date, it will remove that particular entry from the table.

Here is my sp code.

CREATE PROCEDURE [uc_insert_availability]

(
@talentID smallint,
@array varchar(500),
@separator char(1) = ','
)
AS

--now insert availability dates

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(50) -- this holds each array value as it is returned

-- left of the separator character for each array value
set @Array = @Array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @Separator + '%' , @Array)
select @array_value = left(@Array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
INSERT INTO ucAvailability (talentID,dateAvailable) VALUES (@talentID,@array_value)

-- If PK Violation, then remove availability.
if @@error > 1
BEGIN
DELETE FROM ucAvailability WHERE talentID = @talentID AND dateAvailable = @array_value
END

-- This replaces what we just processed with and empty string
select @Array = stuff(@Array, 1, @separator_position, '')

end

set nocount off
GO]

Now, when I run that it doesn't seem to update the table. No error is returned. But when I run it with a date that is already in the table it does return a PK violation, but does not delete the entry. I hope someone can help me out with this.

misc info. I'm using the asp.net calendar control for the selecting of dates.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-24 : 17:24:00

this part:


- If PK Violation, then remove availability. 
if @@error > 1 
BEGIN
DELETE FROM ucAvailability WHERE talentID = @talentID AND dateAvailable = @array_value
END

is not needed. if there is a PK violation, the INSERT will fail. when you do this, you are deleting the OTHER value that's already in your table.

just ignore PK errors and you should be fine.

i.e.,

if @Error <> 0
set @dummy = ''

or something like that .

- Jeff

robc
Yak Posting Veteran

60 Posts

Posted - 2003-09-24 : 17:29:48

also. I occasionally get this error.

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.
The statement has been terminated."

This was converting 23/09/2003 from varchar to smalldatetime.

robc
Yak Posting Veteran

60 Posts

Posted - 2003-09-24 : 17:32:59

quote:
Originally posted by jsmith8858

this part:

is not needed. if there is a PK violation, the INSERT will fail. when you do this, you are deleting the OTHER value that's already in your table.

just ignore PK errors and you should be fine.

i.e.,

Thanks for your quick reply Jeff. But if there is a PK violation, I'd like to removed that date from the table. Basically the user is selecting what dates they are available. So if they select that date again, it means they wish to remove thier availability. Hope this helps.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-25 : 10:10:28

the "This was converting 23/09/2003 from varchar to smalldatetime" is because your dateformat is NOT specified to be DMY....the default is US format MDY!!!

best advice is to use all dates in 'yyyymmdd' format...search here for DATEFORMAT and several topics will come up.
Andrew

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-25 : 10:16:30

quote:

Basically the user is selecting what dates they are available. So if they select that date again, it means they wish to remove thier availability. Hope this helps.

information that might've been useful earlier!

use two parameters, then: one full of dates to remove, one full of dates to add. Don't try to do both at once.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources