New to Stored Procedure - SQL Server Forums

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

New to Stored Procedure

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-11 : 18:19:01

Hi

I am completely new to stored procedure as well as sql server 2008 , please bare with my basic questions, i have a database from which data is being pulled reporting server and i need to create a metrics table in which the logic is already created for some specific colomns , for eg run time of a job , for the column runtime in a table i need have a syntax something like finish_time - start_time = runtime , and this logic has to run every time i call procedure runtime , so i think i need to create a stored procedure for this . could any one please help me creating a stored procedure and updating data into my metric table , or point me towards where i can get more examples similar to this

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 00:29:11

something like:

CREATE PROC yourprocedurename
AS
SELECT other fields...,
DATEDIFF(dd,start_time,finish_time) AS runtime
..
FROM table
..
GO

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 04:13:12

As you are "completely new" :) I recommend using the "English" form for hte first parameter for the DATEDIFF function - you can use Day, Month, Minute, and so on. So:

DATEDIFF(Day,start_time,finish_time) AS runtime

that way you don't have to guess whether the shorthand "M" will be Month, Minute or Millisecond :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-12 : 04:29:29

quote:
Originally posted by Kristen

As you are "completely new" :) I recommend using the "English" form for hte first parameter for the DATEDIFF function - you can use Day, Month, Minute, and so on. So:

DATEDIFF(Day,start_time,finish_time) AS runtime

that way you don't have to guess whether the shorthand "M" will be Month, Minute or Millisecond :)

I always used to do that and suggest
You are surprisingly visiting here

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-12 : 04:40:08

"You are surprisingly visiting here"

Cheers and Hi! I'm just sitting around waiting for brilliant input on my question over in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138200. Peso already replied, what's keeping you?

(Is it your Excel thread which seems to have become a Forum in its own right?

)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-12 : 05:06:48

<<
Is it your Excel thread which seems to have become a Forum in its own right?
>>

Now it is top 333K

Madhivanan

Failing to plan is Planning to fail

dattatreysindol
Starting Member

20 Posts

Posted - 2010-01-12 : 12:12:26

Hi,

If you are trying to create a report in SSRS (Matrix/Table) which involves some calculations then instead of creating & calling a stored procedure, I would recommend you to use Functions in SSRS.

Check this articles for more details about the use of functions in SSRS: [url]http://mytechnobook.blogspot.com/2010/01/how-to-use-functions-in-reporting.html[/url]

Hope that helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:18:02

quote:
Originally posted by dattatreysindol

Hi,

If you are trying to create a report in SSRS (Matrix/Table) which involves some calculations then instead of creating & calling a stored procedure, I would recommend you to use Functions in SSRS.

Check this articles for more details about the use of functions in SSRS: [url]http://mytechnobook.blogspot.com/2010/01/how-to-use-functions-in-reporting.html[/url]

Hope that helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

do you mean doing calculations in report is more efficient than precalculating and bringing them as a part of source dataset?

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-12 : 19:36:13

thank you for the replies ....

can anyone please let me know where to write the procedure and where to call in , i mean i have created a table with all my columns for eg :
Coulumn datatype
queue_time datetime
runtime datetime
sucessfullbuilds number
failedbuild number etc

i have created a stored procedure , as VISAKH16 said,

CREATE PROC yourprocedurename
AS
SELECT other fields...,
DATEDIFF(dd,start_time,finish_time) AS runtime
..
FROM table
..
GO

when i excedute i get the meesage completed suceefully but i dont get the data into my table , could any one point me where i can get the step by step procedure for getting data into my table for all the columns

-Thanks

dattatreysindol
Starting Member

20 Posts

Posted - 2010-01-12 : 22:35:29

Hi There,

Yes! If it is a simple calculation then doing the calculations in the report would be more efficient. Since in SQL Server 2008 Reporting Services most of the report processing happens on the client side, having these calculations in the report would put the load on the client box whereas if you use the Stored Procedures then everytime the report is run then the stored procedure is called/executed which might put more load on the server hosting the Stored Procedures, especially when the number of concurrent users for the report increases.

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 02:14:24

" i dont get the data into my table"

Please post your code, formatted please, (rather than the example that VISAKH16 gave you :) )

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-13 : 15:34:31

Here is my code:
USE [ASWTrans]
GO
/****** Object: StoredProcedure [dbo].[Total_Pend_Time] Script Date: 01/13/2010 12:18:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Total_Pend_Time]
-- Add the parameters for the stored procedure here
@EC_JOB_IND varchar(255),
@CRM_SUB_SYSTEM varchar(50),
@EC_START_TIME datetime,
@TOTAL_PEND_TIME int

AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
Insert into
dbo.METRIC_BY_SUBSYSTEM
(
EC_JOB_IND,
CRM_SUB_SYSTEM,
EC_START_TIME,
TOTAL_PEND_TIME
)
(
Select
EC_JOB_IND,
CRM_SUB_SYSTEM,
EC_START_TIME,
SUM(DATEDIFF(minute,EC_RUNNABLE_TIME,EC_START_TIME)
)

From dbo.DW_T_EC_SBA
Where EC_STEP_INDEX IS NOT NULL AND EC_RUNNABLE_MILLIS IS NOT NULL
group by CRM_SUB_SYSTEM, ec_job_ind,EC_START_TIME

)

END

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 16:38:19

Two things..

I dont see you using the parameters you have defined in the stored proc...

And next..how did you execute the stored proc..Did you actually do a

exec  [dbo].[Total_Pend_Time]

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-13 : 16:52:02

I right clicked on the stored procedure and cicked on execute stored procedure , i am not sure how to do this could you please help me , i am using sql server 2008 and this is the first time i am creating stored procedure , can please let me know how to write the code too ..with the logic i have given my insert statement ?

-thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 16:59:46

Execute this in SSMS

USE [ASWTrans]
GO
/****** Object: StoredProcedure [dbo].[Total_Pend_Time] Script Date: 01/13/2010 12:18:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Total_Pend_Time] 
AS
BEGIN

SET NOCOUNT ON;
-- Insert statements for procedure here
Insert into 
dbo.METRIC_BY_SUBSYSTEM
(
EC_JOB_IND,
CRM_SUB_SYSTEM,
EC_START_TIME,
TOTAL_PEND_TIME
)
Select 
EC_JOB_IND,
CRM_SUB_SYSTEM,
EC_START_TIME,
SUM(DATEDIFF(minute,EC_RUNNABLE_TIME,EC_START_TIME))
From dbo.DW_T_EC_SBA
Where EC_STEP_INDEX IS NOT NULL AND EC_RUNNABLE_MILLIS IS NOT NULL 
group by CRM_SUB_SYSTEM, ec_job_ind,EC_START_TIME

END

This will alter your existing stored procedure...Once this is done successfully..In a query window execute the SP with the command

exec [dbo].[Total_Pend_Time]

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-13 : 17:06:46

yes i did , but i get all NULLS in my table..:-(

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 17:20:09

Are there any rows for this SELECT?

Select 
EC_JOB_IND,
CRM_SUB_SYSTEM,
EC_START_TIME,
SUM(DATEDIFF(minute,EC_RUNNABLE_TIME,EC_START_TIME))
From dbo.DW_T_EC_SBA
Where EC_STEP_INDEX IS NOT NULL AND EC_RUNNABLE_MILLIS IS NOT NULL 
group by CRM_SUB_SYSTEM, ec_job_ind,EC_START_TIME

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-13 : 17:40:07

Oh yeah , i got the data if i use a specific criteria for a specific date ..thank you !!! :-)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-13 : 17:42:56

Ok. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources