Click here to Skip to main content
15,037,168 members
Home / Discussions / Database
   

Database

 
GeneralRe: Max. number of concurrent user in PostGre database Pin
Wendelius17-Aug-11 19:35
mveWendelius17-Aug-11 19:35 
GeneralRe: Max. number of concurrent user in PostGre database Pin
jschell18-Aug-11 13:02
Memberjschell18-Aug-11 13:02 
GeneralRe: Max. number of concurrent user in PostGre database Pin
Navin Pandit18-Aug-11 18:33
MemberNavin Pandit18-Aug-11 18:33 
GeneralRe: Max. number of concurrent user in PostGre database Pin
jschell19-Aug-11 7:34
Memberjschell19-Aug-11 7:34 
QuestionSearching with optional parameters [modified] Pin
Pascal Ganaye16-Aug-11 1:36
MemberPascal Ganaye16-Aug-11 1:36 
AnswerRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 3:23
professionalPIEBALDconsult16-Aug-11 3:23 
GeneralRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 4:16
MemberPascal Ganaye16-Aug-11 4:16 
AnswerRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 4:21
MemberPascal Ganaye16-Aug-11 4:21 
The answer above is valid in C#.
My good collegue and friend James H gave me some hints on how to fix it in T-SQL.


DECLARE @ContactName NVARCHAR(255)
DECLARE @Reference   NVARCHAR(255)
DECLARE @sql         NVARCHAR(255)

SET @sql = 'SELECT TOP 100 FamilyName,Reference FROM Contact C WHERE 1=1'

-- SET @ContactName = 'Smith'
-- SET @Reference   = 'A%'

IF NOT @ContactName IS NULL  
 SET @sql = @sql + ' AND C.ContactName  = @ContactName'
IF NOT @Reference IS NULL 
 SET @sql = @sql + ' AND C.Reference LIKE @Reference'
 
EXECUTE sp_executesql 
 @sql,
 N'@FamilyName NVARCHAR(255), @CoName NVARCHAR(255)',
 @ContactName, @Reference 


This approach is much better for several reasons:
- no quoted parameters, the parameters are passed by name so there is no possible SQL injection
- because the query do not change on each request we can assume it will be optimized and cached
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 17:56
professionalPIEBALDconsult16-Aug-11 17:56 
GeneralRe: Searching with optional parameters Pin
Mycroft Holmes16-Aug-11 18:06
professionalMycroft Holmes16-Aug-11 18:06 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult17-Aug-11 2:48
professionalPIEBALDconsult17-Aug-11 2:48 
GeneralRe: Searching with optional parameters Pin
Mycroft Holmes17-Aug-11 5:40
professionalMycroft Holmes17-Aug-11 5:40 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult17-Aug-11 14:11
professionalPIEBALDconsult17-Aug-11 14:11 
GeneralRe: Searching with optional parameters Pin
S Douglas20-Aug-11 7:53
professionalS Douglas20-Aug-11 7:53 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult20-Aug-11 13:01
professionalPIEBALDconsult20-Aug-11 13:01 
QuestionRe: Searching with optional parameters Pin
Shameel16-Aug-11 4:52
professionalShameel16-Aug-11 4:52 
AnswerRe: Searching with optional parameters Pin
Pascal Ganaye16-Aug-11 6:02
MemberPascal Ganaye16-Aug-11 6:02 
GeneralRe: Searching with optional parameters Pin
PIEBALDconsult16-Aug-11 18:04
professionalPIEBALDconsult16-Aug-11 18:04 
QuestionSQL bit to bool conversion [moved] Pin
5fingers16-Aug-11 0:37
Member5fingers16-Aug-11 0:37 
GeneralRe: SQL bit to bool conversion Pin
Nagy Vilmos16-Aug-11 0:39
professionalNagy Vilmos16-Aug-11 0:39 
GeneralRe: SQL bit to bool conversion Pin
Shameel16-Aug-11 4:53
professionalShameel16-Aug-11 4:53 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 0:59
Member5fingers17-Aug-11 0:59 
GeneralRe: SQL bit to bool conversion Pin
Dalek Dave16-Aug-11 0:39
professionalDalek Dave16-Aug-11 0:39 
GeneralRe: SQL bit to bool conversion Pin
5fingers17-Aug-11 0:57
Member5fingers17-Aug-11 0:57 
GeneralRe: SQL bit to bool conversion Pin
chriselst16-Aug-11 0:40
Memberchriselst16-Aug-11 0:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.