[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

retrieving all customers whose surname begins with a certain letter

Hi

How would i write a select query to retrieve all of the rows in my customer table whose surname begins with a certain letter (e.g. all the customers whose surname starts with 'E')

The query will be built using dynamic sql and the letter will be stored in a variable.

thanks
andrea
0
andieje
Asked:
andieje
3 Solutions
 
NightmanCTOCommented:
declare @Letter varchar(1)
declare @SQL nvarchar(3000)

SET @Letter ='E'

SET @SQL = 'SELECT * FROM MyTable WHERE surname LIKE ''' + @Letter + '%'''
EXEC (@SQL)
0
 
Daniel WilsonCommented:
Too bad you have to do dynamic SQL.

I'm going to assume the surname is in its own field ...

Select * from MyTable Where SurName like 'E%'

to use a variable so you might avoid the dynamic SQL ...

Select * From Mytable Where Surname like @FirstLetter + '%'

0
 
NightmanCTOCommented:
Just remember that dynamic SQL comes with the overhead of no cached query plan, and if used in a stored procedure the user account executing the SQL needs select permissions on the underlying tables, instead of simply stored proc execute rights. Also is a performance overhead in terms of recompiles (CPU) and opens you up to the risk of SQL injection.
0
 
LowfatspreadCommented:
do you really need dynamic sql...

you can code it statically as

select *
 from yourtable
where surname like @yourstartname +'%'

 
0
 
andiejeAuthor Commented:
i'm using an or mapper where i can supply the query as a string containing a where clause. the or mapper cleans the data to prevent sql injection attacks

thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now