October 12, 2008 at 6:57 pm
Hi There,
I have a table contains a start date and end date columns. I need to be able to check that the dates passed into the proc do not exist in the table already, if they do then I will just give the user a message to choose another date. I need them to be totally unique to the point where even the times should not overlap. I'm just not sure of the correct way to do this. The columns are datetime data types.
Can someone help with a working sample please?
set @Existing = (
select count(*) from table
where [Start] <= convert(datetime,@Start,109) and [End] <= convert(datetime,@End,109)
-----------
13/10/2008 12:00:00 AM
October 12, 2008 at 7:15 pm
The way you have this written it's not checking uniqueness, it's checking for an inquequality.
I think you want to check
if exists( select id from table where start= @start and end = @end)
select 1 -- error
else
select 0 -- ok
October 12, 2008 at 8:33 pm
Hi There,
It seems to be working I will need to play around with it I guess. Can I ask one more question?
1. How can I store this in a variable to use further down the procedure?
select @Existing = if exists( select ID from mytable where
[Start] = convert(datetime,@Start,120)
and [End] = convert(datetime,@End,120))
select 1
else
select 0
print @Existing
October 12, 2008 at 9:53 pm
This is how you can store it to a variable
IF EXISTS(
SELECT ID
FROM mytable
WHERE [Start] = convert(datetime,@Start,120)
AND [End] = convert(datetime,@End,120))
BEGIN
SELECT @Existing = 1
END ELSE BEGIN
SELECT @Existing = 0
END
.
October 12, 2008 at 10:09 pm
Thanks very much Jacob!
October 14, 2008 at 10:53 am
Seankerr - can I ask why you're doing this? If you're just keeping duplicate data from existing in the table, it'd be better to add a constraint to the column.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 14, 2008 at 2:25 pm
I have a similar but different problem--perhaps someone can steer me right:
I want to add a column (field) to a table if it doesn't exist but not if it does exist. I know that it cannot add a duplicate column, but it stops the script with the error message.
I have been trying:
IF NOT EXISTS (select field_name from table_name)
ALTER table_name
ADD field_name int
I get a msg 207 error. Ideas? Thanks, Sam
October 14, 2008 at 2:55 pm
Try this...
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
AND COLUMN_NAME = 'YourFieldName'
)
BEGIN
ALTER TABLE ....
END
Gary Johnson
Sr Database Engineer
October 15, 2008 at 9:40 am
Just what I needed--thanks, Gary.
Sam
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply