|
It should return an integer
|
|
|
|
|
It's printed correctly. Query's return type is an array string(I've implemented like that). So, I've to convert it to int using atoi.
|
|
|
|
|
hey guys..i try to insert data in two tables which are related with each other... the first table name is
Mark and the columns mark_id and mark_name
and the second table name is Model
the columns are model_id,Model_name and mark_id and mark_id is FK here
now here is the thing i want to do for example i want to add Honda as Mark to mark table and the model Jazz,Civic etc as models that depends on the HOnda..To do that i created a stored Procedure like below
Create Procedure sp_TablolaraEkle
@marka_ad nvarchar(50),
@model_ad nvarchar(50)
as
Begin Try
Begin Transaction
Set Nocount on
Declare @id int
Declare @Marka nvarchar(50)
insert into Marka(marka_ad) values (@marka_ad)
Select @id=@@Identity
Declare @model_adi nvarchar(50)
insert into Model(marka_id,model_ad)values (@id,@model_ad)
Commit
End Try
Begin Catch
if @@Trancount>0
Rollback
End Catch
but the problem is for example i added Ferrari to mark table with id is 3 i can add its model too but when i want to add one more Ferrari model then it adds Ferrari model with different mark_id in model table
can u help me how it will get rid off the problem...thanks for your help
modified on Tuesday, October 5, 2010 5:28 AM
|
|
|
|
|
The procedure will always return a success. Since you catch any exception that occurs and rollback the SQL statement to insert data.
Your procedure basically inserts a row in the Marka table with the value passed to the stored procedure. Then it attempts to create a new record in Model based upon the identity field of the Marka table (@@identity).
There are a few weird things though. You never assign any value to @model_ad when you are using it to create a new record in Model. This could cause an issue.
To find out what exactly is going wrong remove the 'begin try', 'end try', 'begin catch' and 'end catch'. This should display the error, but remember to put these things back afterwards.
|
|
|
|
|
hii friends..thanks for answer..i did what u said it has given error and i fxed it now...but still my basic problem goes on...is it becasue the id column is identity in Mark table ?
when i add a new row it increase by 1 although i have same data already...For example i save a new data MArk as Fiat and the model is Linea it gives number 16 as id in mark table and it goes to model atble and it writes there too...it is ok till now..but when i want to add one more data mark as Fiat this time give number 17 as id and in model table too.. i want it to be 16...how i will manage it ??
|
|
|
|
|
Before adding the Mark record (ferarri) you need to check if the is an existing record
Select @ID = MarkID from MarkTable where Model = 'ferarri'
If isnull(@id,0) = 0
begin
Insert both records
end
else
begin
insert FK record only
end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Alter Procedure sp_TablolaraEkle
@marka_ad nvarchar(50),
@model_ad nvarchar(50)
as
Begin
Set Nocount on
Declare @id int
Declare @Marka nvarchar(50)
Select @id=id From Marka where marka_ad=@marka_ad
IF isnull(@id,0)=0
BEGIN
INSERT INTO Marka(marka_ad) values (@marka_ad)
SELECT @id=@@Identity
insert into Model(marka_id,model_ad)values (@id,@model_ad)
END
Else
BEGIN
insert into Model (marka_id,model_ad) values(@id,@model_ad)
END
End
ok man i altered my procedure like it and it works now as i wished..thanks for your help...
|
|
|
|
|
We have been upgrading our development IDE's to VStudio 2010. We generate reports for sql server currently using vstudio 2005. Is there a way for newer VStudio's to deploy the reports so sql server 2005 can understand them, so far we don't seem to see how.
|
|
|
|
|
Hi
I would like to share cool feature of SQL Server with you guys like:
Please use the following query when concatenate two or more rows with comma (or any you want) in a single string.
SELECT STUFF((SELECT ',' + RTRIM(RegionDescription) FROM Region FOR XML PATH('')),1,1,'') AS 'Regions'
Please do let me know, if you have any doubt.
Please provide "Vote" if this would be helpful.![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Thanks,
Imdadhusen
sunaSaRa Imdadhusen
+91 99095 44184
+91 02767 284464
|
|
|
|
|
Good stuff. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Publish it as a tip/trick, that way people who are looking for it will find it. Your main audience here are people looking to help others and probably already know this method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your suggestions, i will be share this on Tips/Tricks sections
sunaSaRa Imdadhusen
+91 99095 44184
+91 02767 284464
|
|
|
|
|
Hi There,
I don't really use sql so this can be an stupid questoin but, say ive got a column called accountNumber with the following values:
55123
55124
55124
...etc
and i want to change all the values (5000+ entries) to a concatenation of '000' + the current accountNumber. i.e. 00055123;00055124;00055125
how will i achieve this?
Regards and thanks
|
|
|
|
|
here it is
update tablename set accountNumber = '000'+accountNumber
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
What data type is accountNumber stored in? What database are you using?
|
|
|
|
|
Data type is string and it is a sql express database.
Also, i cannot update the etries that already have a '000' prefix so i can do something like :
UPDATE TableName
SET AccountNumber = '000' + AccountNumber
WHERE AccountNumber NOT LIKE '000%'
Correct?
|
|
|
|
|
What you need to do is use the LPAD function in SQL Server to pad the number to a set size. So, suppose you want to have it padded out to 10 characters, you'd use
UPDATE TableName SET AccountNumber = LPAD(AccountNumber, 10, '0')
|
|
|
|
|
Hello,
I have this structure :
<br />
CREATE PROCEDURE external_sp<br />
AS<br />
BEGIN<br />
<br />
BEGIN TRANSACTION<br />
EXEC internal_sp @param1,@result out<br />
<br />
if( @result <> 1 )<br />
BEGIN<br />
COMMIT TRANSACTION<br />
END<br />
ELSE<br />
BEGIN<br />
ROLLBACK TRANSACTION<br />
END<br />
<br />
END<br />
The problem : inside the stored procedure "internal_sp" there is another BEGIN TRANSACTION block.
If this internal transaction block get rolled back, the outer transaction (in the external_sp stored procedure) fails.
How can i solve this issue ??
Any help will be appreciated...
|
|
|
|
|
Hello,
If you use internal transaction you don't need to use another
transactions.
|
|
|
|
|
?! And if i really need to nest transactions ??
|
|
|
|
|
HI,
I am using SQL SERVER 2005.
I need to find what is the best performance method for my 3 sql Data adapters.
first method:-
1) Open sql connection
2) Initialise 1st data adapter and fill for the data table 1.
3) Initialise 2nd data adapter and fill for the data table 2.
4) Initialise 3rd data adapter and fill for the data table 3.
5) Process my work
6) close connection.
second method:-
a) 1. Open sql connection
2. Initialise 1st data adapter and fill for the data table 1.
3. close connection.
b) 1. Open sql connection
2. Initialise 2nd data adapter and fill for the data table 2.
3. close connection.
c) 1. Open sql connection
2. Initialise 3rd data adapter and fill for the data table 3.
3. close connection.
d) 1. Use thread concept for a) ,b) and c) and take results for adapters.
2. process my work.
so can any one tell me what is the best performance method?
first method or second method?
Thanks
|
|
|
|
|
third method:-
2) Initialise 1st data adapter
3) Initialise 2nd data adapter
4) Initialise 3rd data adapter
1) Open sql connection
2) fill for the data table 1.
3) fill for the data table 2.
4) fill for the data table 3.
6) close connection.
5) Process my work
As to your second method... you do know that the DataAdapter will handle the open/close for you don't you?
Better yet, don't use DataAdapters, but that's another subject.
P.S. And why use separate ones rather than only one?
|
|
|
|
|
hi
my server ip address is changed,should i configure the new ip address with the sqlserver 2000 which i m using in that system.
if so, then wt are the steps to configure ip address to sqlserver 2000
Thanks in advance
Vijay Kumar D
|
|
|
|
|
Hmm 2000 is a long time ago but I'd try this, attempt to connect using your existing IP address, when that does not work think about your question. If you are using a named server you may not need to change the connection information.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to verify user credentials from login form and check it in sql server Database through stored procedure.
And Confirmation sen back to front end.based on that only i'll allow the user.
Thanks & regards
kiran
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
