|
What is the proper syntax of an if statement ina stored procedure in MS SQL please?
thanks
|
|
|
|
|
|
|
IF ReadManual = FALSE OR UseGoogle = FALSE
BEGIN
Waste time posting on CodeProject
END
ELSE
BEGIN
No need to post - Yipee!
END
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi everyone,
I am getting a bizzare message when I try running a stored procedure on SQL Server 2000 Query Analyzer. The message is "Formal parameter '@ACTION' was defined as OUTPUT but the actual parameter not declared OUTPUT".
Now, what I don't get is - the '@ACTION' parameter is NOT an output parameter. This is how the parameter is defined in the stored procedure header:
CREATE PROCEDURE [GV_SUPP_VALUATION_EDIT]
@ACTIVE_USER_ID INTEGER = 0,
@VALUATION_ID INTEGER = 0,
.
.
.
@ACTION VARCHAR(50) = 'SAVE',
@ERROR VARCHAR(500) = '' OUTPUT
AS
PRINT 'PROCEDURE [SDE].[GV_SUPP_VALUATION_EDIT]'
As you see from the code above, only the @ERROR parameter is defined as output. Why then am I getting this error? 
Your assistance with this is highly appreciated 
|
|
|
|
|
Based on your post, I can't tell what's happening. I copied and pasted your snippet and it runs fine (see below). Maybe you could post the entire header along with the code you're using to call the procedure it might be easier to help figure out what's wrong.
Here's what I tried:
<br />
CREATE PROCEDURE [GV_SUPP_VALUATION_EDIT]<br />
@ACTIVE_USER_ID INTEGER = 0, <br />
@VALUATION_ID INTEGER = 0,<br />
@ACTION VARCHAR(50) = 'SAVE', <br />
@ERROR VARCHAR(500) = '' OUTPUT<br />
AS<br />
PRINT 'PROCEDURE [SDE].[GV_SUPP_VALUATION_EDIT]'<br />
go<br />
<br />
DECLARE @ERROR VARCHAR(500)<br />
EXEC [GV_SUPP_VALUATION_EDIT] 0, <br />
0,<br />
'SAVE', <br />
@ERROR OUTPUT<br />
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Hi,
Thanks for your reply  I just figured out what I'd done wrong! 
|
|
|
|
|
Hey everyone,
This is so embarassing! The message was actually correct, I was passing a non-output parameter as an output parameter. What prevented me from picking this up is the number of parameters I was passing, the 'edit' stored proc has 28 parameters - @ACTION is the 27th. My mistake was that I wasn't passing the 24th parameter, which is optional anyway.
This actually has made me favour the practice of using the syntax @param1=param1, @param2=param2, ... , @paramn=paramn - when calling stored procedures. I mean, if I had done this, then I wouldn't have suffered for 3 hours because of an optional parameter!
By the way, it's a great site you guys have here - keep up the good work!
|
|
|
|
|
I am using a Access database, and I am wanting to delete a record that a user selects, from a datagridview. The sql statement looks like this
command.CommandText = "DELETE FROM BankAccount WHERE ID = "'+dataGridView1.Rows[0].Cells[0].Value+ '";
I keep getting a datatype mismatch error. I am unsure of what type the Autonumber is, whether its an int, double etc. Is there any other way to delete a record, using an autonumber in the WHERE clause, or is it mayb just a simple problem and I am unaware of
|
|
|
|
|
try this
command.CommandText = "DELETE FROM BankAccount WHERE [ID] = "+Convert.ToInt32(dataGridView1.Rows[0].Cells[0].Value.ToString());
but be aware of SQL Injections
I Love T-SQL
|
|
|
|
|
Quinton wrote: I am unsure of what type the Autonumber is
AutoNumbers in Access tables are long integers.
|
|
|
|
|
Hi there,
i have an application developed for XP in VS2003 which uses Microsoft.ApplicationBlocks.Data.dll. Now i wish to migrate the application to vista and for that i first wich to migrate my whole code to VS2005. i just wanted to know whether Microsoft.ApplicationBlocks.Data.dll is compatible with VS2005 or if not then what are the workarounds
Best Regards,
Mustanseer
|
|
|
|
|
Download the .NET 2 version and find out. Rather than waiting for others to stumble on this thread, you could have found out by yourself and actually tried it.
|
|
|
|
|
I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:
<br />
Select<br />
DisplayColumns...<br />
From<br />
InterestingTables<br />
Where<br />
(Filter1) --W1<br />
AND --W2<br />
NOT --W3<br />
(Filter2) --W4<br />
Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?).
Now, say SetF1ExF2 is the result set of the sql script above.
Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3.
Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2
I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.
TIA.
I am a SysAdmin, I battle my own daemons.
|
|
|
|
|
Hi,
Do you think you could phrase your question better maybe? I'm not sure I understand what you're asking 
|
|
|
|
|
Hi,
I would like to buy a license for Imceda SQL IDE.
|
|
|
|
|
That's excellent. Good for you. It's nice to know you're being so honest, but you do realise that this isn't Imceda don't you? If you google Imceda you'll find out where to go.
|
|
|
|
|
I have one major problem Regarding Database Updation...Basically We are dealing with BillPament Services. Yesturday we had a Problem in which all the request from the customer through billpament terminal is not updated in the database. Bt it is actually updated in the SQL lOG File due to which response get generated from LOG file itself without being data actually physically updated in the database,,
Data is first written in the SQL log First and than physically to the
database ...
My problem is that what could b the possible reason for data written in SQL Log bt not written in the database table phisically....Its Urgent
|
|
|
|
|
A couple of things - read the forum rules, Urgent is not a valid subject title!
This can be caused by many things, primarily hardisk or log problems. I doubt you will get any satisfaction from a forum, I would recommend getting a DBA consultant to look into your issues.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you had a uncommitted transaction you would see exactly that. A transaction in the log but no committed data.
|
|
|
|
|
|
I like to use identity columns.
1) They are resilient to data changes - like a spelling mistake. I dislke cascading changes - just a personel problem on my part.
2) Joins are usually faster because the keys are usually smaller. More keys per index page with an integer index than char based index.
3) I feel better when there is a single column that uniquely identifies a row. It makes me more confident that the correct row will be deleted/updated without accidental carryover due to a mistake in programming (i.e. leaving out one of the columns in a multi-column PK).
I do break this rule when I have a many to many relationships. I find it silly to create an identity for a table where the only columns contain FKs that represent two other tables' identities.
|
|
|
|
|
Another issue caused by using natural keys instead of surrogates (identities) is index fragmentation. Because identities are sequential there are less page splits required for inserts on clustered indexes (as well as other indexes). When you use a sequential identity the new record is appended to the "end" of the index. This certainly still causes page splits when the leaf nodes must balance the tree, but compare this to when you use a natural key the values you are inserting are almost always being inserted somewhere in the middle of a range of leaf nodes.
If your natural key is used a foreign key and you need to insert a large number of child records, then you're inserting a large number of records right in the middle of your index. Causing more fragmentation.
As your database grows in size this will increase the amount of maintenance your indexes will require to constantly rebuild and defragment them to maintain performance.
That brings us to read performance. With a surrogate key the data you need most often is physically adjacent. Think of reporting on orders or events which all occured w/in a specified time period. Yes you could use a clustered index on a date column, but that means 8 bytes of storage for each entry before you get to the leaf nodes - twice what's required for an INT. But with a natural key your data would be all over the map. Disk I/O is the worst bottleneck in your entire system. Imagine how much more work the disk heads need to work when the data is randomly placed, compared with scanning data in adjacent sectors.
You can use more padding on your indexes to reduce fragmentation and increase performance for modifications but this increases the I/O required to read your index on top of the fact that the natural key has already increased the size of your index in the first place.
And that is just the performance reasons for using surrogate keys. There are other arguments against natural keys.
In case you hadn't guessed, I don't think natural keys should ever be used - with one small exception. If we're talking about a very small set of possible keys which are tied to some sort of lookup data. The reason for this exception is that they are the exception to the performance problems cited above. Additionally, by using natural keys for a small dataset you can often store only the key in the child tables and use some sort of enumeration in your application code. This means you'll have less overhead because you won't have to maintain tables for all the different lookup types in your application.
Hope this helps your delimma. Of course you will need to decide what works best for your requirements and weight them to determine what the most important factors are in developing your application.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
Just a small point on clustering tables.
You probably know this but if you cluster tables that have a high rate of delete/insert/update transactions on them - clustering may not be a good idea with a very large table.
If you have to cluster then an identity index can make sense due to the insertions being added at the end.
Just bear in mind that OLTP heavy tables do not always lend themselves to clustering.
Hopefully I'm not teaching my grandmother to suck eggs too much here...
You always pass failure on the way to success.
|
|
|
|
|
I have a DateTime column in my SQL database with a regular entry looking like this: 2008/03/01 08:57:06 PM
Now when I want to use a calender to be able to choose between what dates I must find specific entries, it gives me no result. Is that because of a clash between what the calender is asking for and what the database is saying?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
