Cadavre (9/27/2013)
enriquezreyjoseph (9/27/2013)
Cadavre (9/27/2013)
enriquezreyjoseph (9/27/2013)
Thank you guys..so, i should change varchar now to my whole table and to my front-end...tsk :-(..
You're also vulnerable to SQL injection. Please look over the code I posted and see the difference between it and yours.
Is ' Stuff ' a keyword???
How is that what you took from what I said? Yes, Stuff is a keyword.
Look at the parametrised dynamic SQL that I used, rather than what you used. If you don't understand the difference, then frankly for the sake of your job and the health of your companies data you need to not use dynamic SQL. It's a powerful tool and when used correctly can do a lot of good. When used incorrectly, it can be a catastrophe.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchBiography]
@firstname NVARCHAR(50),
@middlename NVARCHAR(50),
@lastname NVARCHAR(50),
@sexID int,
@statusID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery NVARCHAR(MAX)
DECLARE @WhereClause NVARCHAR(MAX)
SET @sqlquery = ''
SET @WhereClause = STUFF(CASE WHEN @sexID <> 0 THEN ' AND sexID = ' + convert(varchar(20), @sexID) ELSE '' END +
CASE WHEN @statusID <> 0 THEN ' AND statusID = ' + convert(varchar(20), @statusID) ELSE '' END +
CASE WHEN LEN(@firstname) > 0 THEN ' AND firstname LIKE ''%'+ @firstname + '%''' ELSE '' END +
CASE WHEN LEN(@middlename) > 0 THEN ' AND middlename LIKE ''%' + @middlename + '%'''ELSE '' END +
CASE WHEN LEN(@lastname) > 0 THEN ' AND lastname LIKE ''%' + @lastname + '%'''ELSE '' END, 1, 5, '')
SET @WhereClause = CASE WHEN LEN(@WhereClause) > 1 THEN ' WHERE '+ @WhereClause ELSE @WhereClause END
SET @sqlquery = ' SELECT * FROM TestMyView ' + @WhereClause
PRINT (@SqlQuery)
EXEC (@SqlQuery)
END
Can I used this instead of sp_executesql i will use EXEC only??...cadavare