Solved

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

Posted on 2015-02-09
9
159 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 65

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 65

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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