SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can You make this code Shorter??..


Can You make this code Shorter??..

Author
Message
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1764 Visits: 3432
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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3880 Visits: 8472
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

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


Craig Wilkinson - Software Engineer
LinkedIn
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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...
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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 Wink
Attachments
error2.JPG (10 views, 126.00 KB)
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3880 Visits: 8472
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

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


Craig Wilkinson - Software Engineer
LinkedIn
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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.... :-(
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search