CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Tom
Tom

Reputation: 9878

How to return table output from SP_ExecuteSQL

I'm trying to create a Stored Procedure that generates a load of SQL dynamically. I'm trying to prevent string injection by using the sp_executesql to pass all of the parameters into the query. The query seems to execute fine but I have two issues. Firstly when I execute using my Alias name which uses the pattern xx\xxxxx it throws an error saying there is an error near the first two letters of the Alias. I think this is due to the NVARCHAR type but not entirely sure how to handle the \ in this. Secondly, and more importantly, the overall procedure returns nothing. It tells me that it has executed fine but I don't get my table of results as expected.

I've omitted the statements to generate the parameters for brevity (These work fine if I generate the whole string and Run EXEC string

I'm also wary of using global temp tables as this will be run in a multi-user environment

Updated with full code

DECLARE @IDType NVARCHAR(255) = NULL
DECLARE @Customer NVARCHAR(MAX) = NULL
DECLARE @IdentifiedBy NVARCHAR(255) = NULL
DECLARE @ImpactArea NVARCHAR(MAX) = NULL
DECLARE @Gateway NVARCHAR(255) = NULL
DECLARE @ProbabilityRating NVARCHAR(255) = NULL
DECLARE @ImpactRating NVARCHAR(255) = NULL
DECLARE @CostRevenue NVARCHAR(255) = NULL
DECLARE @Status NVARCHAR(255) = NULL
DECLARE @Keywords NVARCHAR(MAX) = NULL
DECLARE @govOwner NVARCHAR(255) = NULL

DECLARE @Alias VARCHAR(50)
DECLARE @Role NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Where NVARCHAR(MAX) = ''
DECLARE @SQLOrder NVARCHAR(MAX)

SET @Alias = SUSER_SNAME()
SET @Role =(SELECT [Role] FROM [FB].[Users] WHERE [Alias] = @Alias)

IF @IDType IS NOT NULL
BEGIN
IF @IDType = 'Blank'
    SET @Where += ' AND IDType = NULL OR IDType = '''''
ELSE IF @IDType != 'All'
    SET @Where += ' AND IDType = @IDType'
END
IF @Customer IS NOT NULL
BEGIN
IF @Customer = 'Blank'
    SET @Where += ' AND Customer = NULL OR Customer = '''''
ELSE IF @Customer != 'All'
    SET @Where += ' AND Customer = @Customer'
END
IF @IdentifiedBy IS NOT NULL
BEGIN
IF @IdentifiedBy = 'Blank'
    SET @Where += ' AND IdentifiedBy = NULL OR IdentifiedBy = '''''
ELSE IF @IdentifiedBy != 'All'
    SET @Where += ' AND IdentifiedBy = (SELECT FB.Alias(@IdentifiedBy))'
END
IF @ImpactArea IS NOT NULL
BEGIN
IF @ImpactArea = 'Blank'
    SET @Where += ' AND ImpactArea = NULL OR ImpactArea = '''''
ELSE IF @ImpactArea != 'All'
    SET @Where += ' AND ImpactArea = @ImpactArea'
END
IF @Gateway IS NOT NULL
BEGIN
IF @Gateway = 'Blank'
    SET @Where += ' AND Gateway = NULL OR Gateway = '''''
ELSE IF @Gateway != 'All'
    SET @Where += ' AND Gateway = @Gateway'
END
IF @ProbabilityRating IS NOT NULL
BEGIN
IF @ProbabilityRating = 'Blank'
    SET @Where += ' AND ProbabilityRating = NULL OR ProbabilityRating = '''''
ELSE IF @ProbabilityRating != 'All'
    SET @Where += ' AND ProbabilityRating = @ProbabilityRating'
END
IF @ImpactRating IS NOT NULL
BEGIN
IF @ImpactRating = 'Blank'
    SET @Where += ' AND ImpactRating = NULL OR ImpactRating = '''''
ELSE IF @ImpactRating != 'All'
    SET @Where += ' AND ImpactRating = @ImpactRating'
END
IF @CostRevenue IS NOT NULL
BEGIN
IF @CostRevenue = 'Blank'
    SET @Where += ' AND CostRevenue = NULL OR CostRevenue = '''''
ELSE IF @CostRevenue != 'All'
    SET @Where += ' AND CostRevenue = @CostRevenue'
END
IF @Status IS NOT NULL
BEGIN
IF @Status = 'Blank'
    SET @Where += ' AND Status = NULL OR Status = '''''
ELSE IF @Status != 'All'
    SET @Where += ' AND Status = @Status'
END
IF @Keywords IS NOT NULL

IF @govOwner IS NOT NULL
BEGIN
IF @govOwner = 'Blank'
    SET @Where += ' AND govOwner = NULL OR govOwner = '''''
ELSE IF @govOwner != 'All'
    SET @Where += ' AND govOwner = (SELECT FB.Alias(@govOwner))'
END
CREATE TABLE #tmp (
    ID int
    ,FeedbackType varchar(255)
    ,ImpactArea varchar(255)
    ,CreatedDate varchar(11)
    ,Customer varchar(255)
    ,IdentifiedBy varchar(255)
    ,CriticalityRating varchar(255)
    ,govOwner varchar(255)
    ,Status varchar(255)
)

SET @SQL = 'SELECT
            fb.ID
            ,FeedbackType
            ,ImpactArea
            ,CONVERT(VARCHAR(11), CreatedDate, 3) AS CreatedDate
            ,Customer
            ,IdentifiedBy
            ,CriticalityRating
            ,govOwner
            ,Status
        INTO #tmp
        FROM [FB].[Feedback] fb 
        INNER JOIN (SELECT
                        ID
                        ,MAX(Version) AS MaxVer
                    FROM FB.Feedback
                    GROUP BY ID
        ) mv ON fb.ID = mv.ID AND fb.Version = mv.MaxVer'

SET @SQLOrder = ' ORDER BY [ID] DESC'

IF @Where IS NOT NULL
    SET @Where = ' WHERE ' + (SELECT STUFF(@Where,1 , 4, '')) + ' '

IF (@Role != 'Governance Board' AND @Role != 'Admin')
    BEGIN
    IF @Where IS NOT NULL
        SET @Where += ' AND [Author] = @Alias OR [IdentifiedBy] = @Alias'
    ELSE
        SET @Where = ' WHERE [Author] = @Alias OR [IdentifiedBy] = @Alias'
    END

SET @SQL += @Where + @SQLOrder

EXECUTE SP_ExecuteSQL @SQL
    ,@Alias = @Alias
    ,@Customer = @Customer
    ,@IdentifiedBy = @IdentifiedBy
    ,@ImpactArea = @ImpactArea
    ,@Gateway = @Gateway
    ,@ProbabilityRating = @ProbabilityRating
    ,@ImpactRating = @ImpactRating
    ,@CostRevenue = @CostRevenue
    ,@Status = @Status
    ,@Keywords = @Keywords
    ,@govOwner = @govOwner

SELECT * FROM #tmp
DROP TABLE #tmp

Upvotes: 1

Views: 7505

Answers (2)

BeanFrog
BeanFrog

Reputation: 2315

Change @SQL by removing the line 

INTO #tmp

Then above EXECUTE SP_ExecuteSQL add the line

INSERT INTO #tmp

This will change your EXECUTE to a select statement, and then insert what is selected into your temp table, all in this session.

The error you're getting about the Alias parameter is because you're actually missing a bit. sp_executesql requires @params when using parameters, which defines the parameters you are using. From the documentation:

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

and

[ @params= ] N'@parameter_namedata_type [ ,... n ] ' Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. Each parameter definition consists of a parameter name and a data type. n is a placeholder that indicates additional parameter definitions. Every parameter specified in @stmtmust be defined in @params. If the Transact-SQL statement or batch in @stmt does not contain parameters, @params is not required. The default value for this parameter is NULL.

In your case this will be as follows:

DECLARE @params NVARCHAR(300) = N'@Alias VARCHAR(50), @Customer NVARCHAR(MAX), @IdentifiedBy NVARCHAR(255), @ImpactArea NVARCHAR(MAX),@Gateway NVARCHAR(255),@ProbabilityRating NVARCHAR(255), @ImpactRating NVARCHAR(255), @CostRevenue NVARCHAR(255), @Status NVARCHAR(255), @Keywords NVARCHAR(MAX), @govOwner NVARCHAR(255)';

And then use it as follows:

EXECUTE SP_ExecuteSQL @SQL, @params
,@Alias = @Alias
etc, etc

So you have to define your variables for the stored procedue, which you have done, but also for the dynamic sql you are running, which is the @params.

Upvotes: 4

Ruslan K.
Ruslan K.

Reputation: 1981

  1. You must pass params definition to sp_executesql as shown below:
DECLARE @p NVARCHAR(MAX) = 'test';
SET @SQL = 'select @p';
EXECUTE SP_ExecuteSQL @SQL, N'@p NVARCHAR(MAX)', @p = @p;
  1. Use #tbl instead of @tbl, remove ,@tbl = @tbl OUTPUT. Don't use SELECT INTO statement. Local temporary tables are visible in the current session, includes code in called procedures and nested dynamic SQL.

Upvotes: 0

Related Questions