Variable SQL Cluase in Stored procedure

Question

Hello

I’m constructing a SQL string in a stored procedure (see below). The value of the input parameter (@pClass) determines what fields are filtered in the WHERE clause.
I want to use the procedure in SSRS, but when I create the data set , there are no fields showing, so I’m dumping the data into a test table.

Is there a way to get the fields out without using the test table?

Thanks in advance
Ron

@pClass VARCHAR(120),

AS
DROP TABLE IF EXISTS dbo.tblTest

SET @alenzi = ' SELECT * into dbo.tblTest From

(Variable SQL clause in

SELECT * FROM Student l
INNER JOIN dbo.StudentDetails f ON l.StudentKey=f.StudentKey
INNER JOIN [dbo].[StudentStatus] ps ON ps.StudentKey=f.StudentKey
INNER JOIN dbo.Date d ON d.DateKey=f.StartDateKey
INNER JOIN dbo.StudentAccount a ON a.AccountKey=f.AccountKey
WHERE a.StudentAccLevel= '''+@pClass +''''

SET @alenzi =@alenzi + Case @pClass
WHEN ABC THEN ' AND d.Fied1Z=1 '
WHEN'DEF' THEN ' AND ((d.FieldB=1) OR (d.FieldC=1)) '
ELSE ' AND ((d.PreviousCompleteMonth=1) OR (d.Previous2ndCompleteMonth=1)) '
END

SET @alenzi =@alenzi +' ) As a'

EXECUTE sp_executesql @alenzi

SELECT * FROM dbo.tblTest

Answer 1

Hi @ron barlow ,

After checking your statement, I found that there is not necessary to create a test table in your procedure.

You could refer below and check whether it is helpful to you:

CREATE PROCEDURE USP_PROCEDURE  --your procedure name  
@pClass VARCHAR(120)  
AS  
BEGIN  
DECLARE @SQL NVARCHAR(MAX)  
  
IF @pClass= 'ABC'   
SET @SQL=' AND d.Fied1Z=1 '  
ELSE IF @pClass= 'DEF'  
SET @SQL=' AND ((d.FieldB=1) OR (d.FieldC=1))'  
ELSE  
SET @SQL='AND ((d.PreviousCompleteMonth=1) OR (d.Previous2ndCompleteMonth=1))'  
  
SET @SQL = '   
SELECT * FROM Student l  
INNER JOIN dbo.StudentDetails f ON l.StudentKey=f.StudentKey  
INNER JOIN [dbo].[StudentStatus] ps ON ps.StudentKey=f.StudentKey  
INNER JOIN dbo.Date d ON d.DateKey=f.StartDateKey  
INNER JOIN dbo.StudentAccount a ON a.AccountKey=f.AccountKey  
WHERE a.StudentAccLevel= '+@SQL  
  
EXECUTE sp_executesql @SQL  
  
END  

Then you could execute this procedure like below:

EXEC USP_PROCEDURE 'ABC'  
EXEC USP_PROCEDURE 'DEF'  
EXEC USP_PROCEDURE 'GGG'  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

First, that is absolutely the wrong way to do it, if you are to do it with dynamic SQL. That code is open for SQL injection, it will litter the plan cache, there will be a problem if users enters single quotes in input, and dates will give you a headache. And it is difficult to read, write and maintain.

The simplest may be to write it as a single static query, which you put OPTION (RECOMPILE) at the end, to get good performance. This will also solve the problem with SSRS.

If you are going to use dynamic SQL, you need to learn to use parameterised statements. I am not take up space with it here, but I refer you to an article that I have written, which also includes an example:
http://www.sommarskog.se/dyn-search.html#dynsql

As for your actual question, yes, you may be able to avoid the table if you use the WITH RESULTS SETS clause when you run EXEC sp_executesql:

EXEC sp_executesql WITH RESULT SETS ((col1 int, col2 char(3), ...))

However, I am a little nervous that SSRS may use a different (and older) method to determine the result set so that WITH RESULT SETS will not work. I don't use SSRS myself, so I can't investigate this myself.