September 30, 2018 at 3:56 am
I have a generic Sproc that I want to reuse. It returns a resulset which the Parent SProc needs to use / have access to and I want to safeguard against the column definitions, in that resulset, changing in future.
CREATE PROCEDURE dbo.MyGenericSP
@MyParam1 int
, @MyParam2 varchar(10)
AS
SET NOCOUNT ON
SELECT [MyCol1] = @MyParam1 * 2
, [MyCol2] = 'Param2=' + @MyParam2
/** In real life:
FROM dbo.MyTable
WHERE SomeCol1 = @MyParam1
AND SomeCol2 = @MyParam2
**/
GO
Now I want to store the results from that in my Application Sproc
CREATE PROCEDURE dbo.MyApplicationSP
@MyParam1 int
, @MyParam2 varchar(10)
AS
SET NOCOUNT ON
CREATE TABLE #TEMP
(
[MyCol1] int
, [MyCol2] varchar(100)
)
INSERT INTO #TEMP
EXEC dbo.MyGenericSP
@MyParam1 = @MyParam1
, @MyParam2 = @MyParam2
SELECT *
FROM #TEMP
GO
Test the Sproc:
EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'
MyCol1 MyCol2
----------- ----------
246 Param2=ABC
Now ALTER the Sproc and change the resultset:
ALTER PROCEDURE dbo.MyGenericSP
@MyParam1 int
, @MyParam2 varchar(10)
AS
SET NOCOUNT ON
SELECT [MyCol1] = @MyParam1 * 2
, [MyCol2] = 'Param2=' + @MyParam2
[highlight] , [MyNewCol3] = 'Added Col3'[/highlight]
/** In real life:
FROM dbo.MyTable
WHERE SomeCol1 = @MyParam1
AND SomeCol2 = @MyParam2
**/
GO
and the EXEC fails of course:
EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'
Server: Msg 213, Level 16, State 7, Procedure MyGenericSP, Line 7
Column name or number of supplied values does not match table definition.
There are a number of ways of solving this that I am aware of, I would be grateful for any other suggestions
One way is to use OPENROWSET, but I have not found a practical way to make use of that:
SELECT *
INTO #TEMP
FROM OPENROWSET('SQLNCLI'
, 'Server=(local);Trusted_Connection=yes;'
, 'SET FMTONLY OFF;
SET NOCOUNT ON;
EXEC MyDatabaseName.dbo.MyApplicationSP
@MyParam1 = 123
, @MyParam2 = ''ABC''
')
I am getting:
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'
of component 'Ad Hoc Distributed Queries' because this component is turned off
as part of the security configuration for this server.
A system administrator can enable the use of 'Ad Hoc Distributed Queries'
by using sp_configure.
For more information about enabling 'Ad Hoc Distributed Queries',
search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
Fair enough, and I can fix that, but it is not the answer because I do not want to hardwire MyDatabaseName, and I need dynamic parameters
The whole statement would have to be Dynamic SQL ... and of course the scope of
SELECT *
INTO #TEMP
would then only be local to the Dynamic SQL itself. Back to Square One 🙁
So I need a way to maintain the #TEMP definition in all the places in my code where it is used. Even that is a bit fraught, as any change needs a rollout of all effected SProcs, and that has QA testing / procedureal issues which delay rollot of a change.
Best solution that I have seen is to have a real, dummy, table (with no rows) that is the correct structure for the resultset
CREATE TABLE dbo.[highlight]TEMPLATE_MyGenericSP_01[/highlight] -- "_01" in case an Sproc needs more than one #TEMP table
(
[MyCol1] int
, [MyCol2] varchar(100)
, [MyNewCol3] varchar(100)
)
and I change my Application SProc to use that definition:
ALTER PROCEDURE dbo.MyApplicationSP
@MyParam1 int
, @MyParam2 varchar(10)
AS
SET NOCOUNT ON
[highlight] -- Create #TEMP from TEMPLATE_MyGenericSP_01
SELECT *
INTO #TEMP
FROM dbo.TEMPLATE_MyGenericSP_01
WHERE 1=0 -- Do not accidentally select any real rows![/highlight]
INSERT INTO #TEMP
EXEC dbo.MyGenericSP
@MyParam1 = @MyParam1
, @MyParam2 = @MyParam22
SELECT *
FROM #TEMP
GO
Re-test:
EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'
OK ... that works 🙂
MyCol1 MyCol2 MyNewCol3
------ ---------- ----------
246 Param2=ABC Added Col3
At that point I would probably also change MyGenericSP to dynamically create the TEMPLATE_MyGenericSP_01
Is this change going to make all MyGenericSP inefficient? or is the change in efficiency so small that it is worth it for the code-safety?
-- Whenever MyGenericSP script is run drop&recreate TEMPLATE_MyGenericSP_01 (in case it will have changed)
ALTER PROCEDURE dbo.MyGenericSP
@MyParam1 int
, @MyParam2 varchar(10)
AS
SET NOCOUNT ON
SELECT [MyCol1] = @MyParam1 * 2
, [MyCol2] = 'Param2=' + @MyParam2
, [MyNewCol3] = 'Added Col3'
[highlight] , [MyNewCol4] = 'Added Col4' -- Added another column, not already in the template
INTO #MyGenericSP_01 -- Added temporary table[/highlight]
/** In real life:
FROM dbo.MyTable
WHERE SomeCol1 = @MyParam1
AND SomeCol2 = @MyParam2
**/
[highlight] IF OBJECT_ID('dbo.TEMPLATE_MyGenericSP_01') IS NULL
BEGIN
-- Create template table (if not exists)
SELECT *
INTO dbo.TEMPLATE_MyGenericSP_01
FROM #MyGenericSP_01
WHERE 1=0 -- No rows!
END
-- Return the actual Result Set
SELECT *
FROM #MyGenericSP_01 -- Added temporary table[/highlight]
GO
--
-- Recreate TEMPLATE_MyGenericSP_01 in case changed
IF OBJECT_ID('dbo.TEMPLATE_MyGenericSP_01') IS NOT NULL
DROP TABLE dbo.TEMPLATE_MyGenericSP_01
-- EXEC procedure with dummy parameters to force create of XXX
EXEC dbo.MyGenericSP @MyParam1=NULL, @MyParam2=NULL
Re-test that the original MyApplicationSP still works, with the modified resulset from MyGenericSP
EXEC dbo.MyApplicationSP @MyParam1=123, @MyParam2='ABC'
MyCol1 MyCol2 MyNewCol3 [highlight]MyNewCol4[/highlight]
------ ---------- ---------- ----------
246 Param2=ABC Added Col3 [highlight]Added Col4[/highlight]
Tidy up:
DROP PROCEDURE dbo.MyGenericSP
GO
DROP PROCEDURE dbo.MyApplicationSP
GO
DROP TABLE dbo.TEMPLATE_MyGenericSP_01
GO
October 1, 2018 at 12:19 pm
Why not just use an inline table-valued function instead of creating a #temp table everywhere? If you absolutely must have a temp table - you could always select from the iTVF into your temp table (but I would not do that as it isn't necessary). Any changes to the function would automatically get picked up by other procedures using the function.
Accessing the iTVF in your procedures would depend on how you want to use it - and where you are getting the parameters. If the parameters are fixed - you can use the function just like any other table. If the parameters are coming from other tables to be queried - then use CROSS/OUTER APPLY.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2018 at 12:52 pm
Thanks. The real-world Stored Procedure includes a bunch of error handling and logging, which inline table-valued function wouldn't give me, but certainly worth a thought as to whether I could live without that.
My real-world Procedure won't even run with OPENROWSET (seems like it is too complex for OPENROWSET to parse, or somesuch, perhaps because of dependency on nested Procedures (for logging, rather than for actual computations)
October 1, 2018 at 1:44 pm
Kristen-173977 - Monday, October 1, 2018 12:52 PMThanks. The real-world Stored Procedure includes a bunch of error handling and logging, which inline table-valued function wouldn't give me, but certainly worth a thought as to whether I could live without that.My real-world Procedure won't even run with OPENROWSET (seems like it is too complex for OPENROWSET to parse, or somesuch, perhaps because of dependency on nested Procedures (for logging, rather than for actual computations)
I would have to see what error handling and logging are included in that procedure - generally if returning a table there really isn't any need to build in error handling and logging.
If that is required - you could utilize a multi-statement table valued function with the caveat that it would *never* be utilized directly in a join or apply. The only usage would be:
SELECT * INTO #temp FROM dbo.fnTVF(parm1, parm2) AS t;
Any changes to the function would automatically get picked up in the temp table and could then be utilized as you are now using the temp table. This assumes that the parameters for the function would be predefined or passed into the stored procedure. If you try to use this function in a join or apply you can expect poorer performance.
Again - not sure why you would need any error handling or logging for a function/procedure whose only purpose is to return a table to the caller.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 1, 2018 at 2:35 pm
If you create the #temp inside the store procedure it will be deleted as soon as the stored procedure exits. you would need to create #temp outside the stored procedure:
IF OBJECT_ID('tempdb..#TEMP','U') IS NOT NULL
DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(
[MyCol1] int
, [MyCol2] varchar(100)
)
GO
IF OBJECT_ID('dbo.MyGenericSP','P') IS NOT NULL
DROP PROCEDURE dbo.MyGenericSP
GO
CREATE PROCEDURE dbo.MyGenericSP
@MyParam1 int
, @MyParam2 varchar(10)
AS
SET NOCOUNT ON
INSERT INTO #TEMP([MyCol1],[MyCol2]) SELECT @MyParam1,@MyParam2
GO
EXEC dbo.MyGenericSP 3,'three'
EXEC dbo.MyGenericSP 4,'four'
SELECT * FROM #TEMP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply