Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to search? Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 7:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
Hi Everyone

I hope everyone is having a nice day ahead

I have a question guys...

I want to search for example a Sex whether Male or Female regardless of their status..

How should i do that??...here is my Code in SQL 2005 and my Front-end is Visual Studio 2008

see my UI attachment

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]

@firstname varchar(50),
@middlename varchar(50),
@lastname varchar(50),
@sex varchar(50),
@status varchar(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), @SqlSexIDQuery varchar(max), @SqlStatusIDQuery varchar(max)

SET @SqlQuery = ''


IF LEN(@sex) > 0
SET @SqlQuerySex = ' sex = ''' + @sex + ''''
ELSE
SET @SqlQuerySex = ''


IF LEN(@status) > 0
SET @SqlQueryStatus = ' AND status = ''' + @status + ''''
ELSE
SET @SqlQueryStatus = ''


IF @sexID <> 0
SET @SqlSexIDQuery = ' AND sexID IN(SELECT sexID FROM sex WHERE sexID = ' + convert(varchar(20), @sexID) + ' )'
ELSE
SET @SqlSexIDQuery = ''


IF @statusID <> 0
SET @SqlStatusIDQuery = ' AND statusID IN(SELECT statusID FROM status WHERE statusID = ' + convert(varchar(20), @statusID)+ ')'
ELSE
SET @SqlStatusIDQuery= ''


IF LEN(@firstname) > 0
SET @SqlQueryFirstName = ' AND firstname like ''%' + @firstname + '%'''
ELSE
SET @SqlQueryFirstName = ''


IF LEN(@middlename) > 0
SET @SqlQueryMiddleName = ' AND middlename like ''%' + @middlename + '%'''
ELSE
SET @SqlQueryMiddleName = ''


IF LEN(@lastname) > 0
SET @SqlQueryLastName =' AND lastname like ''%' + @lastname + '%'''
ELSE
SET @SqlQueryLastName = ''


SET @SqlQuery = 'SELECT * FROM TestMyView WHERE '
SET @SqlQuery = @SqlQuery + @SqlQuerySex + @SqlQueryStatus + @SqlSexIDQuery + @SqlStatusIDQuery + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName


EXEC(@SqlQuery)
PRINT(@SqlQuery)

END












  Post Attachments 
searchResult.JPG (2 views, 40.82 KB)
Post #1498101
Posted Tuesday, September 24, 2013 7:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
Well, you can either search the table for one, or both.

SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M' OR SEX = 'F'

That will select all rows with either M or F in the Sex column.
If you want only one.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M'

If you are using a sproc, simply add an input parameter with the SexyType
And the query would be
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = @SexType

Have you looked at the SQL Books Online for anything? This would be the place to start.

And as almost everyone on here has stated, stop using Dynamic SQL.

Andrew SQLDBA
Post #1498103
Posted Tuesday, September 24, 2013 8:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
AndrewSQLDBA (9/24/2013)
Well, you can either search the table for one, or both.

SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M' OR SEX = 'F'

That will select all rows with either M or F in the Sex column.
If you want only one.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M'

If you are using a sproc, simply add an input parameter with the SexyType
And the query would be
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = @SexType

Have you looked at the SQL Books Online for anything? This would be the place to start.

And as almost everyone on here has stated, stop using Dynamic SQL.

Andrew SQLDBA


Hi Andrew

But some post say that..Dynamic Sql is a powerful tool...

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(..badly need some advise..
Post #1498104
Posted Tuesday, September 24, 2013 10:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 12:53 AM
Points: 525, Visits: 648
enriquezreyjoseph (9/24/2013)
AndrewSQLDBA (9/24/2013)
Well, you can either search the table for one, or both.

SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M' OR SEX = 'F'

That will select all rows with either M or F in the Sex column.
If you want only one.
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = 'M'

If you are using a sproc, simply add an input parameter with the SexyType
And the query would be
SELECT
<ColumnName>
, <ColumnName>
FROM
<TableName>
WHERE
Sex = @SexType

Have you looked at the SQL Books Online for anything? This would be the place to start.

And as almost everyone on here has stated, stop using Dynamic SQL.

Andrew SQLDBA


Hi Andrew

But some post say that..Dynamic Sql is a powerful tool...

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(..badly need some advise..






1. Debugging a dynamic query is awful.
2. risk of SQL Injection.
3. when you have a very simple solution available for sex search, then why go dynamic
Post #1498112
Posted Wednesday, September 25, 2013 1:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 40,672, Visits: 37,137
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...


Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(


Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.

Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Oh, and if you want to know why the way you've written it is bad, try running this:

EXEC [dbo].[SearchBiography] @firstname = 'Gary'
@middlename = ''
@lastname = 'White''; shutdown with nowait --'
@sex = ''
@status = ''
@sexID = 0
@statusID = 0;

Edit: one quote too many



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1498166
Posted Wednesday, September 25, 2013 1:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
GilaMonster (9/25/2013)
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...


Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(


Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.



Thank you my Friend GilaMonster

You are the Monster of SQL ...1billion thumbs Up to you my friend
Post #1498172
Posted Wednesday, September 25, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
It was me who has repeatedly told you over the last few days that you need to parameterize your dynamic sql. I have posted the same link to the same article that Gail posted (she is the author of that fantastic piece of work btw). Please take the time to read it. You said you have read it but you keep posting code that is vulnerable to sql injection.

I even showed you a code example of how dangerous injection can be. Do yourself and your company a favor and stop using dynamic sql without parameters.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1498414
Posted Wednesday, September 25, 2013 6:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
Sean Lange (9/25/2013)
It was me who has repeatedly told you over the last few days that you need to parameterize your dynamic sql. I have posted the same link to the same article that Gail posted (she is the author of that fantastic piece of work btw). Please take the time to read it. You said you have read it but you keep posting code that is vulnerable to sql injection.

I even showed you a code example of how dangerous injection can be. Do yourself and your company a favor and stop using dynamic sql without parameters.


Thank you Sean

Ok sean i will do that, i'm new to sql and vb.net and you all enlighten me to a Very Very vEry Goood Exellent answers, hoping to learn more from you
Post #1498639
Posted Wednesday, September 25, 2013 6:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
Sean, What do you mean i don't have a parameter??....I guess i have parameters look at my first post above...

like this..

==================================
ALTER PROCEDURE [dbo].[SearchBiography]

@firstname varchar(50),
@middlename varchar(50),
@lastname varchar(50),
@sex varchar(50),
@status varchar(50),
@sexID int,
@statusID int
==================================

firstname,middlename, lastname..etc are parameters of my stored procedure right?..
Post #1498640
Posted Wednesday, September 25, 2013 7:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
GilaMonster (9/25/2013)
enriquezreyjoseph (9/24/2013)
But some post say that..Dynamic Sql is a powerful tool...


Indeed, but you don't use a jackhammer to put a nail in the wall. Powerful tool != use all the time

can you give me a strong justification why should i stop using dynamic SQL?? PLEASE :-(


Harder to write. Much harder to read. Very hard to debug. Vulnerable to SQL injection. Requires elevated permissions. I could go on. When I do code reviews, code that uses dynamic SQL for no good reason goes straight back to the developer to fix.

Now, if you want to do a dynamic search, you will need dynamic SQL, but not the very convoluted, insecure way you've got.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Oh, and if you want to know why the way you've written it is bad, try running this:

EXEC [dbo].[SearchBiography] @firstname = 'Gary'
@middlename = ''
@lastname = 'White''; shutdown with nowait --'
@sex = ''
@status = ''
@sexID = 0
@statusID = 0;

Edit: one quote too many


Hi Sir Gail..

Please see my Attachment..that is the result when i try to run your suggestion...

Can you explain to me why sir Gail??..thanks..


  Post Attachments 
sir_Gail.JPG (12 views, 37.00 KB)
Post #1498646
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse