Need help with Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

Need help with Stored Procedure

Author

Topic

awalker
Starting Member

12 Posts

Posted - 2012-07-18 : 11:51:45

Hi all

I am new to SQL and need some help with a stored procedure. What I need is a stored procedure that does a select on a table where a date = @date, however, if the select does not find anything I need to then do a create using the @date to create a new record. I am just not sure how to do the if select returns nothing then do a create. I hope this made sense.

Thank you for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 11:56:22

use logic like


CREATE PROC yourprocname
@date date
AS

IF NOT EXISTS(SELECT 1 FROM table WHERE date=@date)
 INSERT INTO table....

 SELECT * FROM table WHERE date=@date
GO

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 11:56:53

This will create the row if it is not there then return the row - either existing or newly created.

create proc s_MyProc
@date datetime
as
set nocount on
if not exists (select * from tbl where dte = @date
begin
insert tbl(dte, ...) select @date, ....
end
select * frrom tbl where dte = @date

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

awalker
Starting Member

12 Posts

Posted - 2012-07-18 : 12:00:20

Thank you for such a quick reply, I will try out what you suggested. I really am grateful for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 12:08:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

awalker
Starting Member

12 Posts

Posted - 2012-07-18 : 14:48:54

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I have another question, if I may. What I am trying to do in the insert is: using the @date, I need to get what day of the week it is, then use the day of the week to select from another table called AppointmentTimes. This table has a startTime and an endTime for each day of the week. What I am hoping to inserts is something like this.

Lets say the date is 07/18/2012 so the day of the week is Wednesday, the start time is 10:00:00 and the end time is 17:00:00. I need to create a new row starting at 10:00:00 and for every hour until 17:00:00. So in the end I would end up with 7 new rows kind of like this:
date = 07/18/2012, time = 10:00:00
date = 07/18/2012, time = 11:00:00
date = 07/18/2012, time = 12:00:00
date = 07/18/2012, time = 13:00:00
date = 07/18/2012, time = 14:00:00
date = 07/18/2012, time = 15:00:00 ect....

I hope this made sense

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 15:00:11

Something like

;with cte as
(
select starttime, endtime
from AppointmentTimes a
where DayOfWeek = datename(dw,@date)
) ;
cte2 as
(
select t = starttime from cte
union all
select dateadd(hh,1,t) from cte1 where t < (select endtime from cte)
)
select @date + t
from cte2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

awalker
Starting Member

12 Posts

Posted - 2012-07-18 : 15:18:27

Thank you for your reply, I am sorry to say I don't really follow what you are saying there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 16:38:13

quote:
Originally posted by awalker

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I have another question, if I may. What I am trying to do in the insert is: using the @date, I need to get what day of the week it is, then use the day of the week to select from another table called AppointmentTimes. This table has a startTime and an endTime for each day of the week. What I am hoping to inserts is something like this.

Lets say the date is 07/18/2012 so the day of the week is Wednesday, the start time is 10:00:00 and the end time is 17:00:00. I need to create a new row starting at 10:00:00 and for every hour until 17:00:00. So in the end I would end up with 7 new rows kind of like this:
date = 07/18/2012, time = 10:00:00
date = 07/18/2012, time = 11:00:00
date = 07/18/2012, time = 12:00:00
date = 07/18/2012, time = 13:00:00
date = 07/18/2012, time = 14:00:00
date = 07/18/2012, time = 15:00:00 ect....

I hope this made sense

something like


;With TimeSlots (TimeSlot,EndTime)
AS
(
SELECT StartTime,EndTime
FROM AppointmentTimes a
WHERE dayofweekfield= DATENAME(dw,@date)
UNION ALL
SELECT DATEADD(hh,1,TimeSlot),EndTime
FROM TimeSlots
WHERE DATEADD(hh,1,TimeSlot)<= EndTime
)

SELECT @date,TimeSlot
FROM TimeSlots

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

awalker
Starting Member

12 Posts

Posted - 2012-07-18 : 17:16:57

That seems to work, I got back a list of the different times for that day. Now if I wanted to insert that list into my Appointments table how would I do my insert?

Thank you for your help, this all seems so strange to me.

awalker
Starting Member

12 Posts

Posted - 2012-07-18 : 20:25:16

Actually I figured it out. thank you for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 00:02:40

welcome

make sure you learn about common table expressions

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources