+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 ruclips.net/user/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 ruclips.net/video/y780MwhY70s/видео.html To receive email alerts, when new videos are uploaded, please subscribe to our channel ruclips.net/user/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
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.
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.
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
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 😂
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?
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.
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...................
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?
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.
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.
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.
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.
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 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.
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
ruclips.net/user/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
ruclips.net/video/y780MwhY70s/видео.html
To receive email alerts, when new videos are uploaded, please subscribe to our channel
ruclips.net/user/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!
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.
Your indian accent is so cool.
Thank you for give this knowledge for free. Greetings from Brazil.
You have been improving my coding since 2015, from C# to MVC to SQL
I will recommend these videos to every SQL Server student. Thanks a lot Venkat.
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!!
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.
another clear and concise explanation. thanks for this.
Your videos are just amazing. THANK YOU VERY MUCH SIR.
This is so incredibly clear and helpful. Thank you so much!
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.
Thank you for the video tutorials. You're wonderful.
12 years later, and still benefiting from his lessons immensely. Thanks a lot Sir.!
Very helpful videos for beginners, Thank you very much
very help full stuff, great job
Watching you in 2022. Your teaching style is very easy to grasp. God bless!
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.
I love your session so much. Thanks for help me
Great session...very informative and complete
thank you very much sir,very good explanation
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
thanks Venkat... really very very post..
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 sir.. U r way teaching superb sir...
You are exceptional!
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 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?
Tony the Tigerrrr: "This is Grrrreat!"
nice explanation thanks venkat
I really like your way of teaching.....Can you please provide a SAS tutorial basic to advanced??
thanks for help.....I learn something ..:D
you are the best bro thank you
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;
just excellent sir
SQUARE, BRING ME MY SWORD.
Thanks
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.
Great, thanx
Thank you, sir.
Awesom Video, Please tell that is there any performance issue with functions in comparison to procedures?
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...................
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.
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.
very helpful
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.
I guess my real question is, when should you use output parameters vs functions?
Why we are using .dbo as prefix in scalar user defined function? What is the reason behind this?
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
Happy birthday !
Thanx
How do I calculate age in year and month and date format?
what is the difference between functions and stored procedure.
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.
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
great!!!!!!!!!!!!!!1
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 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.
Hi Sir, I would like to take Microsoft 70-761 Exam. Just advise me how to pass the exam.
Can we give two parameters to a function?
yes
You can give upto 1024.
Sir can u upload SSRS and SSIS pls
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
Please share sample database
can we please connect? I need help in one problem..please if you need this comment do connect once
instead of case statement:
declare @dob date
declare @age int
set @dob = '2021-08-28'
select datediff(month,@dob,getdate())/12