Solved

convert select query to function / stored procedure (T-SQL)

Posted on 2015-02-09
9
161 Views
Last Modified: 2015-02-11
Dear Experts,

I have some knowledge of T-SQL.
I have created a select query which suits my needs.

However, I would like to convert this select query to function / stored procedure (?)
and pass @dzien as variable so that I can call it from e.g.
Visual Basic in Excel or even join with other tables.

Something like

1.

{CALL dbo.returnmyview (2015-01-01)}

or better
 
2. select * from dbo.returnmyview (2015-01-01) join XXXXX

I am sorry for my crazy syntax but I hope you get the point.

The select query has been uploaded here:
http://www78.zippyshare.com/v/askruGQ6/file.html


thank you in advance!
best regards
Jarek
0
Comment
Question by:ja-rek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
9 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40599506
My work spam filter prevented me from viewing the outside link, so here's a mockup
CREATE PROC returnmyview (@dzien date) AS

/*
Looky here, meaningful code comments!

02-09-15  ja-rek  Completely original work
*/

SELECT bingo, bango, bongo
FROM SomeTable
WHERE dzien = @dzien
GO

Open in new window

0
 
LVL 1

Author Comment

by:ja-rek
ID: 40599527
Hi Jim,

thanks a lot - that works!
that's the simpler version, can you please convert it to version 2? (as I have it described in my question)

so that I can use join, union etc on the output, something like

select * from dbo.returnmyview (2015-01-01) join XXXXX

Jarek
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40599537
I recommend against a tvf, as the Query Compiler can't incorporate table-valued functions into a query plan, so execution will be off.

Explain why you need it.
0
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40599541
You need to use an in-line table-valued function, not a multi-line TVF.

Please post the actual code so that such a function can be written.

But just post the query directly.  NO chance I'm downloading anything from a site with that many flashing colors!!
0
 
LVL 1

Author Comment

by:ja-rek
ID: 40599564
Hello and thanks for help!

declare @dzien DATETIME --@dzien is my parameter that I want to pass
set @dzien = {ts '2015-01-31 00:00:00'}

select 
PRE_Kod,
PRE_Imie1,
PRE_Nazwisko,
dkm_nazwa,
PRE_DataUr,
PRE_Plec,
left(pre_kategoriaopis, 2) CAT,
CAST (pre_etaetatl as decimal) / CAST (pre_etaetatm as decimal) MANYEARS,
case when pra_parentid is null then 1 else 0 end HEADCOUNT,
case when PRE_ZatrudnionyDo< {ts '2999-12-31 00:00:00'} then 1 else 0 end TEMPORARIES,
PRE_MLDWojewodztwo,
PRE_ETAStawka, 
PRE_ETAWymiar, 
PRE_DataOd, 
PRE_DataDo, 
PRE_ZatrudnionyOd, 
PRE_ZatrudnionyDo
from cdn.pracetaty
JOIN CDN.Pracidx ON PRI_PraId = PRE_PraId
join cdn.prackod on pra_praid = pre_praid
left join cdn.DaneKadMod on DKM_DkmId = PRE_ETADkmIdStanowisko 
where pri_typ = 1
and PRE_DataOd<@dzien and (PRE_DataDo is null or PRE_DataDo>=@dzien)
and PRE_ZatrudnionyOd<@dzien and (PRE_ZatrudnionyDo>=@dzien or PRE_ZatrudnionyDo is null)

Open in new window



why I need it?
a pro would find a more efficient workaround, but for me the possibility to use select instead of exec is more convenient.
the table/database is rather small and I don't think I will suffer from a loss in speed (it this troubles you).
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 40599570
SELECT *
FROM cdn.get_pracetaty_matches ('20150131')



CREATE FUNCTION cdn.get_pracetaty_matches (
    @dzien datetime
)
RETURNS TABLE
AS
RETURN (
    select
    PRE_Kod,
    PRE_Imie1,
    PRE_Nazwisko,
    dkm_nazwa,
    PRE_DataUr,
    PRE_Plec,
    left(pre_kategoriaopis, 2) CAT,
    CAST (pre_etaetatl as decimal) / CAST (pre_etaetatm as decimal) MANYEARS,
    case when pra_parentid is null then 1 else 0 end HEADCOUNT,
    case when PRE_ZatrudnionyDo< {ts '2999-12-31 00:00:00'} then 1 else 0 end TEMPORARIES,
    PRE_MLDWojewodztwo,
    PRE_ETAStawka,
    PRE_ETAWymiar,
    PRE_DataOd,
    PRE_DataDo,
    PRE_ZatrudnionyOd,
    PRE_ZatrudnionyDo
    from cdn.pracetaty
    JOIN CDN.Pracidx ON PRI_PraId = PRE_PraId
    join cdn.prackod on pra_praid = pre_praid
    left join cdn.DaneKadMod on DKM_DkmId = PRE_ETADkmIdStanowisko
    where pri_typ = 1
    and PRE_DataOd<@dzien and (PRE_DataDo is null or PRE_DataDo>=@dzien)
    and PRE_ZatrudnionyOd<@dzien and (PRE_ZatrudnionyDo>=@dzien or PRE_ZatrudnionyDo is null)
)
GO
0
 
LVL 1

Author Comment

by:ja-rek
ID: 40602942
I've requested that this question be closed as follows:

Accepted answer: 300 points for ScottPletcher's comment #a40599570
Assisted answer: 200 points for Jim Horn's comment #a40599506
Assisted answer: 0 points for ja-rek's comment #a40599564

for the following reason:

thanks a lot for help! :)
0
 
LVL 1

Author Closing Comment

by:ja-rek
ID: 40602943
thanks a lot for help! :)
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question