|
Hi Everyone,
Ive got a Small Sql Server DB which Holds 3 Variables that i want To Update But Not Necessarily togther,Meaning: One time i want to update the first two Variables, the other one ill need to update the other 2, which leads me up to 9 options for updating queries..
Now my question is.. is there any way to write 1 query (With Stored Procedure/not) that somehow ,someway "sees" that if no parameter was sent to update , it will use a default value which ill define ?
I hope i explained myself well
thanks alot !
|
|
|
|
|
I'm not sure that I completely understand what you're trying to do, so this may not be what you're looking for. You can supply a default for each parameter to a stored procedure where the parameters are declare at the top of the procedure, but simply putting an equal sign ( = ) after the parameter's data type and then putting the value; like so:
create procedure dbo.DoSomething
@param1 int = -1,
@param2 datetime = getdate(),
@param3 char(1) = 'M'
as
....
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks For your reply Ben.
thats a half of what i ment,
I'll try to rephrase myself.. is there any way i can check if a null variable has been sent into the query and to give another value to it in the stored procedure?
For example: TextBox1 Contains 1 Variable.. when i send it empty (and sometimes i need it empty) i want to give it another value.
Thanks!
|
|
|
|
|
Try something like this (not necessarily correct code, just off the top of my head)
create proc1 (@var1 int = null, @var2 int = 1) -- this will default @var1 to null and @var2 to 1
as
update table1
set col1 = coalesce(@var1,99), --set to 99 if no @var1 supplied
col2 = coalesce(@var2,col2) -- set to its current value if @var2 is null
Hopefully this will guide you to what you want.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
What I understood from description is you want to check whether the variables provided are null or not. If any of these is null put default value. If is that so, below is the answer:
CREATE PROC sp_TestSp (@var1 int = 0, @var2 int =0 , @var3 varchar(50) = 'Default') -- this will set default value of @var1 to 0 and @var2 to 0 and @var3 to "Default"
AS
UPDATE table1 set col1 = @var1, col2 = @var2, col3 = @var3
OR
CREATE PROC sp_TestSp (@var1 int = 0, @var2 int =0 , @var3 varchar(50) = 'Default') -- this will set default value of @var1 to 0 and @var2 to 0 and @var3 to "Default"
AS
UPDATE table1 set col1 = ISNULL(@var1, 0), col2 = ISNULL(@var2, 0), col3 = ISNULL(@var3, 'Default')
Hope this answers you question.
Umair Feroze
|
|
|
|
|
Why repeat basically what I put 7 hours ago?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Alot
thats exactly what i wanted!
|
|
|
|
|
hello anyone tried to install SQL 2005 Express with Reporting Service on Vista Home Premium?
It's driving me nuts...[^]
I checked and double checked,
1. IIS installed, ASP/ASP.NET enabled, IIS6/IIS6 Metabase ...etc all there.
2. BEFORE install SQL 2005 Express, I made sure this patch is installed.
Update for Windows Server 2008 (KB950636)[^]
3. I made sure this is intalled AFTER SQL 2005 Express installed and BEFORE I try to reinstall to renable "Reporting Service" - Microsoft SQL Server 2005 SP2[^]
Nothing seems to work.. installer still stuck complaining IIS not installed. Perhaps home edition Vista is inherently incompatible with SQL2005 Reporting Service.
dev
|
|
|
|
|
You were able to run IIS on Vista Home Premium? I have had success with Express on XPpro and Vista Ultimate edition only.
Why are you running a server update on a Vista box?
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
One of our clients has just developed a set of coding standards for their .NET and SQL Server application development. For the SQL Server standards, they say the following regarding error handling:
-----------start of quote---------------
Error Handling
- Should be used in every stored procedure, with an Error Handling section
at the bottom of the procedure
- Stored procedures should return the success or failure of the stored
procedure by returning 0 (zero) upon success, or return the error number upon
error.
- @@ERROR should be checked after Every INSERT or UPDATE to deterimine the
success or failure of that action.
- @@ROWCOUNT should be checked after every INSERT or UPDATE if at least one
or more records were expected to be affected.
DECLARE @Error int, @RowCount int
T-SQL here
RETURN 0
EH:
RAISERROR(@Error, 16, 1)
RETURN @Error
-----------end of quote---------------
Does this sound like a good idea? Having an error handler in every stored procedure seems excessive.
Honestly, I'm of the opinion that error handling for the sake of error handling is a waste of time and only serves to obsficate your code. The only reason why you should handle an error is if you're going to do something meaningful with the exception. For example, you know what the error is and how to fix it. Or maybe you want to log it to the database. Or you need to rollback a transaction. But if all you're going to do is catch it and raise it up the call stack, that's pointless code because the error will bubble up the callstack anyway.
Do you agree or disagree? Why or why not?
|
|
|
|
|
Seems pretty much like the standards everywhere I've contracted in the last few years. If you get an error you can enhance the message to help track down the problem, so no, I don't think its a waste of time. My biggest gripe is that you can't (pre 2005) trap ALL errors so you can handle them. I've spent hours trying to track down such elusive messages as 'string or binary data would be truncated' 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
> If you get an error you can enhance the message to help track down the problem
What sort of information would you include to enhance the error message? For example, what would you change about this stored procedure?
--air code
CREATE PROCEDURE GetCustomerCount
AS
SELECT COUNT(*) FROM CUSTOMERS
RETURN 0
EH:
RAISERROR(@Error, 16, 1)
RETURN @Error
|
|
|
|
|
Obviously, in this example there is nothing, but if you have anything more complex, for example a proc which has several update statements, then it is useful to know where the error originated. Another example would be a series of nested procs - by including the proc name in the error you know where it originated.
As with any set of standards there are cases for and against - for example, stylecop in c# insists that the using statements come after the namespace declaration, but if you create a new class they are generated before.
As a long time contractor I have worked at many companies, they all have slightly different standards, some good, some not so good, but at the end of the day they are called standards for a reason - consistency. The overhead they cause is minimal and they make it easy for someone to pick up the code and see what is happening.
If you think the standard is not good you need to raise it with the powers that be, but in your original post you state "One of our clients..." and at the end of the day they call the tune.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
A lot of stored procedures are simple, one-statement CRUD operations that probably don't require error handling, which is why I think that mandating that every single sproc have an error handler is excessive.
These coding standards are brand new. If we get the contract, this will be the very first project to use them. So, consistency with existing applications is not an issue.
|
|
|
|
|
Its up to you and your client. I still maintain they are called standards for a reason.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Sure, I believe in standards too. In fact, my standards that I used on the last project are actually more strict. But unless someone can come up with a valid justification, this particular standard is a bad one.
|
|
|
|
|
Wait a second! Does this error handling strategy even work? When I execute the following stored procedure, I get an error on the RAISERROR statement.
CREATE PROCEDURE dbo.spErrorHandlingTest1<br />
AS <br />
BEGIN<br />
<br />
DECLARE @Error INT<br />
DECLARE @myInt INT<br />
<br />
SELECT @myInt = 1/0<br />
<br />
SELECT @Error = @@ERROR<br />
<br />
IF @Error > 0 GOTO EH<br />
<br />
RETURN 0 <br />
EH: <br />
RAISERROR(@Error, 16, 1)<br />
RETURN @Error<br />
<br />
END
Msg 8134, Level 16, State 1, Procedure spErrorHandlingTest1, Line 8
Divide by zero error encountered.
Msg 2732, Level 16, State 1, Procedure spErrorHandlingTest1, Line 16
Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
|
|
|
|
|
I think it works as expected. Since you are passing forward the original error (8134) as message id in a custom RAISERROR, you'll have trouble. From Books Online:
msg_id
Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
|
|
|
|
|
You mean RAISERROR is working as expected and the calling code is incorrect, right?
|
|
|
|
|
Had to re-read my post and yes that's exactly what I meant... and yes the answer was confusing since I didn't point out what I was referring to. Sorry about that 
But the problem is that you're using reserved error numbers in custom RAISERROR call. If you change the first argument to for example 50001 you'll see it behaves differently.
Mika
|
|
|
|
|
Sorry to revisit an old thread, but I happened to stumble across an article that relates to this topic. According to Microsoft's Design Guidelines for Exceptions:
"Do not overuse catch. Exceptions should often be allowed to propagate up the call stack."
http://msdn.microsoft.com/en-us/library/ms229005.aspx
Of course, this guideline is in reference to .NET code and not SQL, but it's still good advice.
|
|
|
|
|
I'm using DAAB (Enterprise Library) for accessing database. My problem is I can't read output parameters when using DataReader. That is I can only read stored procedure's output parameter only when I use ExecuteNonQuery or ExecuteScalar functions. When I use ExecuteReader function, the output parameter is 0. Do you know how I can read output parameter when using ExecuteReader function?
here is a sample code:
var db = new SqlDatabase(ConnectionString);
string sqlCommand = "ReadItems";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "@Count", SqlDbType.Int, count);
db.AddOutParameter(dbCommand, "@Total", SqlDbType.Int, sizeof(Int32));
var reader = db.ExecuteReader(dbCommand);
while (reader.Read())
{
}
int total = Convert.ToInt32(dbCommand.Parameters["@Total"].Value);
|
|
|
|
|
You need to close the reader before you can get the output parameters. No idea why, it has always been this way.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Does anyone know if it is possible to supply multiple queries and tables to exp - so that the result is a single export file ?
e.g. something like
exp USER/passwd file=db.dmp tables=a,b,c query=[different restictions on tables a, b and c]
?
thanks !
|
|
|
|
|
Sounds like Oracle.
If it is, you can specify multiple tables for exp, but only single query. When used this way, the query must be applicable to all the tables specified.
If the version of Oracle you use is 10g or higher, you should use Data pump. It's capable of exporting several tables with different queries.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
