|
Help with what? That's just the way that feature works.
Prior to SQL Server 2016 SP1[^], there's no CREATE OR ALTER PROCEDURE statement. And as the documentation[^] says: "The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch." Which means there's no way to do a CREATE / ALTER within an If block. So the only option is to execute a string.
If you're willing to manually change the output, you can reduce the duplication slightly:
DECLARE @statement nvarchar(MAX) = N'/****************************************************************************************************/
...
';
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Usp_Add_AddressCommunication]'))
BEGIN
SET @statement = @statement + N'CREATE ';
END
Else
BEGIN
SET @statement = @statement + N'ALTER ';
END;
SET @statement = @statement + N'PROC [dbo].[Usp_Add_AddressCommunication]
...';
EXEC dbo.sp_executesql @statement = @statement;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am not quite familiar with SQL injection and the different methods one could use to infiltrate one's database code, so I have been using PDO with parameter binding with most of my implementation. I do find it a bit clumsy and long sometimes and am tempted to just skip it.
So my question is - what circumstances can I NOT use parameter binding without worrying about possible hacks - for less critical information?
And the flip side - when should really use it? For more sensitive information only like user emails, id's? or?
Would appreciate some feedback or links for further reading.
modified 17-Jun-18 22:31pm.
|
|
|
|
|
You should always use parameter binding.
One exception only, when there are no parameters.
<edit>After reading Mycrofts answer I realize I should change my second sentence to: One exception only, when the parameter is a constant.  </edit>
modified 25-Jun-18 7:23am.
|
|
|
|
|
I'm not as fanatical as Jorgen, there is no circumstance where you should NOT use parameter binding. However there are circumstances (most) where you MUST use parameter binding. If a user or application has anything to do with the values then you MUST use parameters.
The only time I would risk not using parameters is when there is no external input of the values.
An example of where I will risk string queries would be if you have a master table of countries with an Active attribute field. I might use select * from Country where ActiveFlag = 1 from my BL layer.
But seeing as I have a code generator that automatically builds the DAL, model and viewmodel code for me I ALWAYS use parameters and stored procedures
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To be fair, SQL Injection can only occur where the user has influence over the parameter. If your parameter is a constant, or provided by you, there's little risk since the user cannot change the statement that is going to be executed.
..but, parameterized queries are not done "just" to prevent SQL injection, they provide more benefits. While it may take you a minute longer to write it, it makes the code a lot more readable, improving maintainability. There's also an impact on performance[^] if you re-execute the query.
That's not something that every hobbyist wants to learn, so the advice has become that it is NEVER OK to NOT use them. I agree with that advice, since adding the code will not have a downside.
needAbreakNow wrote: I do find it a bit clumsy and long sometimes and am tempted to just skip it.
This will sound rude but that's just being lazy. If it is worth doing at all, it is worth doing it correctly.
Upvoted for asking "why am I doing this"; it proves you are actually thinking about what you are doing, and trying to find alternatives.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
needAbreakNow wrote: and am tempted to just skip it
Versus what? If you create a composition then that can be rather complicated as well.
Perhaps you are comparing it to simple cases like when you want to add just one parameter?
If so I would say that consistency of usage overrides the ease of one-off cases.
|
|
|
|
|
I'm trying to fetch the previous and next record using PDO/MYSQL. The SQL code for Next fetches the next record, but for Previous always returns the first record. Don't understand why Previous does not work. There are 7 records in this test set and the 'article_id' field is not contiguous (due to deletions) but is broken up as such: 24, 45,46,47, 48, 50, 51. Code below:
$id = 47;
echo 'current id is:'.$id;
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id<$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '<br>';
echo 'Previous ID is:'.$row['article_id'];
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id>$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '<br>';
echo 'Next ID is:'.$row['article_id'];
The resultant output is:
current id is:47
Previous ID is:24
Next ID is:48
The previous ID should be 46, not 24!
I've tried the same SQL in phpMyAdmin directly and it still returns 24 for the previous, but works fine for the next. I've seen many other examples on the web showing the similar code as I have, but theirs seems to work (via youtube), mine doesn't. Any help would be appreciated!
Thanks in advance.
|
|
|
|
|
I am not a SQL expert, but the following occurs to me. Your SELECT statement says: find all records whose article_id is less than the value given (i.e. 47), and return the first record found. So SQL starts searching the table at the beginning, checks the first record and its id is 24, and that matches your search criteria. In order to do what you want you need somehow to tell it to find the record with the highest id that is less than 47. But I am not sure how you would specify that.
|
|
|
|
|
To extend Richard's reply:
you may want to refine your SQL query using ORDER BY clause or/and MAX / MIN functions.
|
|
|
|
|
Solved the issue, and thanks for the hints!
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id<$id ORDER by article_id DESC LIMIT 0,1");
It seems like the for the next record, the system naturally assumes ascending, but for the previous, I had to put in the DESC order in!
|
|
|
|
|
I think that Ascending is the default order for all SELECT clauses.
|
|
|
|
|
What is the underlying software or language used by Argue Commercial Valuation?
|
|
|
|
|
|
Google for the following. Select computer and search. Create an account and look at full job descriptions. Should provide an idea.
"Altus group" jobs
However the functionality can be implemented in a variety of different ways. The specific technology does not limit it.
|
|
|
|
|
I am looking for a programming expert to assist in the creation of a commercial real estate software package I am designing. Where is the best place to post a description of the skills I am looking for in a developer, and finding the absolute best?
Thank you for any guidance you can provide.
|
|
|
|
|
Monster.com, Freelancer.com - but not here. We are not a recruitment site, and take a dim view of what appear to be recruiters trolling for bodies ...
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
|
I got a chuckle from Swinkarans suggestion, the after learning part seems to imply junior developers, not "the very best"
I have built and supported RE apps in the past and know how big a job it is, what makes you think your solution is going to be better than the existing apps. Feel free to contact me via PM, if you are just another recruiter I get the chance to be rude, otherwise it may be useful to discuss it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: what makes you think your solution is going to be better than the existing apps.
That of course never stopped anyone from creating competing products in any industry. As long as they have the money and the sales expertise then it only requires a bit of care to actually create a product. If they don't have the first two then it will never fly at all. And without the second it can provide employment for a while but will very likely fail.
|
|
|
|
|
|
Hi,
I have SQL Server 2012 on my machine, I have developed an SSIS Package that I could able to run on locally in debug environment and deployed on SQL Server 2012 Server as well. But the Test and Staging Servers are 2008 R2, when I tried to deploy the Package on the 2008 R2 servers I am getting the following error, any help would be greatly helpful, thanks in advance.
<h2>TITLE: Import Package</h2>
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
<hr />
ADDITIONAL INFORMATION:
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
<hr />
BUTTONS:
<h2>OK</h2>
..==================================
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
And when I queried to see the actual error of the Job I am getting following error message:
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:11:14 PM Error: 2018-06-04 14:11:14.23 Code: 0xC0011007 Source: {12A3883D-DB4A-4085-A76A-DFB45C939490} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2018-06-04 14:11:14.23 Code: 0xC0011002 Source: {12A3883D-DB4A-4085-A76A-DFB45C939490} Description: Failed to open package file "C:\Users\ssubrama\Desktop\CFRS_Provider_2.dtsx" due to error 0x80070005 "Access is denied.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format. End Error Could not load package "C:\Users\ssubrama\Desktop\CFRS_Provider_2.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Users\ssubrama\Desktop\CFRS_Provider_2.dtsx" due to error 0x80070005 "Access is denied.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format. Source: {12A3883D-DB4A-4085-A76A-DFB45C939490} Started: 2:11:14 PM Finished: 2:11:14 PM Elapsed: 0.125 seconds. The package could not be found. The step failed.
The job failed. The Job was invoked by User DHSINTRA\SSubrama. The last step to run was step 1 (OPSExtract).
Any help would be very great, thanks in advance buddies - thanks a lot.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 4-Jun-18 18:23pm.
|
|
|
|
|
Very few software applications are forwards compatible, so what you are doing, 2012 => 2008 is probably not going to work.
The error messages are probably invalid/random as there will almost certainly be a compatibility error in the package.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey guys!
I can't find a section on where to ask about html questions but I have been following this tutorial by mmtuts:
16: How to Create HTML5 Videos and Embed Videos | Learn HTML and CSS | HTML Tutorial | Basics of CSS - YouTube[^]
I am wondering if someone could check to see if the code is working because for some reason, I can' seem to get it to show the correct width and height..
Is it possible to copy sniptool here because I can't get seem to paste it..
My apologies.. I forgot to include my stylesheet... is there still a way to paste images from sniptool?
|
|
|
|
|
YouTube videos include a comments section below them so you can talk directly to the author, who will know a lot more about the subject than we will - we'd have to follow the whole "tutorial" to work out what you might possibly have done wrong (assuming the video doesn't miss stuff out, and most do).
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
