Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Select statements in SQL Server Stored Procedures (Read 10461 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Select statements in SQL Server Stored Procedures
28.11.05 at 13:48:55
 
Stored procedures are used widely in SQL Server. To get output from stored procedures parameters need to be used (see this topic how this works with SQL*XL). With SQL Server select statements can be used in the stored procedure to generate output as well. Functionality to SQL*XL was added to display these recordsets.
 
 
For example the sp_databases stored procedure can be used in SQL Server to display a list of databases with some properties. When you type sp_databases in the SQL dialog in SQL*XL the stored procedure will be executed and the data will be put through the same procedure as a select statement.
 
You can also make stored procedures yourself that return data. The following SampleData procedure will output three queries with the first 10 rows of tables Authors, Publishers and Titles.
Code:
CREATE PROCEDURE [dbo].[SampleData]
AS
BEGIN
	SELECT top 10 * from Authors;
	select top 10 * from Publishers;
	select top 10 * from Titles;
END
 


 
You can simply execute the stored procedure in SQL*XL's SQL dialog.
 
The new functionality is available in SQL*XL 4 Universal 4.0.29. See the release log for further details:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130772637
Back to top
 
« Last Edit: 27.03.06 at 10:45:11 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Support for Select statements in Stored Proced
Reply #1 - 07.12.05 at 14:13:54
 
Al from the USA sent me the following remarks:
 
Remark:
I started playing and found using the stored procedure query works very fast.  
I have a question about saving an excel spreadsheet that has the query that I want to use and be able to open it and run for a different day for example.  
 
Can I use cells to put in a date and use it in the query?  
 
What I want to do is have spreadsheet that the operator can open and enter a date range that is fed to a stored procedure that will update the cells. This is a stored procedure that worked:  
 
sp_getInfoOnDate "9/29/2005"
 
I'd like to use a cell instead. Of course the old data would need to be deleted before the update or the spreadsheet would be empty to start with. Thanks.
 
Reply
Thank you for the compliment that it works fast.
 
You can use a cell as parameter by using the bind variable syntax. To use cell D12 for instance you would use:
sp_getInfoOnDate :D12
 
Now how do you rerun the statement? When you execute the stored procedure, any resulting resultset will be added to the spreadsheet with in the SQL note a comment with the stored procedure call. This can be a bit of a problem I guess. I cannot put the SQL in the note that led to the results as I don't have that SQL statement available. I only know that the stored procedure gives output and cannot see which statements generated the output. Secondly I cannot use the stored procedure callin the SQL text. Suppose there are 3 resultsets resulting from your stored procedure. It would lead to 3 notes with the same stored procedure call. Refreshing the queries will lead to 9 resultsets! because you run the stored procedure three times.
 
What I suggest you do is this. Record a macro while running the stored procedure. This should result in a macro your end user can use to run the process. Simpel and effective. You should not need to change anything in the macro code as it does 100% what you want to achieve.
Back to top
 
« Last Edit: 07.12.05 at 14:17:06 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Support for Select statements in Stored Proced
Reply #2 - 09.12.05 at 08:44:38
 
Al from the USA replied:
 
I was wondering if it is possible to set up an excel file that the operator would just have to enter the date he wanted data for and hit the refresh to update the data. Do you by chance have a demo of this.
 
Answer:
I don't have a demo ready for this precise scenario but it is really easy to achieve. I have put a recipe below how to make this little utility.
  • Open a new Excel file and save it as e.g. demo.xls
  • In sheet1 make a cell where you can enter the date variable. Format it as a date.
  • Now draw a button on sheet1. For notes how to do this see:
    http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133092522
  • Change the caption of the button to Update, you can change the name too to e.g. cmdUpdate
  • Start the macro recorder, store the macro in the current workbook and name the macro Update
  • Go to Sheet2 and select all cells. Clear the contents of the cells.
  • Return to Sheet1
  • Use SQL*XL to connect to the database
  • Run your stored procedure picking up the date from the cell. E.g.:
    sp_MyStoredProc :D12
  • Choose to put the results in Sheet2
  • Disconnect from the database
  • Stop the macro recorder
  • Now you need to attach the macro to the button. Again, see the following page for notes how to do this:
    http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133092522

 
All in all it should not take any longer than 10 minutes. If you are familiar with buttons this is all setup in a few minutes.
 
References:
Back to top
 
« Last Edit: 09.12.05 at 08:48:25 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1