Database

hatemtaleb wrote:

Because I am coding an application in VB6

if (@id IS NULL) --New Item

else
	
	Begin --Update Item

If IsNull(@ID,0)=0
begin

future3839 wrote:

set identity_insert Person_prs off

Set @ID = Scope_Identity
end
else
begin

Select @ID as ID

USE [Hostel]
GO
/****** Object:  StoredProcedure [dbo].[SprocInsertUpdatePerson]    Script Date: 10/29/2010 23:54:55 ******/
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

execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200

select * from Person_prs

future3839 wrote:

execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200

execute SprocInsertUpdatePerson 0,'XXX','YYY','male','1988/10/10',1200

execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200

execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200

Update Person_prs  		
		Set 
		--Id = @id,
		fname_prs = @fname,
		srname_prs = @srname,
		gender_prs = @gender,
		dob_prs = @dob,
		transactionid_prs = @transactionid
		where  id_prs = ????????????????????????????????????????????????????????

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;  

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