Excel with Stored Procedures

Question

Post reply

Excel with Stored Procedures

Viewing 15 posts - 106 through 120 (of 120 total)

You must be logged in to reply to this topic. Login to reply

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 1

Ron

Do you ask Stephen and me?

As Stephen says it depends.

Dim param1 etc as string or as date dependant on what content to be expected in the cell.

Look at the format in Excel.

//Gosta

LadyReader Ten Centuries Points: 1192 More actions · Answer 2

LadyReader

Ten Centuries

Points: 1192

October 10, 2009 at 10:23 am

#1064473

For my purposes, all params will be strings.

Stephen Byrne SSC Enthusiast Points: 156 More actions · Answer 3

Hi Gosta,

well spotted on the errors in the code, I agree the connection should just be opened once.

@Ron, the Param1, 2, etc would in that code be variants so as to be able to handle any type of data since I didn't know what type of information the cells would hold. Obviously they'd need to be quoted or formatted correctly if they were strings or dates, etc, when constructing the query (or use ADODB.Parameter objects if you like which will take care of it for you!)

LadyReader, did you get a chance to try it out?

Michael Lysons SSCertifiable Points: 6491 More actions · Answer 4

Much joy. Really, you've no idea how much. It's like an epiphany.

Thanks, David; you rule!

kadirozgul80 Newbie Points: 1 More actions · Answer 5

hi,

"excel with stored procedures" that opens the title of friend, thank you. I do not know English very well may have problems that I'm sorry for my expression. The topics discussed in this topic useful and easy. But foreigners like me (with little ability to speak and understand English) users will be happy if you add videos to my issues. Thanks again. I have written a Turkish follows. Bye.

merhaba,

"excel with stored procedures" basligini açan arkadasa tesekkür ederim. Ingilizceyi çok iyi bilmedigim için anlatimim da sorunlar olabilir özür dilerim. Bu baslikta anlatilan konu oldukça kullanisli ve kolay. Ama benim gibi yabanci (ingilizce konusma ve anlama kabiliyeti az olan) kullanicilar için konularla ilgili videolar eklerseniz mutlu olacagim. Tekrar tesekkür ederim. Yazdiklarimin bir de Türkçesi asagidadir. Hosçakalin.

ricardo.ferreira Newbie Points: 5 More actions · Answer 6

ricardo.ferreira

Newbie

Points: 5

August 13, 2010 at 3:25 am

#1206481

Man, you should try this on Excel 2010...

leea SSC Enthusiast Points: 186 More actions · Answer 7

Warning - Accessing a SQL Server DB in this way from excel can be prone to causing blocks, maybe not as bad as from Access but something to watch out for!!!

brichardson Grasshopper Points: 14 More actions · Answer 8

I echo the idea that {Call <some stored proc(?,?)} works great for parameters. I have numerous sheets that use this idea to produce sophisticated interactive reports for users. They love it.

As to why I would use stored procs over views--answer is mostly about performance and efficiency. There are times when a parameterized stored proc can return needed data a lot more efficiently than with a view. (For example, views that involve linked servers.) Also, security is better with a stored proc--the user has access to precisely what the stored proc returns, not other columns or rows that may be available in a somewhat generic view. I realize that the security aspect is relative--specific views can limit access as well, but stored procs offer greater flexibility.

Stephen Hirsch SSCommitted Points: 1822 More actions · Answer 9

Why not just create a view in the database?

Assuming, of course, that a single SELECT statement can do the job.

Old SQL Newbie Ten Centuries Points: 1060 More actions · Answer 10

To the poster who mentioned Excel 2010...please share your observations. We're about to move up to this "because it's there". :/

Richard Ray-222778 SSC-Addicted Points: 491 More actions · Answer 11

PowerPivot is a free add in for Excel 2010. For those doing data analysis out of SQL databases or SSAS it's a game changer. The ability to handle datasets with millions of rows, to create linked tables from Excel to use in joins and the replacement of the vlookup to avoid huge amounts of recalculation all make it worthwhile.

In general, as much as I grimaced over the ribbon interface I realized that it was only because I was one of the few nerds who had actually figured out where all the cool stuff was buried many layers down in menus, submenus and dialogs. The ribbon interface actually *does* help users I work with get at some of the more arcane functions and capabilities of the sprawling program Excel has become.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

Excel is the analytical tool of choice for analysts everywhere. Anything we can do to help them use the tools they are familiar with is in everyone's best interest. The newer versions of Excel make this even easier whether you are looking at a view, SP, or a cube. Nice article, thanks.

rhouben Newbie Points: 5 More actions · Answer 13

Did you try putting the parameter marker in the full format of the stored procedure escape clause?

{Call uspGetEmployeeManagers (?) }

sjb500-529772 SSC Rookie Points: 34 More actions · Answer 14

I tried various combinations of the above with one of our stored procs but each time I get an error message 'Invalid parameter number'. Any one got any ideas on how to resolve? Thanks

sgleeson SSC Rookie Points: 49 More actions · Answer 15

If a procedure takes for example 3 parameters, then {CALL uspThreeParm (?,?,?)}

The procedure might look like this :

ALTER PROCEDURE uspThreeParm ( Parm1 int , Parm2 int , Parm3 datetime )

AS

....etc

WHERE int is the datatype of the parameters 1 and 3, datetime is for the 3rd one.

So the parametes will have to be supplied by entry via dialog boxes or by reference to cell locations in the sheet

Hope this helps