A parameter for Where clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

A parameter for Where clause

Author

Topic

Neullson
Starting Member

4 Posts

Posted - 2010-08-20 : 03:44:48

Hello guys, I'm a newbie here, hope for your Guide...

Ok, this is my case, let say I want to search table Customer by CusName.

SELECT * FROM Customer WHERE CusName LIKE '%dean%'

I want to make the searching on this table Customer more efficient, so I don't have to write a different CODE for another Field. So, I just have to give the Field Name parameter. Like this one.

DECLARE @searchBy AS varchar(20)
SELECT @seacrhBy = '[It's gonna be a Field]'
SELECT * FROM Customer WHERE @searchBy LIKE '%hahahaha%'

I'v tried it, but it's failed.

Question:

Can I do what I mention it above ?

If there is a way to do it... How?

Thanks you for your help...

Failed now, to be more perfect later

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-20 : 03:51:40

Go for dynamic SQL.
here you can make more dynamic by passing searchstring also -


DECLARE @searchBy AS varchar(20), @searchString AS varchar(20), @SQL AS VARCHAR(1000)
SELECT @searchBy = 'ColumnName'
SELECT @searchString = 'hahahaha'
SET @SQL = 'SELECT * FROM Customer WHERE '+@searchBy+' LIKE '+ '''%'+@searchString+'%'''
EXEC ( @SQL )

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-20 : 03:55:27

It seems your definition of efficient is different than mine. My definition has to do with performance of queries. Your definition appears to be about less programming.

If you want it efficient (by my definition), then do NOT use dynamic SQL for this.

If you are doing this from within a stored procedure, then just don't bother using a stored procedure. Build the query in the application instead.

If you must use a stored procedure, then you'll need to use dynamic SQL. But it won't be efficient!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Neullson
Starting Member

4 Posts

Posted - 2010-08-20 : 04:17:29

Thank's a lot vaibhavktiwari83, I'll try it...
Thank's a lot tkizer for your tips... Wow, you are an MVP, I have to learn a lot from you.

Failed now, to be more perfect later

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-20 : 04:58:40

Welcome

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 05:29:54

Also make sure to read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Neullson
Starting Member

4 Posts

Posted - 2010-08-20 : 05:39:14

Hello guys, I think, I have to ask for your help again... Hihihihi...
This is my SQL Code :


Declare @searchBy AS varchar(20), @searchString AS varchar(20), @SQL AS VARCHAR(1000),
		@MaxRecord as int, @ItemPerPage as int, @PageIndex as int
SELECT @searchBy = 'AssigmentName'
SELECT @searchString = 'ing'
SELECT @MaxRecord = 30
SELECT @ItemPerPage = 5
SELECT @PageIndex = 0

SET @SQL = 'SELECT TOP('+@ItemPerPage+') * FROM
		(SELECT TOP('+@MaxRecord+')
			ROW_NUMBER() OVER(ORDER BY AssignmentHeader.ID) as RowNumber,
			AssignmentHeader.*,
			AssignmentCategories.AssignmentCategoriesName,
			UserProfile.FullName AS AssignedByName, 
			UserProfile_1.FullName AS AssignedToName
		FROM
			AssignmentCategories
			INNER JOIN AssignmentHeader ON AssignmentCategories.ID = AssignmentHeader.AssignmentCategoryID
			INNER JOIN UserProfile ON AssignmentHeader.AssignedBy = UserProfile.ID
			INNER JOIN UserProfile AS UserProfile_1 ON AssignmentHeader.AssignedTo = UserProfile_1.ID) PageDetail
	WHERE RowNumber > ('+@ItemPerPage+' * '+'('+@PageIndex - 1+'))'+' AND '+@searchBy+' LIKE '+ '''%'+@searchString+'%'''
EXEC (@SQL)

I got an error message.

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value 'SELECT TOP(' to data type int.

Question:

Is dynamic SQL can't use int type ?

Or is it my Code that error..?

Thank's for your help...

Failed now, to be more perfect later

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-20 : 05:58:34

MaxRecord , @ItemPerPage and @PageIndex are all integers. You need to convert all of them them to varchar before concatenating them into the string. (not just the one that Vaibhav did in his post above, all three of them in all the places they appear)

I do hope that good performance is not a requirement here. This query is likely to perform terribly regardless of indexes (because the only predicates are on computed columns or have leading wildcards.) Tablescans are a near certainty.
Also, you have taken steps to prevent SQL injection, right? Otherwise imagine what's going to happen when someone passes, for @SearchBy, the value "'; DROP TABLE UserProfile; --"

For what it's worth, I'm in full agreement with Tara. This is not a good design pattern, dynamic SQL is not a good way of going about this.

--
Gail Shaw
SQL Server MVP

Neullson
Starting Member

4 Posts

Posted - 2010-08-22 : 09:22:57

Thanks you very much GilaMonster... I write this CODE, just for my experiment... I know a lot now... Thank's...

Failed now, to be more perfect later

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources