I am new to SQL world

Question

Post reply

I am new to SQL world

Kabina

Old Hand

Points: 335
More actions
November 18, 2019 at 3:16 am
Go to Answer
#3698404
I eager to learn sql , I am currently working on Access . I want to learn SQL i have seen lots of videos and understand how sql query works.
I found this forum is one of great forum to learn. I wish you all the best. I mostly retrieve data for reporting. I want strong command on my SQL query.
I have following few question in mind.
can we use procedure in our query to retrieve data ?
can we use function in our query to retrieve data ?
Can sql query result send via outlook with attachment automatically ?
I need to retrieve data using sql for reporting so that I could use it in power bi and micro strategy, kindly let me know what should I learn most.
- This topic was modified 4 years, 5 months ago by Kabina.
- This topic was modified 4 years, 5 months ago by Kabina.
Jeff Moden

SSC Guru

Points: 1003863
More actions
November 18, 2019 at 1:59 pm
Answer
#3698504

Kabina wrote:
can we use procedure in our query to retrieve data ?
Yes. Contrary to popular belief, you CAN call a stored procedure in a SELECT query using things like OPENROWSET.
Kabina wrote:
can we use function in our query to retrieve data ?
Yes. However, there are 3 different common types of functions and, when it comes to performance, it makes a huge difference on which type you use and how you use it.
Heh... and very much against common knowledge and supposed "Best Practices", you can actually call stored procedures from functions. That's why they called "FUNctions". You do have to be careful when you do something that wasn't designed to happen or you'll quickly find out what the "FU" in "FUN" stands for. 😀
Kabina wrote:
Can sql query result send via outlook with attachment automatically ?
Thom covered this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

can we use procedure in our query to retrieve data ?

You can use an SP to return data, yes, but not inside a (SELECT?) query. Executing an SP would be its own statement

can we use function in our query to retrieve data ?

Yes

Can sql query result send via outlook with attachment automatically ?

You can't send it via Outlook, but you can use sp_send_dbmail which can use POP3/IMAP to send emails.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99126 More actions · Answer 2

Jeff Moden wrote:

Kabina wrote:
can we use procedure in our query to retrieve data ?
Yes. Contrary to popular belief, you CAN call a stored procedure in a SELECT query using things like OPENROWSET.

Very true; i was more thinking along the side of "standard" SQL, rather than using Psuedo-Dynamic. 🙂

Jeff Moden wrote:

Yes. However, there are 3 different common types of functions and, when it comes to performance, it makes a huge difference on which type you use and how you use it.

Worth noting that there are 4 now, 2019 finally added inline Scalar functions, however, not applicable on 2012.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Thanks, Thom. Understood on the 2019 stuff. I was under the impression that it wasn't actually a "4th" type... I was under the impression that if SQL Server could run a non-iTVF function inline, that it would "auto-magically".

Understood on the "normal" stuff, as well. I just didn't want to limit anyone's imagination with "normal". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kabina Old Hand Points: 335 More actions · Answer 4

Thank you all of you for explaining. If I want to retrieve data from SQL server and Oracle. Is there any difference in query structure between SQL and Oracle or almost same as I saw in SQL we have function Getdate() but in oracle it sysdate()? Are there any more like that , Can I have list ?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Yeah... confirmed. It's not something that you can assign. It just happens if you do things right. You can, however, prevent it. I don't know why anyone would want to do that.

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

Heh... I wonder if Joe Celko will recommend turning this feature off because it's Microsoft Proprietary and won't actually migrate. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99126 More actions · Answer 6

Jeff Moden wrote:

Thanks, Thom. Understood on the 2019 stuff. I was under the impression that it wasn't actually a "4th" type... I was under the impression that if SQL Server could run a non-iTVF function inline, that it would "auto-magically".

Yes, true (though I'm not sure how much I trust SQL Server doing it "auto-magically") and will likely ensure that where I can I'll write my Scalar Functions in the inline syntax (CREATE FUNCTION ... RETURNS date AS BEGIN RETURN {expression). but either way the inline scalar function do perform much better than their multi-line versions. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Kabina wrote:

Thank you all of you for explaining. If I want to retrieve data from SQL server and Oracle. Is there any difference in query structure between SQL and Oracle or almost same as I saw in SQL we have function Getdate() but in oracle it sysdate()? Are there any more like that , Can I have list ?

There are huge differences both in structure an syntax between what is available in SQL Server and Oracle. Don't get me wrong... there are a lot of similarities but there is a fairly decent paradigm shift between the two.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

Thom A wrote:

Jeff Moden wrote:
Thanks, Thom. Understood on the 2019 stuff. I was under the impression that it wasn't actually a "4th" type... I was under the impression that if SQL Server could run a non-iTVF function inline, that it would "auto-magically".
Yes, true (though I'm not sure how much I trust SQL Server doing it "auto-magically") and will likely ensure that where I can I'll write my Scalar Functions in the inline syntax (CREATE FUNCTION ... RETURNS date AS BEGIN RETURN {expression). but either way the inline scalar function do perform much better than their multi-line versions. 🙂

Also true... Gail did some simple testing and the difference in performance (where it works) can be absolutely incredible.

https://sqlinthewild.co.za/index.php/2019/05/07/in-line-scalar-functions-in-sql-server-2019/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Jeff Moden wrote:

Kabina wrote:
Thank you all of you for explaining. If I want to retrieve data from SQL server and Oracle. Is there any difference in query structure between SQL and Oracle or almost same as I saw in SQL we have function Getdate() but in oracle it sysdate()? Are there any more like that , Can I have list ?
There are huge differences both in structure an syntax between what is available in SQL Server and Oracle. Don't get me wrong... there are a lot of similarities but there is a fairly decent paradigm shift between the two.

p.s. The list of differences would cover volumes. Someone can provide a "short list" but it'll end up ultimately being seriously inadequate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kabina Old Hand Points: 335 More actions · Answer 10

Hi I am trying to get :

if today is Monday then it will show result from Friday to Sunday

Else

if today is not Monday then I will show you result one day before.

please note my PODATE is 'yyyymmdd' format.

This is what I made . Kindly check if it is ok.

select * from PODATA where PODATE BETWEEN

CASE

WHEN

DATENAME(WEEKDAY, GETDATE())='Monday'

THEN

CONVERT(CHAR(8), GETDATE(), 112)-3

ELSE

CONVERT(CHAR(8), GETDATE(), 112)-1

END

AND

CASE

WHEN

DATENAME(WEEKDAY, GETDATE())='Monday'

THEN

CONVERT(CHAR(8), GETDATE(), 112)-1

ELSE

CONVERT(CHAR(8), GETDATE(), 112)-1

END

This reply was modified 4 years, 5 months ago by Kabina.
This reply was modified 4 years, 5 months ago by Kabina.
This reply was modified 4 years, 5 months ago by Kabina.

Thom A SSC Guru Points: 99126 More actions · Answer 11

This is pseudo-SQL, but seems like you'd be better off with a start and end parameter, which you set at the start of the batch:

DECLARE @StartDate date = DATEADD(DAY,-CASE DATENAME(WEEKDAY, GETDATE()) WHEN 'Monday' THEN 3 ELSE 1 END,GETDATE()),
        @EndDate date = GETDATE();

SELECT {Columns}
FROM dbo.YourTable
WHERE YourDate >= @StartDate
  AND YourDate < @EndDate;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Kabina wrote:

Hi I am trying to get :
if today is Monday then it will show result from Friday to Sunday
Else
if today is not Monday then I will show you result one day before.
please note my PODATE is 'yyyymmdd' format.
This is what I made . Kindly check if it is ok.
select * from PODATA where PODATE BETWEEN
CASE
WHEN
DATENAME(WEEKDAY, GETDATE())='Monday'
THEN
CONVERT(CHAR(8), GETDATE(), 112)-3
ELSE
CONVERT(CHAR(8), GETDATE(), 112)-1
END
AND
CASE
WHEN
DATENAME(WEEKDAY, GETDATE())='Monday'
THEN
CONVERT(CHAR(8), GETDATE(), 112)-1
ELSE
CONVERT(CHAR(8), GETDATE(), 112)-1
END

In the future, it would be much more helpful to you as a person with a question, to us as persons who have answers to such questions, and to others who will need both in the future if you were to create a new post for new questions not related to a given thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kabina Old Hand Points: 335 More actions · Answer 13

Should I use declare variable above query or I can use in between as well ?

This reply was modified 4 years, 5 months ago by Kabina.