Call a SQL Server Stored Procedure using Excel Parameters

Sometimes we need to dynamically transfer data from SQL Server into Excel without the manual cutting and pasting. By the end of this video, you'll know how to use sp_executesql and parameters to pass data between Excel and SQL Server. Unlike the first video where I demonstrated using a raw SQL query, parameterized queries can improve query performance by avoiding the need for the database engine to compile the same query multiple times. Additionally, sp_executesql is more secure, as it helps prevent SQL injection attacks.
Watch Part 1 for worksheet details and for the quick way to retrieve data: • Passing Parameter Valu...
★☆★ Join this channel to get access to perks: ★☆★
/ @anthonysmoak
#SQL #analytics #tsql #excel #dataanalyst #data
Download the Database used in the video: learn.microsof...
★☆★ FOLLOW ME BELOW: ★☆★
Blog ► anthonysmoak.com/
Instagram ► / @anthonysmoakdata
Facebook ► / anthonybsmoak
Tableau Public ► bit.ly/3JMKsLY

[@AnthonySmoak]

Become a member and you can do this the easy way without code (and support the channel)! th-cam.com/video/2oT-PhgcKa8/w-d-xo.html

[@abdullahquhtani4247]

Thank you very much. 👍🏼

[@AnthonySmoak]

Much appreciated!

[@justuju]

Hey Anthony nice video. What do I need to do if instead of details I want to do pivot table representation? meaning I want to do all this, but then data needs to be transformed to a pivot table without user interaction.

[@AnthonySmoak]

Thanks. You can repeat the process on a table that has been "pre-pivoted" in SQL Server or most likely you will need some sort of VBA code to automate the pivot. Play around with the macro recorder and observe what code it develops when you create a pivot table.

[@Faisal1504]

Very nice.

[@oussamakherroub1103]

Thank you for this video it works perfectly, but i have the error 438 when we run this macro in an other PC

[@HerbertWoelffer]

Followed everything. Runs great on mine but some other users get runtime error 1004 on the refresh line of the macro. Thoughts?

[@AnthonySmoak]

Double check the cell references.

[@terryzimmerman8795]

I spent 2 days trying to resolve this issue. It is a common issue people have and I'm not sure why. To fix, just replace the refresh line with: ActiveWorkbook.RefreshAll

[@NyphronRising]

i find its because the Refresh method isnt available to me for some reason

[@devendrateluguchannel]

how to create stored procedure please give me a video step by step .....

[@AnthonySmoak]

I literally show the stored procedure code I use in the video. 😊

[@mnvsaikiran]

Very nice but the video is fast. Please see if you can make it go slow.

[@AnthonySmoak]

Adjust your playback speed in the settings.

[@emmanuel.aggrey]

thanks for the video, please can share this file with us so we can edit it according to our need or share the code you pasted. I will be much greafull thank you sir

[@jaygibbs7218]

I cant get my button to execute I click it and nothing I tried this 3 times and I dont see anything wrong

[@AnthonySmoak]

@@jaygibbs7218 I assume you have design mode turned off and your code is placed in the on click action?

[@jdwyer4851]

how is this in anyway better than MSQuery where i could just do {Call MyStoredProcedure(?,?,?)}. Not a bit of VBA or a macro, i could just assign the 3 parameters to cells on the sheet.

[@AnthonySmoak]

I know MSQuery is listed as an older legacy option and you have to manually enable it in the latest version of Office 365 Excel (will it continue to be supported like VBA)? However, you have given me an option to test, I'll let you know how it goes.

[@jdwyer4851]

@@AnthonySmoak My complaint is really with Microsoft. They took something simple and replaced it with a nightmare. I'm a programmer so other than disliking VBA, i have no problem with this approach other than it used to be very simple with MSQuery.

[@AnthonySmoak]

@@jdwyer4851 I made a video exploring the MS Query approach. Thanks for the comment. th-cam.com/video/2oT-PhgcKa8/w-d-xo.htmlfeature=shared

[@jdwyer4851]

@@AnthonySmoak Thank you for doing that. Microsoft has buried MSQuery on the current Excel version and you have to add it to the menu yourself. I hope that it doesn't go away completely, or at least if it does Microsoft could replace it something that its as equally simple. I'm a programmer, but for fast ad-hoc solutions I can turnout an Excel doc calling a stored procedure in minutes. Perhaps the more people who know about and use MSQuery the less likely it gets killed by Microsoft.