sp_executesql help

Question

Post reply

sp_executesql help

Alan Burstein

SSC Guru

Points: 61141
More actions
December 4, 2014 at 8:13 pm

#397735

Hello everyone,
I have a stored proc I am using for an SSRS application. It uses dynamic sql; what it does is pretty self-explanatory. Here's the stored proc:
USE tempdb
GO
IF EXISTS
(
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME = 'dynamic_ssrs_paramsearch'
)
DROP PROC dbo.dynamic_ssrs_paramsearch;
GO
CREATE PROC dbo.dynamic_ssrs_paramsearch
(
@ParamTable varchar(100),-- include schema, error handling to deal with
@ParamValue varchar(100),
@ParamCaption varchar(100),
@SearchText varchar(100),
@CSFlag bit = 0
)
AS
DECLARE @dsql varchar(1000) = '
WITH searchtext(searchtext) AS (SELECT REPLACE('''+@searchtext+''',''*'',''%''))
SELECT ParamValue = '+@ParamValue+', ParamCaption = '+@ParamCaption+'
FROM '+@ParamTable+' '+'
CROSS APPLY searchtext st
WHERE ('+cast(@CSFlag AS char(1))+' = 0 AND 0 <> PATINDEX(searchtext,'+@ParamCaption+'))
OR ('+cast(@CSFlag AS char(1))+' = 1 AND 0 <> PATINDEX((searchtext COLLATE Latin1_General_BIN),'+@ParamCaption+'))'
EXEC(@dsql);
GO
Here's an example of my telling it to return the database_id and name columns from sys.databases. It will search the name column in sys.databases for records containing this pattern:
*[^a-z]*
Results:
ParamValue ParamCaption
----------- --------------------------------------------------------------------------------------------------------------------------------
9 AdventureWorks2012
...
17 temp2
18 temp3
It works exactly how I want it to. Using EXEC like I am, however, I know is very bad and leaves me open to SQL injection. I'm trying to re-write this using sp_execute and having a heck of a time. Perhaps there's other ways to re-write this to prevent SQL injection...
Any help would be very appreciated. Thanks!
Notes:
1. I used sys.databases because everyone has it (easier than putting together sample data)
2. The query that I am generating using dsql can obviously be optimized by that's not really a concern because I'm only dealing with a few thousand records on most of the tables this sproc is intended for.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick suggestion (pseudo code)

😎

DECLARE @ParamTable varchar(100) = N''; -- include schema, error handling to deal with

DECLARE @ParamValue varchar(100) = N'';

DECLARE @ParamCaption varchar(100) = N'';

DECLARE @SearchText varchar(100) = N'';

DECLARE @CSFlag bit = 0 ;

DECLARE @params NVARCHAR(MAX) = N'@ParamValue varchar(100),@ParamCaption varchar(100),@SearchText varchar(100)';

DECLARE @dsql varchar(1000) = N'

WITH searchtext(searchtext) AS (SELECT REPLACE(@searchtext,''*'',''%''))

SELECT ParamValue = @ParamValue, ParamCaption = @ParamCaption

FROM '+ /* Your clean string function ;-) */ PATEXCLUDE(@ParamTable,'^[-;]') +N'

CROSS APPLY searchtext st

WHERE ('+cast(@CSFlag AS char(1))+N' = 0 AND 0 <> PATINDEX(searchtext,@ParamCaption))

OR ('+cast(@CSFlag AS char(1))+N' = 1 AND 0 <> PATINDEX((searchtext COLLATE Latin1_General_BIN),@ParamCaption))';

EXECUTE SP_EXECUTESQL @dsql, @params, @ParamValue,@ParamCaption,@SearchText;

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

Last portion of @dsql, why not just

DECLARE @dsql varchar(1000) = N'

WITH searchtext(searchtext) AS (SELECT REPLACE(@searchtext,''*'',''%''))

SELECT ParamValue = @ParamValue, ParamCaption = @ParamCaption

FROM '+ /* Your clean string function */ PATEXCLUDE(@ParamTable,'^[-;]') +N'

CROSS APPLY searchtext st

WHERE PATINDEX(searchtex '+ case @CSFlag when 1 then 'COLLATE Latin1_General_BIN' else '' end +', @ParamCaption))';

Plus one more execution plan but less unnecessary OR.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Eirikur Eiriksson (12/4/2014)
Quick suggestion (pseudo code)
😎
DECLARE @ParamTable varchar(100) = N''; -- include schema, error handling to deal with
DECLARE @ParamValue varchar(100) = N'';
DECLARE @ParamCaption varchar(100) = N'';
DECLARE @SearchText varchar(100) = N'';
DECLARE @CSFlag bit = 0 ;
DECLARE @params NVARCHAR(MAX) = N'@ParamValue varchar(100),@ParamCaption varchar(100),@SearchText varchar(100)';
DECLARE @dsql varchar(1000) = N'
WITH searchtext(searchtext) AS (SELECT REPLACE(@searchtext,''*'',''%''))
SELECT ParamValue = @ParamValue, ParamCaption = @ParamCaption
FROM '+ /* Your clean string function ;-) */ PATEXCLUDE(@ParamTable,'^[-;]') +N'
CROSS APPLY searchtext st
WHERE ('+cast(@CSFlag AS char(1))+N' = 0 AND 0 <> PATINDEX(searchtext,@ParamCaption))
OR ('+cast(@CSFlag AS char(1))+N' = 1 AND 0 <> PATINDEX((searchtext COLLATE Latin1_General_BIN),@ParamCaption))';
EXECUTE SP_EXECUTESQL @dsql, @params, @ParamValue,@ParamCaption,@SearchText;

Thank you Sir! Great work as always.

Sorry for the late reply, I did not get a chance to put test your solution until tonight (I was under the weather this weekend)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001