|
thnks for ur reply, but i got confused. because I will get error in passing parameter for id in update and insert.
|
|
|
|
|
What you are doing is exactly the method we use.
Assumes ID is an identity primary key.
Test the variable one the way in, note the isnull treatment
If IsNull(@ID,0)=0
begin
future3839 wrote: set identity_insert Person_prs off
This is wrong, leave the identity insert alone but remove the @ID from the value list
After the insert get the new identity value to pass back to the client
Set @ID = Scope_Identity
end
else
begin
Do the update
Return the ID as the result
Select @ID as ID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
this is my code
USE [Hostel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SprocInsertUpdatePerson]
(-- Definition of Storeprocedure's parameter
@id int,
@fname nvarchar(50),
@srname nvarchar(50),
@gender nvarchar(15),
@dob datetime,
@transactionid int
)
AS
Declare @ReturnValue int
if (@id IS NULL) --New Item
Begin
insert into Person_prs
(
--id_prs,
fname_prs,
srname_prs,
gender_prs,
dob_prs,
transactionid_prs
)
values
(
--@id,
@fname ,
@srname,
@gender,
@dob ,
@transactionid
)
Select @id = Scope_Identity()--Returns the last identity value inserted into an identity column in the same scope.
End
else
Begin --Update Item
Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = @id
Select @id as id
End
IF (@@ERROR != 0)
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN @ReturnValue
END
the id_prs field is autonumber. I run SP by this value
execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200
select * from Person_prs
after select table nothing insert into table.while I have to add data and when pass parameter can update it.
could you please help me??
|
|
|
|
|
future3839 wrote: execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200
This is an update, you passed in an ID that is > 0
This will insert a record
execute SprocInsertUpdatePerson 0,'XXX','YYY','male','1988/10/10',1200
I would also return the the @ID value as the return value, it is very useful in the client.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think you have to pass like this for insert
execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200
First Parameter should be NULL
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
thanks a lot. if I insert your code absolutely I don't have any problem with inserting.
execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200
the issue is when I am doing update who can I send parameter.
for instance, I inserted 20 record by above code. now, I wanna update one of those record who have user entered id.
Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = ????????????????????????????????????????????????????????
|
|
|
|
|
I got it.its ok
thanks a lot for your help
|
|
|
|
|
Hi,
I tried this code to get the record Identity with C# Windows Form application bu tit's not working.. can any one help please...
CREATE OR REPLACE FUNCTION fn_create_resume(resume_name character)
RETURNS int AS
$$
DECLARE
record_identity int;
BEGIN
insert into resume_details (resume_id, resume_name) values (default, resume_name);
return resume_id;
END;
$$ LANGUAGE plpgsql;
|
|
|
|
|
Try the following
BEGIN
insert into resume_details (resume_id, resume_name) values (default, resume_name);
select resume_id into record_identity from resume_details where resume_name = resume_name;
return record_identity;
END;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
|
I have the following data returned buy a simple SQL query
| Site | X | Y | Z |
--------------------
A 1 2 3
B 4 5 6
C 7 8 9
I need to get it to the following format
| A | B | C |
--------------
1 4 7
2 5 8
3 6 9
Any ideas / code snippits most welcome
Mark
|
|
|
|
|
|
I have done that!
Can't work it out tho, as all the example sum values and I dont want to do that.
TSQL is not my strong point
|
|
|
|
|
Well Here[^] is an example on Pivot two or more columns. Read through it and try out.
|
|
|
|
|
Do I understand that both your sites (A,B,C) and your attributes (X,Y,Z) are variable in number ? And you are counting some sort of occurrence ?
My pivot tables show the months across the top, the salesman down the left and the number in the grid represents the number of orders closed in that month. Sound familiar ?
Your Pivot might look something like this:
select Salesman, [1] as Jan,[2] Feb,[3] Mar,[4] Apr,[5] May,[6] Jun,<br />
[7] Jul, [8] Aug, [9] Sep, [10] Oct, [11] Nov, [12] Dec<br />
from (<br />
select salesman, OrderID, datepart(month,order_date) as month<br />
from orders<br />
where datepart(year,order_date) = 2010)<br />
AS SourceTable<br />
PIVOT (COUNT(OrderID) FOR month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
|
|
|
|
|
Thanks for your reply, I will have a look at your code now.
My sites are a variable, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances)
Just incase anybody want to have a go here is a create script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[X] [int] NOT NULL,
[Y] [int] NOT NULL,
[Z] [int] NOT NULL
) ON [PRIMARY]
insert into Example(Site, X,Y,Z) Values ('A',1,2,3)
insert into Example(Site, X,Y,Z) Values ('B',4,5,6)
insert into Example(Site, X,Y,Z) Values ('C',7,8,9)
select * from Example
|
|
|
|
|
This article [^]may be useful, I use MAX (min will do just as well) for the aggregator.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think the main problem is I need multiple aggregates (for X, Y and Z).
I have this so far
select *
from Example
pivot
(
Max(X)
for site in ([A],[B],[C])
) as p
|
|
|
|
|
Not sure I understand your problem, are you saying you want 3 aggregates(X,Y,Z) for each type(columns) (A,B,C)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think thats what I need, but not sure.
Basically I want to rotate the returned data by 90 degrees
|
|
|
|
|
You need to unpivot first, or normalize it actually, into something similar to this format:
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Accident] [nvarchar] NOT NULL,
[Occations] [int] NOT NULL,
) ON [PRIMARY]
Either in the query or a temp table, but preferably it should be stored normalized in the database
After that you can make the pivot the normal way
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thanks, you were right 
I now have it working.
|
|
|
|
|
You should consider normalizing the table. It would make it a lot easier in the future to add functionality.
Consider this:
CREATE TABLE Incidents (
Site varchar
IncidentType varchar,
IncidentDate Date,
Incidentinfo varchar,
...
)Then just make your pivot on Select site,incidenttype,count(*) as incidentcount from incidents
You may also exchange site and incidenttype for IDs referencing tables holding info on sites and incidenttypes.
If you add a site to your organisation you simply add a row in a table.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
mark_w_ wrote: I think thats what I need, but not sure.
In that case work through the article because that is EXACTLY what it does with 2 columns, just extend it to meet your requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to zip file with xp_cmdshell in sql server 2005
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
