0

I have not found a way to do this. I have a large SQL statement that does the following:

  1. checks for two temp tables, if found deletes them
  2. Declares local variables
  3. Sets local variables
  4. runs SQL statement1 which inserts into #tmp1 (temp table)
  5. runs SQL statement2 which inserts into #tmp2 (temp table)
  6. runs SQL statement3 which selects from #tmp1 and #temp2 (temp table) and returns pivot results

Running this in a query window works fine but I'd like to make one function (or something) to do the same.

EDIT1: I seem to be able to call a table function from excel and return those results but I can't call a stored proc from excel, I get an error. The problem with the table function is that I can't delete (drop) tables.

Baub
  • 723
  • 4
  • 21
  • 36
  • 3
    What problem do you have when you put all of this in a stored procedure? It should work just fine. Maybe the problem is terminology. You want a "stored procedure", not a "function". – David Sep 17 '13 at 20:49
  • I didn't think I could return a table, thought I had to use a table function. Would I be able to call this from Excel? – Baub Sep 17 '13 at 20:59
  • Steps 4 through 6 can likely be condensed to a single SELECT that avoids the use of temp tables, but there's not enough info here for us to tell you what that query might look like – Joel Coehoorn Sep 17 '13 at 21:07
  • Can't you call an `EXEC sproc` from Excel? Take a look here: http://stackoverflow.com/questions/12693967/table-in-excel-from-sql-server-stored-procedure-with-parameter-field-in-workbook – Rubens Farias Sep 17 '13 at 21:10
  • Rubens - it seems following those instructions I can call a stored proc from Excel. I was using a slightly different method that wasn't working. Thanks. – Baub Sep 17 '13 at 21:32

1 Answers1

1
CREATE PROCEDURE dbo.OneFunction AS

--Checks for two temp tables, if found delete them
IF OBJECT_ID('#Frob') IS NOT NULL  
    DROP #Frob
IF OBJECT_ID('#Grob') IS NOT NULL
    DROP #Grob

--Declare local variables
DECLARE @some int;
DECLARE @localVariables nvarchar(max);

--Sets local variables
SET @some = 69;
SET @localVariables = N'Tĥḗ qᴜįċᴋ ᶀʀᴏᴡɴ ƒᴏx ᴊᴜᴍᴘěɖ ᴏᴠḝʀ'  

--runs SQL statement1 which inserts into #tmp1 (temp table)
INSERT INTO #tep1
SELECT 'Hi'

--runs SQL statement2 which inserts into #tmp2 (temp table)
INSERT INTO #tmp2
SELECT 'Low'

--runs SQL statement3 which selects from #tmp1 and #temp2 (temp table) and returns pivot results
SELECT *
FROM #tmp1
   FULL OUTER JOIN #tmp2 ON 1=1
PIVOT BY SOMEONE WHO KNOWS THE INSANE PIVOT SYNTAX

Note: Any code is released into the public domain. No attribution required.

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219