Can You make this code Shorter??..

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  September 27, 2013 at 2:30 am

  #1653911

  Why not build up your WHERE clause like this?

  ALTER PROCEDURE [dbo].[SearchBiography] @firstname NVARCHAR(50), @middlename NVARCHAR(50), @lastname NVARCHAR(50), @sexID NCHAR(1), @statusID NCHAR(1) AS

  BEGIN;

  SET NOCOUNT ON;

  DECLARE @sqlquery NVARCHAR(MAX);

  DECLARE @WhereClause NVARCHAR(MAX);

  SET @WhereClause = STUFF(CASE WHEN @sexID <> '0' THEN ' AND sexID = @sexID' ELSE '' END +

  CASE WHEN @statusID <> '0' THEN ' AND statusID = @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, 4, '');

  SET @WhereClause = CASE WHEN LEN(@WhereClause) > 1 THEN 'WHERE'+@WhereClause ELSE @WhereClause END;

  SET @sqlquery = ' SELECT * FROM TestMyView '+@WhereClause;

  EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname'

  , @sexID = @sexID, @statusID = @statusID, @firstname = @firstname, @middlename = @middlename, @lastname = @lastname

  END;

  ---

  --Edit--

  ALTER PROCEDURE [dbo].[SearchBiography] @firstname NVARCHAR(50), @middlename NVARCHAR(50), @lastname NVARCHAR(50), @sexID NCHAR(1), @statusID NCHAR(1) AS

  BEGIN;

  SET NOCOUNT ON;

  DECLARE @sqlquery NVARCHAR(MAX);

  DECLARE @WhereClause NVARCHAR(MAX);

  SET @WhereClause = STUFF(CASE WHEN @sexID <> '0' THEN ' AND sexID = @sexID' ELSE '' END +

  CASE WHEN @statusID <> '0' THEN ' AND statusID = @statusID' ELSE '' END +

  CASE WHEN LEN(@firstname) > 0 THEN ' AND firstname LIKE '+CHAR(39)+'%'+CHAR(39)+@firstname+CHAR(39)+'%'+CHAR(39) ELSE '' END +

  CASE WHEN LEN(@middlename) > 0 THEN ' AND middlename LIKE '+CHAR(39)+'%'+CHAR(39)+'%'+@middlename+'%'+CHAR(39)+'%'+CHAR(39) ELSE '' END +

  CASE WHEN LEN(@lastname) > 0 THEN ' AND lastname LIKE '+CHAR(39)+'%'+CHAR(39)+'@lastname'+CHAR(39)+'%'+CHAR(39) ELSE '' END, 1, 4, '');

  SET @WhereClause = CASE WHEN LEN(@WhereClause) > 1 THEN 'WHERE'+@WhereClause ELSE @WhereClause END;

  SET @sqlquery = ' SELECT * FROM TestMyView '+@WhereClause;

  EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname'

  , @sexID = @sexID, @statusID = @statusID, @firstname = @firstname, @middlename = @middlename, @lastname = @lastname

  END;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  September 27, 2013 at 2:35 am

  #1653912

  enriquezreyjoseph (9/27/2013)

  ---

  this is it

  That's because you have declared your statement as varchar. sp_executesql only accepts nvarchar.

  Your

  DECLARE @sqlquery varchar(max)

  should be

  DECLARE @sqlquery Nvarchar(max)

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]

• enriquezreyjoseph

  Ten Centuries

  Points: 1291

  More actions

  September 27, 2013 at 2:47 am

  #1653914

  Thank you guys..

  so, i should change varchar now to my whole table and to my front-end...tsk :-(..

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  September 27, 2013 at 2:51 am

  #1653915

  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.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• enriquezreyjoseph

  Ten Centuries

  Points: 1291

  More actions

  September 27, 2013 at 3:00 am

  #1653918

  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???

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  September 27, 2013 at 3:03 am

  #1653919

  enriquezreyjoseph (9/27/2013)

  ---

  Thank you guys..

  so, i should change varchar now to my whole table and to my front-end...tsk :-(..

  The table can contain varchar columns but the statement must be nvarchar.

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  September 27, 2013 at 3:03 am

  #1653920

  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.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• enriquezreyjoseph

  Ten Centuries

  Points: 1291

  More actions

  September 27, 2013 at 6:13 pm

  #1654234

  Sean Pearce (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 :-(..

  The table can contain varchar columns but the statement must be nvarchar.

  Thank you sean :-)...i never knew that...

• enriquezreyjoseph

  Ten Centuries

  Points: 1291

  More actions

  September 27, 2013 at 6:31 pm

  #1654236

  Cadavre (9/27/2013)

  ---

  Why not build up your WHERE clause like this?

  ALTER PROCEDURE [dbo].[SearchBiography] @firstname NVARCHAR(50), @middlename NVARCHAR(50), @lastname NVARCHAR(50), @sexID NCHAR(1), @statusID NCHAR(1) AS

  BEGIN;

  SET NOCOUNT ON;

  DECLARE @sqlquery NVARCHAR(MAX);

  DECLARE @WhereClause NVARCHAR(MAX);

  SET @WhereClause = STUFF(CASE WHEN @sexID <> '0' THEN ' AND sexID = @sexID' ELSE '' END +

  CASE WHEN @statusID <> '0' THEN ' AND statusID = @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, 4, '');

  SET @WhereClause = CASE WHEN LEN(@WhereClause) > 1 THEN 'WHERE'+@WhereClause ELSE @WhereClause END;

  SET @sqlquery = ' SELECT * FROM TestMyView '+@WhereClause;

  EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname'

  , @sexID = @sexID, @statusID = @statusID, @firstname = @firstname, @middlename = @middlename, @lastname = @lastname

  END;

  ---

  --Edit--

  ALTER PROCEDURE [dbo].[SearchBiography] @firstname NVARCHAR(50), @middlename NVARCHAR(50), @lastname NVARCHAR(50), @sexID NCHAR(1), @statusID NCHAR(1) AS

  BEGIN;

  SET NOCOUNT ON;

  DECLARE @sqlquery NVARCHAR(MAX);

  DECLARE @WhereClause NVARCHAR(MAX);

  SET @WhereClause = STUFF(CASE WHEN @sexID <> '0' THEN ' AND sexID = @sexID' ELSE '' END +

  CASE WHEN @statusID <> '0' THEN ' AND statusID = @statusID' ELSE '' END +

  CASE WHEN LEN(@firstname) > 0 THEN ' AND firstname LIKE '+CHAR(39)+'%'+CHAR(39)+@firstname+CHAR(39)+'%'+CHAR(39) ELSE '' END +

  CASE WHEN LEN(@middlename) > 0 THEN ' AND middlename LIKE '+CHAR(39)+'%'+CHAR(39)+'%'+@middlename+'%'+CHAR(39)+'%'+CHAR(39) ELSE '' END +

  CASE WHEN LEN(@lastname) > 0 THEN ' AND lastname LIKE '+CHAR(39)+'%'+CHAR(39)+'@lastname'+CHAR(39)+'%'+CHAR(39) ELSE '' END, 1, 4, '');

  SET @WhereClause = CASE WHEN LEN(@WhereClause) > 1 THEN 'WHERE'+@WhereClause ELSE @WhereClause END;

  SET @sqlquery = ' SELECT * FROM TestMyView '+@WhereClause;

  EXEC sp_executesql @sqlquery, N'@statusID=@statusID, @sexID=@sexID, @firstname=@firstname, @middlename=@middlename, @lastname=@lastname'

  , @sexID = @sexID, @statusID = @statusID, @firstname = @firstname, @middlename = @middlename, @lastname = @lastname

  END;

  Hi ...the code did not work...see my attachment 😉

• enriquezreyjoseph

  Ten Centuries

  Points: 1291

  More actions

  September 27, 2013 at 8:23 pm

  #1654242

  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

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  September 28, 2013 at 2:36 am

  #1654263

  enriquezreyjoseph (9/27/2013)

  ---

  Can I used this instead of sp_executesql i will use EXEC only??...cadavare

  I'm not sure how many times I have to say "SQL Injection" to you. . . yes you can do what you're saying if you want to be vulnerable to SQL injection which will allow a malicious user to do a lot to your server.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• enriquezreyjoseph

  Ten Centuries

  Points: 1291

  More actions

  October 1, 2013 at 9:54 pm

  #1655109

  Cadavre (9/28/2013)

  ---

  enriquezreyjoseph (9/27/2013)

  ---

  Can I used this instead of sp_executesql i will use EXEC only??...cadavare

  I'm not sure how many times I have to say "SQL Injection" to you. . . yes you can do what you're saying if you want to be vulnerable to SQL injection which will allow a malicious user to do a lot to your server.

  Sorry Poh.... 🙁