?
Solved

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

Posted on 2015-02-09
9
Medium Priority
?
181 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 800 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

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 70

Accepted Solution

by:
Scott Pletcher earned 1200 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

Technology Partners: 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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

569 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