Solved

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

Posted on 2015-02-09
9
151 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

761 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