INSERT EXEC without defining table structure 1

[woodpd]

This one's been bugging me all night.

OK, at present this is what I do.
I have a stored procedure (usp_ReportMaster) which returns the result of a dynamic SQL query like this:
SELECT Improvement.* FROM ..........

I then have many other stored procedures that run the above stored proc first and then run a second filter on the result (like this)

CREATE TABLE #ReportMaster (
[ImprovementID] [bigint] NOT NULL,
......
)

INSERT INTO #ReportMaster EXEC usp_ReportMaster @Filters, @Company, @User

SELECT ... FROM #ReportMaster WHERE .......

-----------------------

All well and good, but everytime the structure of the Improvement table changes, I have to change ALL the stored procedures' temp tables to reflect this - BIG HASSLE as there are loads of them.

I have been experimenting with SELECT INTO and OPENROWSET. I pretty much have the OPENROWSET method working (with static params at present, need to make them dynamic), but that introduces other aspects which may have to be changed every now and then, so isn't really satisfactory.

Therefore I have been looking at SELECT INTO to get the structure for me with the following code:

SELECT * INTO #ReportMaster FROM Improvement WHERE 0 = 1
INSERT INTO #ReportMaster EXEC usp_ReportMaster @Filters, @Company, @User

However, I get this error:
Insert Error: Column name or number of supplied values does not match table definition.

Which so doesn't make sense as the SELECT INTO query and the usp_ReportMaster both return the entire data structure (only) of the Improvement table and are identical as far as I can see (I have compared the resulting structure output from each statement).

I have also tried using table variables and UDFs and there is always some restriction that stops me doing what I want.

If anyone has any bright ideas, I will be very grateful

 

[Catadmin]

First thought: Do you really need to pull every last column from Improvement? If not, just list out the columns you need to pull, alter the temp tables accordingly, and then you won't keep getting errors when the schema changes.

Second thought: Try using information_Schema.Columns (which includes datatype details) & information_schema.tables to build your temp tables dynamically. It'll be a long coding hassle to start with, but if you can get it to work, you will save yourself from ever having to redo the temp tables again.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[JamesLean]

I would look at making usp_ReportMaster into a table-valued function. You can then use SELECT INTO without having to explicitly define the columns:

SELECT *
INTO #reportmaster
FROM fnReportMaster('foo', 'bar', 123)

--James

 

[Catadmin]

Hey! Now there's a thought!

Star for you James (and congrats on being TipMastered, BTW). I don't know why I keep trying to do things the hard way. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[woodpd]

First thought, yes I need every single column as the various filters and reports that use this system are varied and wide, using every single column in the Improvement table. As new columns are added, new reports and filters are often added too!

Second thought, I hoped no one would say that. Would be very painful for me as I don't really have much experience with system level stuff.

 

[woodpd]

I did look into that. Here is my attempt:

CREATE FUNCTION dbo.udf_ReportMaster (@Filters varchar(4000), @Company int, @User int)
RETURNS @ReportMaster TABLE ([ImprovementID] [bigint] NOT NULL, [MU_Employee_DepartmentID] [bigint] NOT NULL,
....... [ReminderDate] [smalldatetime] NULL) AS
BEGIN
-- Collect and deal with security options
DECLARE @Level tinyint, @ScopeDept bigint, @ScopeSite bigint
SELECT @Level = AccessLevel, @ScopeDept = DepartmentID, @ScopeSite = SiteID FROM Users WHERE UsersID = @User
IF @ScopeSite = NULL SELECT @ScopeSite = 0
IF @ScopeDept = NULL SELECT @ScopeDept = 0

-- Filter improvement table according to security options and other parameters
DECLARE @SQLstatement varchar(5000)
SELECT @SQLstatement = 'SELECT Improvement.* FROM Project RIGHT OUTER JOIN Committee RIGHT OUTER JOIN Improvement ON Committee.CommitteeID = Improvement.CommitteeID LEFT OUTER JOIN Risk ON Improvement.RiskID = Risk.RiskID ON Project.ProjectID = Improvement.ProjectID LEFT OUTER JOIN Site INNER JOIN Department INNER JOIN MU_Employee_Department ON Department.DepartmentID = MU_Employee_Department.DepartmentID INNER JOIN Employee ON MU_Employee_Department.EmployeeID = Employee.EmployeeID ON Site.SiteID = Department.SiteID ON Improvement.MU_Employee_DepartmentID = MU_Employee_Department.MU_Employee_DepartmentID WHERE Improvement.CompanyID = ' + CAST(@Company AS varchar(10)) + @Filters
IF (@Level BETWEEN 5 AND 10) AND @Level <> 8 SELECT @SQLstatement = @SQLstatement + ' AND (Department.Private = 0 OR Department.Private IS NULL) '
IF @Level BETWEEN 5 AND 7 SELECT @SQLstatement = @SQLstatement + ' AND (Department.SiteID = ' + CAST(@ScopeSite AS varchar(20)) + ')'
IF @Level BETWEEN 8 AND 10 SELECT @SQLstatement = @SQLstatement + ' AND (MU_Employee_Department.DepartmentID = ' + CAST(@ScopeDept AS varchar(20)) + ')'

-- INSERT INTO @ReportMaster EXEC (@SQLstatement)
RETURN
END

I've trimmed this a little to make it easier to read. As you can see the problem is that I HAVE to use dynamic SQL and that won't work with the INSERT EXEC

 

[vongrunt]

Why function?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[woodpd]

vongrunt: Trying other options apart from the Stored Procedure method mentioned at the top which won't work.