Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to search?


How to search?

Author
Message
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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









Attachments
searchResult.JPG (2 views, 40.00 KB)
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
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
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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..
Mr. Kapsicum
Mr. Kapsicum
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 968
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 :-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
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, 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
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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 ;-)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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 :-)
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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?..
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
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..
Attachments
sir_Gail.JPG (12 views, 37.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search