Passing parameter value to SP from table

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

SQL Server Development (2000)

Passing parameter value to SP from table

Author

Topic

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-01-09 : 06:12:24

Hi

Following SP takes two parameters , start and stop , for example start (23:10:19) and stop (23:14:19)

CREATE PROCEDURE [dbo].[increment_minutes]

@start datetime, @stop datetime
AS
SELECT DATEADD(second,60*number,@start)
FROM master..spt_values
WHERE type='p'
AND DATEADD(second,60*number,@start)<=@stop
GO

I have table which has start and stop values , i want to pass thoses values one by one to this store procedure
and insert the result into a table.

Thanks for your help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 06:45:10

alter PROCEDURE [dbo].[increment_minutes]
@start datetime, @stop datetime
AS
set nocount on
begin
SELECT DATEADD(second,60*number,@start)as datevalue INTO urtable
FROM master..spt_values
WHERE type='p'
AND DATEADD(second,60*number,@start)<=@stop
set nocount off
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 06:46:41

make it a function

CREATE PROCEDURE [dbo].[increment_minutes] 
(
@start datetime, @stop datetime
)
RETURNS @RESULTS TABLE
AS
RETURN
(
SELECT DATEADD(second,60*number,@start)
FROM master..spt_values
WHERE type='p'
AND DATEADD(second,60*number,@start)<=@stop
)

GO

then use it as below

SELECT *
FROM yourTable t
CROSS APPLY dbo.[increment_minutes](t.field1,t.field2)f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 06:47:15

quote:
Originally posted by bklr

alter PROCEDURE [dbo].[increment_minutes]
@start datetime, @stop datetime
AS
set nocount on
begin
SELECT DATEADD(second,60*number,@start)as datevalue INTO urtable
FROM master..spt_values
WHERE type='p'
AND DATEADD(second,60*number,@start)<=@stop
set nocount off
end

have you read question at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 06:49:20

just noticed this is 2000 forum. in that case ,cross apply wont work. you might need to use cursor or while loop and call function inside that

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-01-09 : 07:33:40

Hi guys

Thanks for the help. however I am lost now. I am using sql server 2000. And have no idea about cursors.
You help would be highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 08:39:24

quote:
Originally posted by ahmadjamalkhan

Hi guys

Thanks for the help. however I am lost now. I am using sql server 2000. And have no idea about cursors.
You help would be highly appreciated.

just was thinking why cant you do this inline? wont below be enough?


SELECT t.columns...,DATEADD(second,60*v.number,t.startdate)
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(second,60*v.number,t.startdate)<=t.enddate

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-02-23 : 07:42:59

It works , Thanks for your help
Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources