|
I am using the following ocde to loop through existign records and create account summart record for every client but it seems looping with no end and it's creating an endless account summary records for the first client only.. what's wrong?
USE takhlees
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
DELETE FROM accounts_summary
-- reset primary key
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000001, 'CASH', 'True', 'True')
INSERT INTO accounts_summary (account_number, account_name, core_account, is_read_only) VALUES (10000000002, 'ACCOUNTS RECEIVABLE', 'True', 'True')
DECLARE @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)
DECLARE account_numbers CURSOR FOR
SELECT clients.client_id, clients.client_cpr, clients.client_name
FROM clients
ORDER BY clients.client_name
OPEN account_numbers
FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, SCOPE_IDENTITY(), @client_name)
-- get random from 1 to 7
SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
-- get random from 1 to 365
SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), SCOPE_IDENTITY()), 7)
SET @account_number = convert(bigint, @account_number_string)
UPDATE accounts_summary SET account_number = @account_number WHERE account_id = SCOPE_IDENTITY()
UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END
CLOSE account_numbers
DEALLOCATE account_numbers
|
|
|
|
|
does it have anything to do with this:
WHILE @@FETCH_STATUS = 0
|
|
|
|
|
DECLARE @rec_id int, @client_id int, @client_cpr varchar(50), @client_name varchar(255), @account_number_string VARCHAR(50), @account_number numeric(38,0)
DECLARE account_numbers CURSOR FOR
SELECT clients.client_id, clients.client_cpr, clients.client_name
FROM clients
ORDER BY clients.client_name
OPEN account_numbers
FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO accounts_summary (client_category, client_id, cr_cpr_number, account_number, account_name) VALUES ('I', @client_id, @client_cpr, 0, @client_name)
SET @rec_id = SCOPE_IDENTITY()
-- get random from 1 to 7
SET @account_number_string = RIGHT('0' + convert(varchar(1), DATEPART(WEEKDAY, GETDATE())), 1)
-- get random from 1 to 365
SET @account_number_string = @account_number_string + RIGHT('000' + convert(varchar(3), DATEPART(DAYOFYEAR, GETDATE())), 3)
SET @account_number_string = @account_number_string + RIGHT('0000000' + convert(varchar(7), @rec_id), 7)
SET @account_number = convert(bigint, @account_number_string)
UPDATE accounts_summary SET account_number = @account_number WHERE account_id = @rec_id
UPDATE clients SET account_number = @account_number WHERE client_id = @client_id
FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END
CLOSE account_numbers
DEALLOCATE account_numbers
|
|
|
|
|
Dammed if I can see any problem with your cursor. Try breaking it down to just print the @client_id for each loop to make sure it is performing correctly.
I have an issue with you using the @account_number as a FK between client and account. You should be using the @rec_id as the FK on the client table. Keys should be stupid, you know no intelligence and you are using a formatted string as the key instead of the identity value - bad design!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i did PRINT and it's just printing one record# 25
|
|
|
|
|
Try just this
DECLARE account_numbers CURSOR FOR
SELECT clients.client_id, clients.client_cpr, clients.client_name
FROM clients
ORDER BY clients.client_name
OPEN account_numbers
FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Client_ID
FETCH NEXT FROM account_numbers INTO @client_id, @client_cpr, @client_name
END
CLOSE account_numbers
DEALLOCATE account_numbers
Also make sure your select statement is returning what you think.
|
|
|
|
|
I've had problems with cursors not working correctly, or, telling me the cursor is already open.
Can you change from using a cursor to using a temporary table or table variable?
Insert the select data with an identity field into the temporary table/table variable and
then create a loop to process each record.
Hope it helps.
Tim
|
|
|
|
|
Could it possibly have anything to do with using GETDATE() to construct 'random' portions of the string? Each pass through the loop will render the same values until the server system clock flips to the next day. It seems to me you will always get the exact same value for each pass of the loop until the next day arrives. I also don't understand your use of the RIGHT() function, perhaps it is some odd way of padding with 0's. In each case it seems you concatenate 000's + some value and then pull the 'some value' you just concatenated from the 000's; why not just use the values themselves and not clutter it up by concatenating the 000's and values together only to turn around and take the values off? If you need to pad try using the REPLICATE() function, as it will be easier to read:
DECLARE @hold varchar(50)
SET @hold = convert(varchar(7), SCOPE_IDENTITY()) -- or @recid
SET @hold = REPLICATE('0', 7 - LEN(@hold)) + @hold -- pad to desired length of 7
-- REPLICATE('0', 0) does not produce an error
Also if you truly need to get a random value try using the NEWID() function which will return a random GUID.
Hold on a second here... Don't you think you might be putting the horse ahead of the cart?
|
|
|
|
|
I tried to debug a stored procedue in VS 2005 as mention in this^link.
Howver, when I tried to select, 'Step into Stored Procedure', I am getting an message in the output window saying that ' Cancelled by User'. Not sure what is happening. Any idea?
I even tried to give admin rights by executing the sp_addsrvrolemember sp in master db. This returns the message, 'Command(s) completed successfully'. Hope it has given the admin rights.
But still I am getting the same message as above, not able to debug the stored procedure. Any idea what is happening? I am using VS 2008.
Thanks
meeram395
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
I got the fix. I don't have the rdbgsetup.exe in 1033 folder, even though 1033 folder is present. Do I need to reinstall this or is it available for free download from net? I tried in google, but didn't get anything such.
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
Hi all,
I want a trick as to how to add multiple values for a single value .
For example :
A Person named "A" has two cars "Car1" , "Car2"
How do I design the database for this ??
|
|
|
|
|
You have a table Person keyed say on PersonId.
You also have a table Car, keyed on CarId. Also in this table you have a PersonId fogeign key to the first table.
Each Person can then own multiple cars, and to see which you need to do a query with a 'join'.
Regards,
Rob Philpott.
|
|
|
|
|
Hello,
Rob Philpott has the right idea. Do not forget to index the combination table.
If you want to have only one table then I would suggest having an ID, Person, and Car columns that way the ID could be your primary key and you could have an index on the person column and one on the car column.
However, Rob's way is the proper way to setup a database.
|
|
|
|
|
I think Rob's idea is better as it is easy to handle, rather than to make All-in-One table. There is really a basic databse rule invloved here that we should make another table for the multi-valued attributes. Parent table's primary key will be foriegn in the newly made table.
|
|
|
|
|
<quote>
"When inserting or updating via proc that it is a good practice to return the new or updated rec using output parameters."
Opinions - one way or the other - appreciated.
Thanks
|
|
|
|
|
It really depends on how it is being used, and I personally use both techniques. I can't say I've read much on the benefits/pitfalls of each approach, but within T-SQL, I prefer the OUTPUT param simply to favor variable assignment rather than having a result set returned. If the stored procedure is being called from .NET, I usually SELECT out the record ID, and utilize the ExecuteScalar method of SqlCommand.
The T-SQL usage doesn't really favor one over the other (in terms of keystrokes):
EXECUTE StoredProc @Param1, @Param2, @RecordID OUTPUT
SET @RecordID = EXECUTE StoredProc @Param1, @Param2
OUTPUT Parameter:
If stored procedure is being called from within T-SQL.
If the new record ID is not always used by the caller.
SELECT Record ID:
If stored procedure is being called from .NET (use SqlCommmand.ExecuteScalar).
If the new record ID is always used by the caller.
If you do choose to take the OUTPUT parameter route, I would suggest you are careful about how you assign the new record ID to that OUTPUT parameter. If the stored proc is being called within a loop, you'll want to make sure the previous value does not remain in the output parameter (in the event that a record is not actually affected). You may also want to set the default value for that OUTPUT param as NULL so it is optional (in the event the caller does not require the record ID).
|
|
|
|
|
Member 4501940 wrote: that it is a good practice to return the new or updated rec using output parameters.
Having multiple paths to Rome doesn't mean that one traderoute is the "bestest". Some paths are optimized for heavy traffic, while others are only accessible by horse.
It's a good practice to let the programmer decide what solution would fit best into the problem at hand. It might be quite acceptable to return the identity using a simple select statement 
I are Troll 
|
|
|
|
|
Hi,
I am using MS SQL Server 2008 Express.
I have a product table with columns Id and Name. I changed these columns to ProductId and ProductName. In my stored procedure it's not finding ProductId and ProductName, but rather Id and Name. And now it underlines ProductId and ProductName in red. How do I update it so that it sees these columns?
Regards,
Brendan
|
|
|
|
|
Try this,
Right Click the Tables branch in the Server Explorer.
Click Refresh. Open the branch for the changed tables - have the changes taken?
On the Edit Menu - Click on Intellisense/Refresh Local Cache.
Does this help?
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Andy_L_J wrote:
Right Click the Tables branch in the Server Explorer. Click Refresh.
Nope this does not work.
Andy_L_J wrote:
Open the branch for the changed tables - have the changes taken?
Where do I find this?
Andy_L_J wrote:
On the Edit Menu - Click on Intellisense/Refresh Local Cache.
There is no intellisense option from which to choose.
From where are you directing me, from SQL Server Management Studio 2008?
|
|
|
|
|
Did Ashcroft' answer help? You will have to change all references to these field names in all Functions and Stored Procedues that call them in code.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
You need to recompile your stored procedure. It will then reflect the new column names.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi,
Why can't I name a column with the name Name? When I say "SELECT Name..." then Name appears in blue. Why is this? Then I use [Name]. Are then any such similar words that I need appear in blue that I need to be aware of?
I have a product table, and the name of the product I called Name. Is this incorrect? Should I rather call it ProductName?
Regards,
Brendan
|
|
|
|
|
.NET Enthusiast wrote: Name appears in blue. Why is this?
Those are keywords[^].
.NET Enthusiast wrote: Then I use [Name]
You can even name a column "select column", provided that you enclose it with those brackets. That would give you something like this;
SELECT [select column] FROM [tablename]
.NET Enthusiast wrote: I have a product table, and the name of the product I called Name. Is this incorrect? Should I rather call it ProductName?
Both are allowed as column-names. Does a product have name, or a productname?
I are Troll
|
|
|
|
|
Check out the keyword list in Eddies post and NEVER use them. Even square bracketing them should never be allowed IMHO.
Spaces in column names another horrible option that should NEVER be used. If you need to use a square bracket then there is something wrong.
IIRC there used to be an app/database (I think it may have been an old ODBC driver) that refused to support [] and therefore any column requiring a [] could not be used.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
