Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Can You make this code Shorter??.. Expand / Collapse
Author
Message
Posted Friday, September 27, 2013 3:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 916, Visits: 2,881
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

@SeanPearceSQL

About Me
Post #1499265
Posted Friday, September 27, 2013 3:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,379, Visits: 7,564
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.



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1499266
Posted Friday, September 27, 2013 6:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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...
Post #1499622
Posted Friday, September 27, 2013 6:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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 ;)


  Post Attachments 
error2.JPG (7 views, 126.18 KB)
Post #1499624
Posted Friday, September 27, 2013 8:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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
Post #1499630
Posted Saturday, September 28, 2013 2:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,379, Visits: 7,564
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.



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1499658
Posted Tuesday, October 1, 2013 9:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 79, Visits: 191
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.... :-(
Post #1500647
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse