Convert query containing 3 CTE's into Stored Procedure

Question

Post reply

Convert query containing 3 CTE's into Stored Procedure

2Tall

SSChampion

Points: 14644
More actions
June 21, 2010 at 2:35 am

#223993

Hi. I built a query intended for a report. The query grew to eventually comprise of 3 CTE's and a UNION ALL.
Is it possible to alter the code and deliver as a stored procedure?
Forgive any coding poor practices, I am still at the bottom of the coding ladder.
I have attached the code in its current format.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Short answer: yes, just type

CREATE PROCEDURE someProcedure AS

BEGIN

your code here

END

Long answer: I suppose you already have tried this and you have issues, so, go on and post it.

-- Gianluca Sartori

2Tall SSChampion Points: 14644 More actions · Answer 2

Hi, I did kinda try that. I have played with a few variations.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyProc]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DueDate smalldatetime
DECLARE @From smalldatetime
DECLARE @To smalldatetime
DECLARE @ActivityCentre NVARCHAR(20)
SET @DueDate = GETDATE();

If I run EXEC MyProc it runs and returns no records. I have variables defined within the CTE's to filter data.

I would like the stored procedure to require parameters required @From, @To, @ActivityCentre

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

I would like the stored procedure to require parameters required @From, @To, @ActivityCentre

Try changing it to something like this

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[MyProc]

@DueDate smalldatetime = GETDATE(),

@From smalldatetime,

@To smalldatetime,

@ActivityCentre NVARCHAR(20)

AS

BEGIN

SET NOCOUNT ON;

If I run EXEC MyProc it runs and returns no records. I have variables defined within the CTE's to filter data.

Run your proc like this:

EXEC MyProc

@DueDate = '20100621', -- remove this parameter to use default --> getdate()

@From = '20100620',

@To = '20100621',

@ActivityCentre = 'somevalue'

-- Gianluca Sartori

2Tall SSChampion Points: 14644 More actions · Answer 4

Hi. I have attached code2 (stored procedure) which produces the following errors:

Msg 102, Level 15, State 1, Procedure MyProc, Line 2
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure MyProc, Line 43
Must declare the scalar variable "@From".
Msg 102, Level 15, State 1, Procedure MyProc, Line 77
Incorrect syntax near ','.
Msg 137, Level 15, State 2, Procedure MyProc, Line 84
Must declare the scalar variable "@ActivityCentre".
Msg 137, Level 15, State 2, Procedure MyProc, Line 118
Must declare the scalar variable "@From".

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

My fault, GETDATE() cannot be specified as default value in a stored procedure. I would change it to a default = NULL and then set it to GETDATE() if the parameter value is null.

Try the code attached.

-- Gianluca Sartori

2Tall SSChampion Points: 14644 More actions · Answer 6

Gianluca that works fine.

Many thanks for your help.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

You're welcome.

Glad I could help.

-- Gianluca Sartori