Scalar user defined functions in sql server Part 30

[@wasimbader9170]

THANK YOU SIR, I HOPE U DO FULL TUT ON SAS/SPSS OR DATA ANALYSIS WITH FEW PROGRAMS

[@Csharp-video-tutorialsBlogspot]

+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 kzbin.infoplaylists?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 kzbin.info/www/bejne/r2ibYYCtnb5qZtU To receive email alerts, when new videos are uploaded, please subscribe to our channel kzbin.info 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

[@ouruzbekhomeschool1334]

11 years later, and yet still benefiting from his lessons immensely. Thank you a lot!

[@gawiga]

Your indian accent is so cool. Thank you for give this knowledge for free. Greetings from Brazil.

[@anuragvashishtha6660]

I will recommend these videos to every SQL Server student. Thanks a lot Venkat.

[@lizravenwood5317]

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.

[@mdasadujjaman5342]

12 years later, and still benefiting from his lessons immensely. Thanks a lot Sir.!

[@amantlemashele5719]

You have been improving my coding since 2015, from C# to MVC to SQL

[@tksafi6075]

thanks man your sql videos are the best on youtube

[@devikasiddani]

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.

[@QuietLumina]

This is so incredibly clear and helpful. Thank you so much!

[@vaniagarwal5800]

Your videos are just amazing. THANK YOU VERY MUCH SIR.

[@yamaya5136]

Watching you in 2022. Your teaching style is very easy to grasp. God bless!

[@ishwakv1171]

Thank you sir.. U r way teaching superb sir...

[@rgarayia]

Hi Venkat, Want to thank you for your wonderful videos. They are simply great!!

[@mdfaysal8233]

Thank you Sir for a nice tutorial. All of your tutorials are so much easy to understand for every level of learners.

[@maheshgore1066]

Great session...very informative and complete

[@Csharp-video-tutorialsBlogspot]

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.

[@naodagere8210]

You are exceptional!

[@prabdeepdhaliwal9725]

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 😂

[@girldreams878]

😂

[@nerellapydiraju2320]

Very helpful videos for beginners, Thank you very much

[@MO-er7xp]

you are the best bro thank you

[@hongshen1702]

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.

[@dbin805]

another clear and concise explanation. thanks for this.

[@gauranshbhutani592]

I really like your way of teaching.....Can you please provide a SAS tutorial basic to advanced??

[@RPTech-ic2ed]

very help full stuff, great job

[@AdrianFusco]

Thank you for the video tutorials. You're wonderful.

[@deepro__]

Thank you, sir.

[@kaushikbhadani]

thanks Venkat... really very very post..

[@daliladali1268]

thank you very much sir,very good explanation

[@brahmanathtripathi2213]

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

[@DriveWithMunna]

just excellent sir

[@evvavinash]

+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?

[@saurabhs4743]

+sai avinash edara the function can be used as a new column while stored procedure wont do that

[@SuperGojeto]

+sai avinash edara if you watched his videos carefully then you will find out he has explained the difference.

[@danielofosuasare7757]

good work done, very useful

[@pucca9090]

I love your session so much. Thanks for help me

[@gracechan3039]

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.

[@abhay6276]

Seriously 😂😂😂

[@sarathchandhra1284]

nice explanation thanks venkat

[@Superman-hr4xl]

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?

[@artokilponen6989]

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.

[@amitkishor7338]

make a video for different between stored procedure and function it a very important question in an interview

[@artokilponen6989]

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.

[@tarikzaman9096]

Happy birthday !

[@RudraPratapSinghLearning]

Awesom Video, Please tell that is there any performance issue with functions in comparison to procedures?

[@Idhikapabi]

Thanku sir... your are really great teacher.I want to meet you Sir & say many many thnx^^.... Sir please upload AJAX tutorials....

[@Nadeeraasanka]

thanks for help.....I learn something ..:D

[@omotayoogunnika6865]

Thanks for this tutorial. Can you please explain the logic behind subtracting 1 from DATEDIFF(YEAR, DOB, GETDATE)) ) when the conditions are met. Thanks..

[@xswooshx]

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?

[@neo77jb]

Great, thanx

[@KiranNadukula]

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...................

[@ahsansiddiqui4263]

Tony the Tigerrrr: "This is Grrrreat!"

[@freddylouza7874]

very helpful

[@nandadk314]

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;

[@rdg515]

I guess my real question is, when should you use output parameters vs functions?

[@Amritha8]

Hi Venkat - Can you please write a function to find the number of Sundays in a month when you pass a date value?

[@zackziss]

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)

[@devexpost8508]

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.

[@krismaly6300]

Thanks

[@adityasamanta8621]

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.

[@hellofriend2012]

I think you cannot use RAND() function in user-defined functions

[@archrodney]

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

[@santhoshzany1451]

I'm not able to run this function. Can not find column or user defined function or aggregate or name ambiguous. Please answer

[@soniasadeque5950]

can you give me the name of a good SQL book for databse testing? thanks for your videos Sir

[@honzekpavlicek4044]

Nice video and explanation is great. Thx...but you can get example not about data or date of birth...:-)

[@shikhilbhalla969]

Why we are using .dbo as prefix in scalar user defined function? What is the reason behind this?

[@EmpoweringPerson]

Thanx

[@variniaquisbert4212]

Why do you use "dbo." before a function's name? What's dbo? Is a database name?

[@tanaygupta5133]

DBO(database owner) full form. It is a schema name usually we use before table name or fumctions.

[@kinjalthumar814]

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

[@The_Alco]

SQUARE, BRING ME MY SWORD.

[@aamirali8114]

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.

[@shashankpulijala3378]

Can we give two parameters to a function?

[@omaraprham2025]

yes

[@gshrdy5415]

You can give upto 1024.

[@saniyasaleem6434]

Sir can u upload SSRS and SSIS pls

[@santoshdachepalli]

great!!!!!!!!!!!!!!1

[@mshahzeb8926]

what is the difference between functions and stored procedure.

[@rdg515]

Couldn't you have used spCalculateAge if you had created an output parameter?

[@Kookie_ur_friend]

Hi Sir, I would like to take Microsoft 70-761 Exam. Just advise me how to pass the exam.

[@zhoou1201]

15:52

[@dattatrayarathi1014]

How do I calculate age in year and month and date format?

[@sudhapandian6577]

I GOT ERROR // Conversion failed when converting date and/or time from character string.///

[@anthonwellsjo]

me too!

[@souravkundu7051]

😊

[@coding3438]

instead of case statement: declare @dob date declare @age int set @dob = '2021-08-28' select datediff(month,@dob,getdate())/12

[@stutitehri993]

good videos,however, fonts for sql server appears to be small;hence less viewable

[@penchalababuallam2717]

Please share sample database

[@Creator07-sx2gl]

can we please connect? I need help in one problem..please if you need this comment do connect once

[@c-jay8500]