question

ronbarlow-5250 avatar image
0 Votes"
ronbarlow-5250 asked ronbarlow-5250 commented

Variable SQL Cluase in Stored procedure

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 @SQL = ' 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 @SQL=@SQL+ 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 @SQL=@SQL+' ) As a'

EXECUTE sp_executesql @SQL

SELECT * FROM dbo.tblTest

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Ron,
Your issue involves T-SQL related information, I will add a T-SQL tag for you, I hope it can help you.
Best Regards,
Joy

0 Votes 0 ·

Hi @ronbarlow-5250,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!


Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ronbarlow-5250 commented

Hi @ronbarlow-5250,

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Many thanks.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.