Call a SQL Server Stored Procedure using Excel Parameters

No video

Call a SQL Server Stored Procedure using Excel Parameters

Рет қаралды 16,529

Күн бұрын

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

Пікірлер: 23

@abdullahquhtani4247 Жыл бұрын

Thank you very much. 👍🏼

@AnthonySmoak Жыл бұрын

Much appreciated!

@Faisal1504 7 ай бұрын

Very nice.

@mnvsaikiran 25 күн бұрын

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

@AnthonySmoak 25 күн бұрын

Adjust your playback speed in the settings.

@justuju 10 ай бұрын

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 10 ай бұрын

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.

@oussamakherroub1103 Жыл бұрын

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

@jaygibbs7218 26 күн бұрын

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

@AnthonySmoak 26 күн бұрын

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

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

@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 11 ай бұрын

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

@jdwyer4851 2 ай бұрын

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 2 ай бұрын

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 2 ай бұрын

@@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. kzfaq.info/get/bejne/aNWEYLOZzMmvkWw.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.

@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