Solved

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

Posted on 2015-02-09
9
137 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
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
 
LVL 69

Expert Comment

by:ScottPletcher
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:
ScottPletcher 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now