SQL Server – Executing Dynamic SQL Generated from SELECT Statement

dynamic-sqlsql serversql-server-2012t-sql

A business process in SQL can generate a variable to each operation 1-N number of calls to a stored procedure whose purpose is to insert data related to the main process.

How does one execute multiple rows of generated sql from a select or is there a way of calling a stored procedure from a select?

The process before this step is an involved operation of business logic and data driven operations and has done subsequent inserts to related FK joined tables.

At that point where it needs to call a stored procedure it has data ready to be processed in a table variable. The step (logic inside the proc) calling the stored proc is an involved process that makes sense to be in a stored proc and cannot be copied to the existing proc.

Faux example of generated code generated by Select.

SELECT 'EXEC [sim].[ROI_Save] ' + CONVERT(varchar(6), VSL.PathId)           + ', '
                                + Convert(varchar(6), @ROIResultId)         + ', '
                                + CONVERT(varchar(6), RT.routeTypeResultId) + ', '
                                + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + '; '
FROM  @routeResultIds RT
JOIN sim.ROI_Result_Vehicle VSL ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId

Results

Does one concatenate the results and call Exec?

Best Answer

While I would prefer to generate true dynamic SQL statements with strongly-typed parameters, concatenation is probably ok here because the proper way will be a lot more cumbersome. I do, however, prefer sp_executesql over EXEC(), always.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'EXEC [sim].[ROI_Save] ' 
        + CONVERT(varchar(6), VSL.PathId)           + N', '
        + CONVERT(varchar(6), @ROIResultId)         + N', '
        + CONVERT(varchar(6), RT.routeTypeResultId) + N', '
        + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + N'; '
FROM  @routeResultIds AS RT
JOIN sim.ROI_Result_Vehicle AS VSL 
ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId;

EXEC sys.sp_executesql @sql;

I still don't like that the parameters to the procedure aren't named, and if any of your parameter values were strings or dates, the build-up of single quotes would quickly get very annoying.

Related Solutions

Sql-server – How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data

I do not think that your requirements are complete as there are reasons that your prior attempts did not work that are not reflected in the stated Requirements. You mentioned in attempted solution #1 that you need to be able to debug the code / know what version it is, and the client needs to be able to replicate the code. Are these two issues current requirements?

If the need for replication is still a requirement, then that conflicts with the requirement to be "compiled (secured)".

I will assume that the client's server is something they have full sa access to such that DENYing the VIEW ANY DEFINITION permission is not a viable approach.

One thing to try is taking the T-SQL of your Stored Procedures and encapsulating each one, unchanged, into CLR Stored Procedures. You would place one or more related Stored Procedures into an Assembly. Here are some thoughts about this:

Assemblies conveniently have a version number that can be seen in the sys.assemblies catalog view, SSMS (right-click on the Assembly to view the Properties), and the ASSEMBLYPROPERTY function.
You probably don't want to put all of the Stored Procedures into a single Assembly, but you also don't want to have one Assembly per Stored Procedure. You can have one class file per Stored Procedure and several of those included in a single Assembly.
All of the Assemblies should use WITH PERMISSION_SET = SAFE
The only code in each CLR Stored Procedure would be:
- Create a SqlCommand with a CommandType of CommandType.Text and CommandText set to the current T-SQL Stored Procedure content
- No SqlConnection should be needed (if it is, then it is just "context connection=true")
- Map the SqlParameterCollection of the SqlCommand to the input parameters of the CLR Stored Procedure
- Call SqlContext.Pipe.ExecuteAndSend(_YourSqlCommand);
The Assembly and related Stored Procedures would be loaded into each Client DB
CLR Stored Procedures are not able to be replicated, but that could probably be worked around

SQL Server – Using CTE to Select Alternate When No Rows Returned

Here is the way I would do this, assuming the "do it all in one query" isn't just a pointless, artificial requirement.

SELECT cols INTO #x FROM dbo.Users WHERE UserID > 5;

IF @@ROWCOUNT > 0
  SELECT cols FROM #x;
ELSE
  SELECT cols FROM dbo.Users;

If the "do it all in one query" is a hard requirement and you can't use a #temp table, or you really have complex logic you've dumbed down from us and/or don't want to repeat, then you're going to have to pay for those choices in performance, I'm afraid. This looks like it should be quite efficient, but there are going to be more hits to the base table than most people would hope / expect:

;WITH TargetUsers AS 
(
  SELECT cols FROM dbo.Users WHERE UserID > 5
),
AllUsers AS 
(
  SELECT cols FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT cols FROM TargetUsers 
  UNION ALL 
  SELECT cols FROM AllUsers
)
...;

In fact if UserID is the primary key and the filter really is on UserID, this might be better as follows (but you'll have to check the plans to see, depends on a variety of factors):

;WITH TargetUserIDs AS 
(
  SELECT UserID FROM dbo.Users WHERE UserID > 5
),
AllUserIDs AS 
(
  SELECT UserID FROM dbo.Users
  WHERE NOT EXISTS (SELECT 1 FROM TargetUsers)
    -- this will only return rows if the first CTE is empty
),
ComputeWithUsers AS
(
  SELECT UserID FROM TargetUserIDs 
  UNION ALL 
  SELECT UserID FROM AllUserIDs
)
...
-- and join to dbo.Users to get other columns necessary
-- deferring that to as late as possible

Hopefully your queries are using suboptimal practices due to simplification, and not like that in production, but please give these a read:

Best Answer

Related Solutions

Sql-server – How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data

SQL Server – Using CTE to Select Alternate When No Rows Returned

Related Question