Is this Correct Use of Dynamic SQL???

Question

Is this Correct Use of Dynamic SQL???

enriquezreyjoseph

Ten Centuries

Points: 1291
More actions
September 27, 2013 at 11:19 pm

#299297

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]
@firstname nvarchar(50),
@middlename nvarchar(50),
@lastname nvarchar(50),
@sexID int,
@statusID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max)
SET @sqlquery = ''
SET @SqlQueryStatus = ''
SET @SqlQueryFirstname = ''
SET @SqlQueryMiddlename = ''
SET @SqlQueryLastName = ''
SET @SqlQuerySex = ''
SET @SqlQueryStatus = ''
IF @sexID <> 0
SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID)
IF @statusID <> 0
BEGIN
IF LEN(@SqlQuerySex) > 0
SET @SqlQueryStatus = ' AND statusID = ' + convert(varchar(20), @statusID)
ELSE
SET @SqlQueryStatus = ' WHERE statusID = ' + convert(varchar(20), @statusID)
END
IF LEN(@firstname) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0
SET @SqlQueryFirstname = ' AND firstname like ''%' + @firstname + '%'''
ELSE
SET @SqlQueryFirstname = ' WHERE firstname like ''%' + @firstname + '%'''
END
IF LEN(@middlename) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0
SET @SqlQueryMiddlename = ' AND middlename like ''%' + @middlename + '%'''
ELSE
SET @SqlQueryMiddlename = ' WHERE middlename like ''%' + @middlename + '%'''
END
IF LEN(@lastname) > 0
BEGIN
IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 or LEN(@SqlQueryMiddlename) > 0
SET @SqlQueryLastname = ' AND lastname like ''%' + @lastname + '%'''
ELSE
SET @SqlQueryLastname = ' WHERE lastname like ''%' + @lastname + '%'''
END
SELECT @SqlParam = ' @xfirstname nvarchar(50),
@xmiddlename nvarchar(50),
@xlastname nvarchar(50),
@xsexID int,
@xstatusID int '
EXEC sp_executesql, @SqlParam, @firstname,@middlename,@lastname,@sexID,@statusID
END

Viewing 13 posts - 1 through 12 (of 12 total)

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

pietlinden SSC Guru Points: 63506 More actions · Answer 1

The correct use for dynamic SQL is Option 1: DON'T, especially if you're a noob.

If you understand the repercussions of code that can't be optimized, and SQL injection attacks, etc, then knock yourself out.

It's perfectly okay to have a lot of stored procedures in your database, because those can be optimized.

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 2

pietlinden (9/28/2013)
The correct use for dynamic SQL is Option 1: DON'T, especially if you're a noob.
If you understand the repercussions of code that can't be optimized, and SQL injection attacks, etc, then knock yourself out.
It's perfectly okay to have a lot of stored procedures in your database, because those can be optimized.

I can't understand :-(..please elaborate more please 😉

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 3

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:<Author,,Name>

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[SearchBiography]

@firstname nvarchar(50),

@middlename nvarchar(50),

@lastname nvarchar(50),

@sexID nchar(5) = NULL,

@statusID nchar(5) = NULL

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sqlquery nvarchar(max) , @SqlParam nvarchar(max)

SET @sqlquery = ''

SELECT @sqlquery = ' SELECT * ' + ' FROM TestMyView WHERE 1 = 1 '

IF @sexID <> '0'

SELECT @sqlquery = @sqlquery + ' AND sexID LIKE ' + @sexID

IF @statusID <> '0'

SELECT @sqlquery = @sqlquery + ' AND statusID LIKE ' + @statusID

IF LEN(@firstname) > 0

SELECT @sqlquery = @sqlquery + ' AND firstname LIKE ''%'+@firstname+'%'''

IF LEN(@middlename) > 0

SELECT @sqlquery = @sqlquery + ' AND middlename LIKE ''%'+@middlename+'%'''

IF LEN(@lastname) > 0

SELECT @sqlquery = @sqlquery + ' AND lastname LIKE ''%'+@lastname+'%'''

SELECT @SqlParam = '@xfirstname nvarchar(50),

@xmiddlename nvarchar(50),

@xlastname nvarchar(50),

@xsexID nchar(5),

@xstatusID nchar(5) '

EXEC sp_executesql @sqlquery,@SqlParam,

@firstname, @middlename,

@lastname, @sexID,

@statusID

END

How about this??..huhuhuhuh ;-(

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

What are the correct data types for the following columns:

sexID

statusID

firstname

middlename

lastname

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 5

Lynn Pettis (9/28/2013)
What are the correct data types for the following columns:
sexID
statusID
firstname
middlename
lastname

sexID int

statusID int

firstname varchar(20)

middlename varchar(20)

lastname varchar(20)

that is in my table

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

enriquezreyjoseph (9/28/2013)
Lynn Pettis (9/28/2013)
What are the correct data types for the following columns:
sexID
statusID
firstname
middlename
lastname
sexID int
statusID int
firstname varchar(20)
middlename varchar(20)
lastname varchar(20)
that is in my table

Okay, then based on the info above, your code is inefficient. Your parameters to both your stored procedure and the dynamic sql you are building should match the data types of the columns in your table/view.

Also, the way you wrote your last dynamic sql you don't even need the parameters you defined, you aren't using them. The code you have written is ripe for SQL injection.

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

When you post in multiple threads like this, you fragment answers and make people have to start from the beginning again with helping you. If you have further questions, stick to your original thread please.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

All of the posted code is vulnerable to SQL injection. Please, please, for the third or fourth time, read up on SQL injection and don't use dynamic SQL until you have done so and understand how and why it's such a risk.

You've got sp_execute SQL with parameters being passed to it, but those parameters are never used anywhere in the dynamic SQL and hence give you no protection at all. Passing parameters is not the key. Using only parameters is the key

Additionally, you have been referred, more than once, to my blog post on how to do catch-all queries safely with no injection risk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 9

GilaMonster (9/28/2013)
All of the posted code is vulnerable to SQL injection. Please, please, for the third or fourth time, read up on SQL injection and don't use dynamic SQL until you have done so and understand how and why it's such a risk.
You've got sp_execute SQL with parameters being passed to it, but those parameters are never used anywhere in the dynamic SQL and hence give you no protection at all. Passing parameters is not the key. Using only parameters is the key
Additionally, you have been referred, more than once, to my blog post on how to do catch-all queries safely with no injection risk.

Thank you sir:-)

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 10

Cadavre (9/28/2013)
When you post in multiple threads like this, you fragment answers and make people have to start from the beginning again with helping you. If you have further questions, stick to your original thread please.

Sorry:-(....

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

pietlinden (9/28/2013)
If you understand the repercussions of code that can't be optimized...

Actually, one of the best reasons for using Dynamic SQL is to optimize the performance of "Catch All" queries. Gail Shaw has a great article on the subject.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 12

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:<Author,,Name>

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[SearchBiography]

@firstname nvarchar(50),

@middlename nvarchar(50),

@lastname nvarchar(50),

@sexID nchar(5) = NULL,

@statusID nchar(5) = NULL

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sqlquery nvarchar(max) , @SqlParam nvarchar(max)

SET @sqlquery = ''

SELECT @sqlquery = ' SELECT * ' + ' FROM TestMyView WHERE 1 = 1 '

IF @sexID <> '0'

SELECT @sqlquery = @sqlquery + ' AND sexID = @xsexID '

IF @statusID <> '0'

SELECT @sqlquery = @sqlquery + ' AND statusID = @xstatusID '

IF LEN(@firstname) > 0

SELECT @sqlquery = @sqlquery + ' AND firstname LIKE ''%'' + @xfirstname + ''%'' '

IF LEN(@middlename) > 0

SELECT @sqlquery = @sqlquery + ' AND middlename LIKE ''%'' + @xmiddlename + ''%'' '

IF LEN(@lastname) > 0

SELECT @sqlquery = @sqlquery + ' AND lastname LIKE ''%'' + @xlastname + ''%'' '

SELECT @SqlParam = '@xfirstname nvarchar(50),

@xmiddlename nvarchar(50),

@xlastname nvarchar(50),

@xsexID nchar(1),

@xstatusID nchar(1) '

EXEC sp_executesql @sqlquery,@SqlParam,

@xfirstname = @firstname, @xmiddlename = @middlename,

@xlastname = @lastname, @xsexID = @sexID,

@xstatusID = @statusID

END

by reference to : http://www.sommarskog.se/dyn-search-2005.html and http://www.sommarskog.se/dynamic_sql.html