Scalar user defined functions in sql server Part 30
Vložit
- čas přidán 21. 07. 2024
- In this video we will learn about
1. User Defined Functions in sql server
2. Types of User Defined Functions
3. Creating a Scalar User Defined Function
4. Calling a Scalar User Defined Function
5. Places where we can use Scalar User Defined Function
6. Altering and Dropping a User Defined Function
Text version of the video
csharp-video-tutorials.blogspo...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our CZcams channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tutorials.blogspo...
All SQL Server Text Articles
csharp-video-tutorials.blogspo...
All SQL Server Slides
csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
czcams.com/users/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
THANK YOU SIR, I HOPE U DO FULL TUT ON SAS/SPSS OR DATA ANALYSIS WITH FEW PROGRAMS
+Wasim Bader Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
At the moment, we don't have videos on the technologies you mentioned. I will record and upload as soon as I can. Thank you very much for your patience.
Dot Net & SQL Server training videos for web developers
czcams.com/users/kudvenkatplaylists?view=1&sort=dd
You can order DVDs for offline viewing using the link below
www.pragimtech.com/Order.aspx
Code Samples & Slides are on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use our channel
czcams.com/video/y780MwhY70s/video.html
To receive email alerts, when new videos are uploaded, please subscribe to our channel
czcams.com/users/kudvenkat
Please click that THUMBS UP button below the video, if you like the videos
Thanks a million for sharing these resources with your friends
Best
Venkat
11 years later, and yet still benefiting from his lessons immensely. Thank you a lot!
Your indian accent is so cool.
Thank you for give this knowledge for free. Greetings from Brazil.
I am so in love with your teaching! I cannot believe how brilliant you are with all of these different technologies as well as the ability and willingness to teach and share. Thank you so much. Namaste.
I will recommend these videos to every SQL Server student. Thanks a lot Venkat.
You have been improving my coding since 2015, from C# to MVC to SQL
Hi Venkat, All your videos are simple and clear which makes anyone easy to understand, your tutorials are like a Gift, Thank you so much.
Hi Venkat, Want to thank you for your wonderful videos. They are simply great!!
So I was trying to create the CalculateAge() function myself and display the table with Name, DOB and Age before you showed us how to. I got the age values that were 10 years higher than your values and was confused on what I did wrong. Then I remembered the video is like 10 years old so all those people aged by 10 😂
😂
Thank you Kudvenkat so much for creating this series of SQL training videos for beginners. They are so easy to understand with the tables and query demonstrations. You are very good at teaching. I learn a few videos each day and decide to finish them all. Again, many thanks.
Do you teach Python? Hope you have some videos too.
Hi zainzoom,
To invoke a scalar function prefix dbo. before the name of the function. For example, if you have order with Id=101, then invoke the function as shown below.
Select dbo.GetTotalOrderValue(201), SomeOtherColumn
from SomeTable
where SomeCondition
I hope this will resolve your issue. If not let me know.
thanks man your sql videos are the best on youtube
Thank you Sir for a nice tutorial. All of your tutorials are so much easy to understand for every level of learners.
12 years later, and still benefiting from his lessons immensely. Thanks a lot Sir.!
very help full stuff, great job
Thanks for the video! Just became a subscriber.
Question: Is it possible to create a function in a 'general' location so that it can be called from any database that you might be using?
Your videos are just amazing. THANK YOU VERY MUCH SIR.
Hi Venkat, Your video help me a lot. I like all your video. A small request this video blur some part so that not clearly visible of creating function so please upload again with good visibility
another clear and concise explanation. thanks for this.
This is so incredibly clear and helpful. Thank you so much!
Thank you for the video tutorials. You're wonderful.
Very helpful videos for beginners, Thank you very much
Great session...very informative and complete
I love your session so much. Thanks for help me
Watching you in 2022. Your teaching style is very easy to grasp. God bless!
Hi Sir, you said the returned value can not be of cursor.. But we are using a function and returning p_recordset which is a sys ref cursor.. can you explain... my function is as below:
Create or replace Function fn_name (v_param in varchar2)
Return sys_refcursor as p_recordset sys_refcursor;
Begin
Open
OPEN p_recordset FOR
SELECT
FROM
WHERE
WHERE
(V1."param" = V_param);
RETURN p_recordset;
END;
In Korea that actually count you as 1 year old as soon as you are born, so Koreans didn't need to add the case statement. haha.
Seriously though, thanks for these videos. They help me so much with my job.
You are exceptional!
I really like your way of teaching.....Can you please provide a SAS tutorial basic to advanced??
Awesom Video, Please tell that is there any performance issue with functions in comparison to procedures?
Thanku sir... your are really great teacher.I want to meet you Sir & say many many thnx^^.... Sir please upload AJAX tutorials....
good work done, very useful
thanks Venkat... really very very post..
thank you very much sir,very good explanation
Tony the Tigerrrr: "This is Grrrreat!"
Thank you sir.. U r way teaching superb sir...
nice explanation thanks venkat
can you give me the name of a good SQL book for databse testing? thanks for your videos Sir
Venkat, this video on your Calculating Age Function is so Advanced and Essential. Will you (can you please make) be making a mini misc. tutorials on advanced topic such as this?
If someone else (since this question is three years old) has same problem with programming, remember that you should take these in pieces, not swallow as whole at once. In this case, make sure you understand CASE-WHEN-THEN-ELSE-END first and then advance from there.
SQUARE, BRING ME MY SWORD.
thanks for help.....I learn something ..:D
Hi, venkat could you please make videos on AJAX Controls. I watched a lot of videos but could not understand any of them because they are not a match to the way you explain. So, please make some videos on Ajax controls venkat. I request please...................
you are the best bro thank you
Thanks for this tutorial. Can you please explain the logic behind subtracting 1 from DATEDIFF(YEAR, DOB, GETDATE)) ) when the conditions are met. Thanks..
+kudvenkat, you created the function called CalculateAge and said that we can call this function in future to calculate the age. How different is it from creating a stored procedure for calculating the age and using this stored procedure to calculate the age?
+sai avinash edara the function can be used as a new column while stored procedure wont do that
+sai avinash edara if you watched his videos carefully then you will find out he has explained the difference.
just excellent sir
Thank you, sir.
make a video for different between stored procedure and function it a very important question in an interview
Check this one to begin with: stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions
Procedure can return zero or n values whereas function can return one value which is mandatory.
Procedures can have input/output parameters for it whereas functions can have only input parameters.
Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
Functions can be called from procedure whereas procedures cannot be called from function.
Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
We can go for transaction management in procedure whereas we can't go in function.
Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Thanks
Great, thanx
Why we are using .dbo as prefix in scalar user defined function? What is the reason behind this?
Happy birthday !
I guess my real question is, when should you use output parameters vs functions?
very helpful
Nice video and explanation is great. Thx...but you can get example not about data or date of birth...:-)
Couldn't you have used spCalculateAge if you had created an output parameter?
I'm not able to run this function. Can not find column or user defined function or aggregate or name ambiguous.
Please answer
How do I calculate age in year and month and date format?
what is the difference between functions and stored procedure.
I WRITE SAME QUERY TO CREATE FUNCTION IN MY LAPTOP SSMS BUT NOT ABLE TO CREATE FUNCTION PLEASE SIR CAN YOU EXPLAIN MORE THANK YOU
I am getting an error in the first line in @DOB Date of Create Function CalculateAge(@DOB Date), even I am getting an error when I using declare keyword: is not valid position, expecting.
great!!!!!!!!!!!!!!1
Thanx
Hi Venkat - Can you please write a function to find the number of Sundays in a month when you pass a date value?
Hi Amritha, PFB script ALTER FUNCTION
--ALTER PROC
fn_SundayCount(@dtDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @Count INT, @StartOfMonth DATE, @EndOfMonth INT, @MonthNo INT, @YearNo INT
SET @Count = 0
SET @EndOfMonth = 0
SET @MonthNo = DATEPART(MM, @dtDate)
SET @YearNo = DATEPART(YYYY, @dtDate)
SET @StartOfMonth = CONVERT(DATE, CONVERT(CHAR(4), @YearNo) + '-' + CONVERT(VARCHAR(2), @MonthNo) + '-01')
--SELECT CONVERT(CHAR(2), @MonthNo) AS [MonthNo], CONVERT(CHAR(4), @YearNo) AS [YearNo], @StartOfMonth AS [StartOfMonth]
--PRINT @StartOfMonth
IF @MonthNo = 2
BEGIN
IF (@YearNo % 4) = 0
SET @EndOfMonth = 29
ELSE
SET @EndOfMonth = 28
END
ELSE IF @MonthNo >= 8
BEGIN
IF (@MonthNo % 2) = 0
SET @EndOfMonth = 31
ELSE
SET @EndOfMonth = 30
END
ELSE
BEGIN
IF (@MonthNo % 2) = 1
SET @EndOfMonth = 31
ELSE
SET @EndOfMonth = 30
END
WHILE (DATEPART(DD, @StartOfMonth)
Amritha S / zackziss,
When finding number of Sundays in a month, the days of the month from 01 --> 28 will ALWAYS be 28 days, and will always contain 4 Sundays, no matter what. For your solution, start looping from day 29 instead of day 01 and save yourself 28 unnecessary WHILE Loop iterations on each procedure invocation...
*Instead of doing this:*
SET @Count = 0
...
SET @StartOfMonth = CONVERT(DATE, CONVERT(CHAR(4), @YearNo) + '-' + CONVERT(VARCHAR(2), @MonthNo) + '-01')
*do this:*
SET @Count = 4
...
SET @StartOfMonth = CONVERT(DATE, CONVERT(CHAR(4), @YearNo) + '-' + CONVERT(VARCHAR(2), @MonthNo) + '-29')
Best Regards.
I am trying to write a function that spits out a random number. - basically I have a table in which I want to add a column with has random numbers assigned to each record
Create Function fnRand()
returns int
as
begin
declare @num as int
set @num= round(rand()*100,0)
Return @num
end
I am getting the following error : please can you help:
Msg 443, Level 16, State 1, Procedure fnRand, Line 7
Invalid use of a side-effecting operator 'rand' within a function.
I think you cannot use RAND() function in user-defined functions
Aditya Samanta You could just use built-in functions like this:
select MyColumnList, abs(checksum(newid())) % 100 as [Random numbers] from MyTable
OR
select MyColumnList, cast(rand(checksum(newid())) * 100 as int) + 1 as [Random numbers] from MyTable
Hi Sir, I would like to take Microsoft 70-761 Exam. Just advise me how to pass the exam.
Sir can u upload SSRS and SSIS pls
Can we give two parameters to a function?
yes
You can give upto 1024.
😊
Why do you use "dbo." before a function's name? What's dbo? Is a database name?
DBO(database owner) full form. It is a schema name usually we use before table name or fumctions.
I GOT ERROR // Conversion failed when converting date and/or time from character string.///
me too!
good videos,however, fonts for sql server appears to be small;hence less viewable
instead of case statement:
declare @dob date
declare @age int
set @dob = '2021-08-28'
select datediff(month,@dob,getdate())/12
Please share sample database
can we please connect? I need help in one problem..please if you need this comment do connect once