I have created the sp myself, like a PP said, there is no handy and easy solution.

Thanks anyway.

Maybe this helps?

CREATE TABLE T1

(

ID int

Division varchar(20)

)

INSERT INTO T1 ( ID, Division ) VALUES ( 1, 'Division1' )

INSERT INTO T1 ( ID, Division ) VALUES (...

Thanks.

Is there any tricks I can create sample table quickly with sample data in this forum?

Lynn Pettis (5/17/2012)

---

Perhaps this:

set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' +...

That's not where is the problem, but thank you for your reply.

Thank you very much Lynn. I will try out your solution.

Tried this, also failed:

EXEC sp_executesql

@query = @sql,

@params = N'@subTotal VARCHAR OUTPUT',

@subTotal = @subTotal OUTPUT

Error:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement'...

Thanks Lynn,

I need to use the temp table for further data processing so defining it as global is the only way.

Big thanks, that's the reason

Lynn Pettis (5/16/2012)

---

The table is created, it is created within the context of the EXEC statement. When that returns, the temporary table is then deleted. You either need...

I have made the sp very simple to isolate the problem, here is the whole sp:

ALTER proc [dbo].[spDashboardByCategories1]

as

Declare @sqlCreateTable varchar(2000)

Set @sqlCreateTable = 'Create table #tFinal (DocID int, [Unmasked Production...

Thank you for your input, much appreciated

I figured it out, here is the little trick, I hope it's useful to others:

SELECT *

FROM OPENROWSET( 'SQLNCLI',

...

Thanks. Same error

This works for me though, but I don't know why the previous one is not working:

SELECT * INTO #Temp FROM

OPENROWSET(

'SQLNCLI',

'Server=localhost;Trusted_Connection=yes',

'EXEC msdb.dbo.sp_help_job')

SELECT * FROM #Temp

Drop table #Temp

Even after I...