|
I'm repeating myself here:
Don't use string concatenation to build a dynamic SQL query. Your code will be susceptible to SQL Injection[^].
If you really need to use a dynamic query, use sp_executesql[^] to execute it:
CREATE Procedure sp_EmployeeSelect
@EmployeeName nvarchar(50)
AS
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery = N'SELECT * from Employee Where (1=1)'
If @EmployeeName <> ''
Set @SQLQuery = @SQLQuery + N' AND (EmployeeName LIKE N''%'' + @EmployeeName + N''%'')'
Exec sp_executesql @SQLQuery,
N'@EmployeeName nvarchar(50)',
@EmployeeName
However, in this case, as with all of your QA questions, you don't need a dynamic query:
CREATE Procedure sp_EmployeeSelect
@EmployeeName nvarchar(50)
AS
SELECT
*
FROM
Employee
WHERE
@EmployeeName = N''
Or
EmployeeName Like N'%' + @EmployeeName + N'%'
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello everyone,
Can someone help me select rows between 2 dates en between 2 times.
I need to select all records from 02/09/2014 starting from time 05:00:00 until the next day time until 05:00
If I do it like this then it wont work, no records shows:
SELECT TOP 10000 *
FROM Staging.[dbo].AD
WHERE DATE_CREATED BETWEEN '02/09/2014' and '03/09/2014'
AND Convert(Time,TIME_CREATED) between '05:00:00' AND '05:00:00'
It should be something like this:
Between DATE_CREATED 02/09/2014, TIME_CREATED 05:00:00 and DATE_CREATED 03/09/2014, TIME_CREATED 05:00:00
Kind regards,
Ambertje
|
|
|
|
|
SELECT TOP 10000 *
FROM Staging.[dbo].AD
WHERE DATE_CREATED BETWEEN '02/09/2014 05:00:00' and '03/09/2014 05:00:00'
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Thank you for the reply but this is not working, it's showing me all the records for both days, not the records between 05:00:00 and 05:00:00
|
|
|
|
|
What datatype is time?
I'd recommend putting it in the DATE_CREATED column, where it belongs.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Why are you using nvarchar instead of DateTime?
|
|
|
|
|
Yes, kinda predictable.
It's a bad idea to do so, and should be fixed. The time you are saving is a culture-specific format, it is a text, something the computer does not calculate with.
A DateTime in a computer is a floating point. The integer-part counts the days passed since the epoch (start of counting of days, often 1/1/1900), the decimal part represents the time, in ticks. They are not two separate facts - and should be modelled as a single field, of the DateTime-datatype. The computer can easily calculate with those.
Breaking the date and time into separate fields is as usefull as using a separate field for the day, month, year, hour, minute and second. If they represent a single atomic fact, than that is how it should be modelled.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
It's interesting that the Date type in Oracle while handled as a single entity but is stored internally as seven bytes. One byte each for year, month, day, hour minute, second and fraction of a second.
It's a space waster, but oh so fast to calculate with.
Timestamp on the other hand is stored as a floating point to save space.
|
|
|
|
|
Internally, yes, if the engine expects it. But still no way to model a database.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
If date and time should not be keep separate, why did MS create data formats DATE and TIME? 
|
|
|
|
|
Because there are times when you do need to store just a date or just a time. The OP's example just isn't one of them. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You could try and merge DATE_CREATED and TIME_CREATED to get a DATETIME value then you could use between.
Something like (most likely will not work as written):
(Date_Created + Time_Created) BETWEEN @startdatetime AND @enddatetime
|
|
|
|
|
As others have said, the time should be part of the DATE_CREATED column. However, it's still possible to get what you need:
WHERE
(DATE_CREATED = '20140902' And Convert(time, TIME_CREATED) >= '05:00:00')
Or
(DATE_CREATED = '20140903' And Convert(time, TIME_CREATED) <= '05:00:00')
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you so much for the help, my query is working fine now.
|
|
|
|
|
That is great!
I get started down a path and do not think outside of that. 
|
|
|
|
|
convert function is not working in access
|
|
|
|
|
select * from Staging.[dbo].AD where DATE_CREATED between '2013-03-08 05:00:00' and '2013-03-09 05:00:00'
-- I hope i will help u...
|
|
|
|
|
I need to send an email from a stored procedure, I found I use msdb.sp_send_dbmail, can someone give a suggestion on how I can do it except calling this built-procedure in sql server? Thanks.
|
|
|
|
|
I use sp_send_dbmail - I can't think of any other methods provided by sql server which will do this.
For security reasons I have the stored procedure, which runs this, within the msdb database as certain permissions, which I am unwilling to give to users, are needed to run this stored procedure.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
thank you very much for your reply.
Can you tell me what the best practice is for notifying an developer if an error occurred in a stored procedure?
|
|
|
|
|
Just throw the exception.
There is no reason, I can think of, that you should need to catch an exception from a stored procedure.
Throwing the exception will allow the developer to have an error message and pursue fixing the error.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Member 1284721 wrote: Can you tell me what the best practice is for notifying an developer if an error occurred in a stored procedure?
What happens if the stored proc doesn't run at all?
|
|
|
|
|
The sproc is there to provide the functionality.
If it is there, and works, and you need such, why ask for "any" solution that excludes it? Why "except"?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
