change the following query to storedprocedure

Question

Post reply

change the following query to storedprocedure

koolme_85

Ten Centuries

Points: 1153
More actions
January 3, 2011 at 1:44 pm

#243035

select AccountDim.accountbranchnumber,AccountDim.accountdimkey,AccountDim.accountnumber,

AccountDim.accounttype,case AccountDim.accounttype when 'D' then 'Checking' when 'S' then

'Savings' when 'X' then 'Club' else ''end as accounttypedesc,AccountDim.productcode,

DDMasterFacts.currentbalance,DDMasterFacts.postdatedimkey,TimeDim.SLEOMInd,CASE

SUBSTRING(CAST(DDMasterFacts.postdatedimkey AS varchar(8)),5,2) when 01 then 'Jan'+''+

LEFT(DDMasterFacts.postdatedimkey,4) when 02 then 'Feb'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 03 then 'Mar'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 04 then 'Apr'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 05 then 'May'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 06 then 'Jun'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 07 then 'Jul'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 08 then 'Aug'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 09 then 'Sep'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 10 then 'Oct'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 11 then 'Nov'+''+

LEFT(DDmasterFacts.postdatedimkey,4) Else 'Dec'+''+

LEFT(DDmasterFacts.postdatedimkey,4) end as "monthname",

ProductTypeDim.condensecodedesc,ProductTypeDim.statementdesc,BranchDim.branchname,GroupDim.groupname,

LEFT(GroupDim.groupname,2) AS division

from DDMasterFacts inner join AccountDim on DDMasterFacts.accountdimkey =

AccountDim.accountdimkey inner join TimeDim on DDMasterFacts.postdatedimkey =

TimeDim.TimeDimKey inner join ProductTypeDim on AccountDim.productcode =

ProductTypeDim.producttype and AccountDim.accounttype = ProductTypeDim.accounttype inner

join BranchDim on AccountDim.accountbranchnumber = BranchDim.branchnbr inner join GroupDim

on BranchDim.parentkey = GroupDim.groupid

where AccountDim.statuscode NOT IN (2,8) AND TimeDim.SLEOMInd = 'y' and

DDMasterFacts.postdatedimkey between (select LastPostDate.LastPostDatedimkey - 1000 from LastPostDate)

and (select LastPostDate.LastPostDatedimkey from LastPostDate)

order by

division,GroupDim.groupname,AccountDim.accountbranchnumber,DDMasterFacts.postdatedimkey

Viewing 15 posts - 1 through 15 (of 19 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

LutzM

SSC Guru

Points: 107049

January 3, 2011 at 2:02 pm

#1268668

Something like

CREATE PROCEDURE NameHere AS

SELECT...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 2

...and the question is?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

LutzM SSC Guru Points: 107049 More actions · Answer 3

The question is in the title:

change the following query to storedprocedure

how to change my query to SP

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

koolme_85 Ten Centuries Points: 1153 More actions · Answer 4

koolme_85

Ten Centuries

Points: 1153

January 3, 2011 at 2:08 pm

#1268672

so its going to be

create proc myproc

as

(the same select statement that i have posted )

go

is that it ?

LutzM SSC Guru Points: 107049 More actions · Answer 5

koolme_85 (1/3/2011)

so its going to be

create proc myproc

as

(the same select statement that i have posted )

go

is that it ?

Yes. That simple. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 6

It depends.

Do you want any parameters? Perhaps something for the Where clause. That would be pretty normal for a stored procedure.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

koolme_85 Ten Centuries Points: 1153 More actions · Answer 7

koolme_85

Ten Centuries

Points: 1153

January 3, 2011 at 2:21 pm

#1268678

can you tell me how i can use parameters for the where clause in the following query

thanks

regards

greg

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

koolme_85 (1/3/2011)

can you tell me how i can use parameters for the where clause in the following query

thanks

regards

greg

Greg,

These are very basic things to understand working with SQL Server, may I recommend doing a little reading and research with google on your own.

For example: Googling: SQL Server tutorial stored procedure

Returns 3 articles on what are they, and how to make one, before you start hitting anything that isn't obvious.

This article seemed sound, and is multi-paged to walk you through each component:

http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GSquared SSC Guru Points: 260824 More actions · Answer 9

koolme_85 (1/3/2011)

can you tell me how i can use parameters for the where clause in the following query

thanks

regards

greg

Do you know what parameters are?

Do you know what a Where clause is?

Do you know what stored procedures are?

I need to know these things before I can begin to answer your question.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

koolme_85 (1/3/2011)

select AccountDim.accountbranchnumber,AccountDim.accountdimkey,AccountDim.accountnumber,

AccountDim.accounttype,case AccountDim.accounttype when 'D' then 'Checking' when 'S' then

'Savings' when 'X' then 'Club' else ''end as accounttypedesc,AccountDim.productcode,

DDMasterFacts.currentbalance,DDMasterFacts.postdatedimkey,TimeDim.SLEOMInd,CASE

SUBSTRING(CAST(DDMasterFacts.postdatedimkey AS varchar(8)),5,2) when 01 then 'Jan'+''+

LEFT(DDMasterFacts.postdatedimkey,4) when 02 then 'Feb'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 03 then 'Mar'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 04 then 'Apr'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 05 then 'May'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 06 then 'Jun'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 07 then 'Jul'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 08 then 'Aug'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 09 then 'Sep'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 10 then 'Oct'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 11 then 'Nov'+''+

LEFT(DDmasterFacts.postdatedimkey,4) Else 'Dec'+''+

LEFT(DDmasterFacts.postdatedimkey,4) end as "monthname",

ProductTypeDim.condensecodedesc,ProductTypeDim.statementdesc,BranchDim.branchname,GroupDim.groupname,

LEFT(GroupDim.groupname,2) AS division

from DDMasterFacts inner join AccountDim on DDMasterFacts.accountdimkey =

AccountDim.accountdimkey inner join TimeDim on DDMasterFacts.postdatedimkey =

TimeDim.TimeDimKey inner join ProductTypeDim on AccountDim.productcode =

ProductTypeDim.producttype and AccountDim.accounttype = ProductTypeDim.accounttype inner

join BranchDim on AccountDim.accountbranchnumber = BranchDim.branchnbr inner join GroupDim

on BranchDim.parentkey = GroupDim.groupid

where AccountDim.statuscode NOT IN (2,8) AND TimeDim.SLEOMInd = 'y' and

DDMasterFacts.postdatedimkey between (select LastPostDate.LastPostDatedimkey - 1000 from LastPostDate)

and (select LastPostDate.LastPostDatedimkey from LastPostDate)

order by

division,GroupDim.groupname,AccountDim.accountbranchnumber,DDMasterFacts.postdatedimkey

If the column being interogated is a DATETIME datatype in the following....

CASE



SUBSTRING(CAST(DDMasterFacts.postdatedimkey AS varchar(8)),5,2) when 01 then 'Jan'+''+

LEFT(DDMasterFacts.postdatedimkey,4) when 02 then 'Feb'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 03 then 'Mar'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 04 then 'Apr'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 05 then 'May'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 06 then 'Jun'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 07 then 'Jul'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 08 then 'Aug'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 09 then 'Sep'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 10 then 'Oct'+''+

LEFT(DDmasterFacts.postdatedimkey,4) when 11 then 'Nov'+''+

LEFT(DDmasterFacts.postdatedimkey,4) Else 'Dec'+''+

LEFT(DDmasterFacts.postdatedimkey,4) end as "monthname",

... then all of that can be simply replaced with...

LEFT(DATENAME(mm,DDmasterFacts.postdatedimkey),3) + ' ' +



DATENAME(yy,DDmasterFacts.postdatedimkey) AS [monthname],

If the column in an Integer, it should probably be the DATE datatype, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

koolme_85 Ten Centuries Points: 1153 More actions · Answer 11

koolme_85

Ten Centuries

Points: 1153

January 5, 2011 at 7:02 am

#1269479

yeh i do know what they are

GSquared SSC Guru Points: 260824 More actions · Answer 12

koolme_85 (1/5/2011)

yeh i do know what they are

Good. Then pick what columns you will usually filter the query by, and add parameters for those columns, then compare them to the columns in the Where clause. That's how you use parameters in the Where clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

koolme_85 Ten Centuries Points: 1153 More actions · Answer 13

koolme_85

Ten Centuries

Points: 1153

January 5, 2011 at 9:53 am

#1269604

i did but now its throwing the arithematic over flow error

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type datetime.

after i replaced the code with

LEFT(DATENAME(mm,DDmasterFacts.postdatedimkey),3) + ' ' + DATENAME(yy,DDmasterFacts.postdatedimkey) AS [monthname],

GSquared SSC Guru Points: 260824 More actions · Answer 14

GSquared

SSC Guru

Points: 260824

January 5, 2011 at 9:54 am

#1269606

Are the parameters the same data types as the columns?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon