Return Parameter using sql_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return Parameter using sql_executesql

Author

Topic

kurtgr
Starting Member

25 Posts

Posted - 2012-05-04 : 19:44:58

Hello

I would like some assistance cant seem to see why it not returning the correct value.

Below is the code


ALTER TRIGGER [dbo].[CommencementOfCareTrackingInsertTrigger] 
   ON  [dbo].[CommencementOfCareTracking] 
   FOR INSERT
AS 
BEGIN

	
	SET NOCOUNT ON;


	DECLARE @ChildId SMALLINT, @GuardianId SMALLINT, @FirstName CHAR(50), @MiddleName CHAR(70),
			@LastName Char(70), @AddressLine1 VARCHAR(50), @AddressLine2 VARCHAR(50), 
			@City CHAR(30), @DateOfBirth SMALLDATETIME,@TitleOfCourtesy CHAR(5), 
			@Gender CHAR(1), @TRN CHAR(13), @NISNo CHAR(10), @EmployeeRef CHAR(10),
			@Phone CHAR(16), @Email VARCHAR(100), @CompanyId SMALLINT, 
			@BranchId SMALLINT, @DepartmentId SMALLINT, @LocationId SMALLINT,
			@PayrollEmployeeId INT,@SqlStr NVARCHAR(1000),@PaymentType CHAR(20),
			@EmployeeRental CHAR(10), @RentalPayee VARCHAR(100), @LandLord VARCHAR(100),@RegularHours REAL,
			@LandLordAddressLine1 VARCHAR(50), @LandLordAddressLine2 VARCHAR(50), @LandLordTRN CHAR(11),@LastModifiedBy CHAR(25),
			@JobTitle CHAR(5),@FosterCareStartDate DATETIME,@EmployeeId INT


	SET @RentalPayee			= ''
	SET @LandLord				= ''
	SET @LandLordAddressLine1	= ''
	SET @LandLordAddressLine2	= ''
	SET @LandLordTRN			= ''
	SET @PaymentType			= ''
	SET @JobTitle				= ''
	SET @RegularHours			= 0
	SET @EmployeeId				= 4


	SELECT  @ChildId = ChildId,
			@GuardianId = GuardianId,
			@LastModifiedBy = UserId,
			@FosterCareStartDate = EffectiveDate			
	FROM Inserted

	Select  @CompanyId = PayrollCompanyId
--			@FosterCareStartDate =  FosterCareStartDate
	FROM Child
	WHERE ChildId = @ChildId

	
	IF NOT EXISTS ( Select * FROM SmartPay.dbo.Employee WHERE HrmEmployeeId = @GuardianId )
	BEGIN


		Set @EmployeeRef = replicate('0',6 - Len(RTRIM(LTRIM(STR(@GuardianId)))))+ LTRIM(RTRIM(STR(@GuardianId)))
		
		Select  @TitleOfCourtesy = Title,
				@FirstName = FirstName,
				@MiddleName = MiddleName,
				@LastName = LastName,
				@DateOfBirth = DateOfBirth,
				@TRN = TRN,
				@NISNo = NISNo,
				@AddressLine1 = AddressLine1,
				@AddressLine2 = AddressLine2,
				@City = City,
				@Phone = PhoneNo1,
				@Email = Email,
				@DepartmentId = DepartmentId,
				@BranchId = BranchId,
				@LocationId = LocationId
		FROM dbo.Guardian
		WHERE GuardianId = @GuardianId


		SET @SqlStr = 'EXEC SmartPay.dbo.AddEmployeeFromFosterCare @EmployeeRef , 
						@TitleOfCourtesy, @FirstName, @LastName, @MiddleName, 
						@AddressLine1, @AddressLine2, @City, @DateOfBirth, 
						@Gender, @TRN, @NISNo, @Phone, @Email, @CompanyId, 
						@RecordId, @BranchId, @DepartmentId, @LocationId, @JobTitle, 
						@StartDate, @LastModifiedBy, @RegularHours, @PayrollEmployeeId '

				
		 		EXECUTE sp_executesql @SqlStr,
          				N'@EmployeeRef CHAR(10), @TitleOfCourtesy CHAR(5), @FirstName VARCHAR(50), 
						  @LastName VARCHAR(70), @MiddleName VARCHAR(70), 
						  @AddressLine1 VARCHAR(50), @AddressLine2 VARCHAR(50), 
						  @City CHAR(30), @DateOfBirth SMALLDATETIME, 
						  @Gender CHAR(1), @TRN CHAR(13), @NISNo CHAR(10), 
						  @Phone CHAR(16), @Email VARCHAR(100),  @CompanyId INT,  @RecordId INT,
						  @BranchId SMALLINT, @DepartmentId SMALLINT, @LocationId SMALLINT,
						  @JobTitle VARCHAR(30), @StartDate SMALLDATETIME, @LastModifiedBy VARCHAR(19), 
						  @RegularHours REAL, @PayrollEmployeeId INT OUTPUT',
          				  @EmployeeRef, @TitleOfCourtesy, @FirstName, @LastName, 
						  @MiddleName, @AddressLine1, @AddressLine2, @City, 
						  @DateOfBirth, @Gender, @TRN, @NISNo, @Phone, @Email, 
						  @CompanyId, @GuardianId, @BranchId, @DepartmentId, 
						  @LocationId, @JobTitle, @FosterCareStartDate, @LastModifiedBy,
						  @RegularHours,@PayrollEmployeeId=@EmployeeId OUTPUT

						  IF @@ERROR <> 0
						  BEGIN
								ROLLBACK TRANSACTION
						  END
	

		

		UPDate Guardian
		SET PayrollEmployeeId = @EmployeeId
		Where GuardianId = @GuardianId

		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRANSACTION
		END

	END

	UPDATE Child
	SET GuardianId = @GuardianId
	WHERE ChildId = @ChildId

	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION
	END



END


ALTER  PROCEDURE [dbo].[AddEmployeeFromFosterCare]
@EmployeeRef CHAR(10), @TitleOfCourtesy CHAR(5), @FirstName VARCHAR(50), 
@LastName VARCHAR(70), @MiddleName VARCHAR(70), 
@AddressLine1 VARCHAR(50), @AddressLine2 VARCHAR(50), 
@City CHAR(30), @DateOfBirth SMALLDATETIME, 
@Gender CHAR(1), @TRN CHAR(13), @NISNo CHAR(10), 
@Phone CHAR(16), @Email VARCHAR(100), @CompanyId SMALLINT, 
@HRMEmployeeId INT, @BranchId SMALLINT, @DepartmentId SMALLINT, 
@LocationId SMALLINT, @JobTitle VARCHAR(50), @StartDate SMALLDATETIME, 
@LastModifiedBy CHAR(25), @RegularHours REAL, @PayrollEmployeeId INT OUTPUT
AS

BEGIN TRANSACTION


		
		INSERT INTO Employee(EmployeeRef, CompanyId, DepartmentId, BranchId, LocationId, TitleOfCourtesy, 
			JobTitle, FirstName, LastName, MiddleName, AddressLine1, AddressLine2, City, 
			DateOfBirth, Gender, EmploymentStartDate, EmploymentEndDate, IncrementDate, TRN, 
			NISNo, Phone, Email, EmployeeStatus, WageType, EmployeeType, 
			RegularHours, AddonPercentage, WageRate, TypeOfTaxCredit, FixedTaxAmount, 
			AdditionalTaxCredit, Rental, RentalPayee, LandLordAddressLine1, 
			LandLordAddressLine2, LanLord, LandLordTRN, PaymentType, OverTimeOnGross, 
			OverTimeGross, CompanyCar, YearOfCar, CostOfCar, CarUsage, 
			TaxablePortionOfPay, BasicPayGL, UseFormattingConvention, EmergencyContact, 
			EmergencyContactNo, TaxSchemeId, P45AddonGross, P45GrossPay, P45PAYE, 
			P45FixedTax, P45NHT, P45Edtax, P45NIS, P45Pension, P45FRC, P45PeriodsWorked, 
			LastModified, LastModifiedBy)
		VALUES( @EmployeeRef, @CompanyId, @DepartmentId, @BranchId, @LocationId, @TitleOfCourtesy, '', @FirstName,  
			   @LastName, @MiddleName, @AddressLine1, @AddressLine2, @City, @DateOfBirth, '', @StartDate, NULL,
			   NULL, @TRN, @NISNo, @Phone, @Email, 'A', 'H', 'C', 0, 0, 0, 'A', 0, 0, 'N', '',
			   '', '', '', '', 'CHQ', 0, 0, 0, '', 0, 'N', 1,
				'', 0, '', '', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, GETDATE(), LTRIM(RTRIM(@LastModifiedBy))+ '-[SFCA]' )



		Select @PayrollEmployeeId = SCOPE_Identity()
		FROM Employee

		

		
		
	COMMIT TRANSACTION

Where is it i am going wrong?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 01:38:55

Question is not clear. Whats returning incorrect value? are you referin to value for @PayrollEmployeeId?

Also there's an issue with your trigger. You're assuming only single inserts happening on CommencementOfCareTracking which might not always be the case. You should be using inserted table in the join rather than using variables to hold its values. Please keep in mind that in case of batch inserts variable will hold only one set of values and hence you'll lose the other rows in the batch.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-05 : 10:22:02

quote:

Select @PayrollEmployeeId = SCOPE_Identity()
FROM Employee

While you would not notice anything strange if you execute this statement, SCOPE_IDENTITY() is not a property of or data in the table that you select from. So the "FROM Employee" is not required/should not be there. You can see what I mean if you run this script

CREATE TABLE #tmpA(id INT IDENTITY(1,1));
CREATE TABLE #tmpB(id INT IDENTITY(1,1));

INSERT INTO #tmpA DEFAULT VALUES;
INSERT INTO #tmpA DEFAULT VALUES;
INSERT INTO #tmpA DEFAULT VALUES;
INSERT INTO #tmpB DEFAULT VALUES;

SELECT SCOPE_IDENTITY() FROM #tmpA;

DROP TABLE #tmpA;
DROP TABLE #tmpB;

You would simply see the following output:

(No column name)
1
1
1

Not to suggest that that is what is wrong with your stored proc - like Visakh said, there is not enough information in your posting to figure out what might be wrong.

kurtgr
Starting Member

25 Posts

Posted - 2012-05-07 : 08:40:49

Hi All

@PayrollEmployeeId was not returning anything at first but I was able to get it work by changing the trigger to


/****** Object:  Trigger [dbo].[CommencementOfCareTrackingInsertTrigger]    Script Date: 05/07/2012 07:35:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Adrian Fenton
-- Create date: 13-MAY-2011
-- Description:	
-- =============================================
ALTER TRIGGER [dbo].[CommencementOfCareTrackingInsertTrigger] 
   ON  [dbo].[CommencementOfCareTracking] 
   FOR INSERT
AS 
BEGIN

	
	SET NOCOUNT ON;


	DECLARE @ChildId SMALLINT, @GuardianId SMALLINT, @FirstName CHAR(50), @MiddleName CHAR(70),
			@LastName Char(70), @AddressLine1 VARCHAR(50), @AddressLine2 VARCHAR(50), 
			@City CHAR(30), @DateOfBirth SMALLDATETIME,@TitleOfCourtesy CHAR(5), 
			@Gender CHAR(1), @TRN CHAR(13), @NISNo CHAR(10), @EmployeeRef CHAR(10),
			@Phone CHAR(16), @Email VARCHAR(100), @CompanyId SMALLINT, 
			@BranchId SMALLINT, @DepartmentId SMALLINT, @LocationId SMALLINT,
			@PayrollEmployeeId INT,@SqlStr NVARCHAR(1000),@PaymentType CHAR(20),
			@EmployeeRental CHAR(10), @RentalPayee VARCHAR(100), @LandLord VARCHAR(100),@RegularHours REAL,
			@LandLordAddressLine1 VARCHAR(50), @LandLordAddressLine2 VARCHAR(50), @LandLordTRN CHAR(11),@LastModifiedBy CHAR(25),
			@JobTitle CHAR(5),@FosterCareStartDate DATETIME,@EmployeeId INT,@paramDef NVARCHAR(3000)


	SET @RentalPayee			= ''
	SET @LandLord				= ''
	SET @LandLordAddressLine1	= ''
	SET @LandLordAddressLine2	= ''
	SET @LandLordTRN			= ''
	SET @PaymentType			= ''
	SET @JobTitle				= ''
	SET @RegularHours			= 0
	SET @EmployeeId				= 4


	SELECT  @ChildId = ChildId,
			@GuardianId = GuardianId,
			@LastModifiedBy = UserId,
			@FosterCareStartDate = EffectiveDate			
	FROM Inserted

	Select  @CompanyId = PayrollCompanyId
	FROM Child
	WHERE ChildId = @ChildId

	
	IF NOT EXISTS ( Select * FROM SmartPay.dbo.Employee WHERE HrmEmployeeId = @GuardianId )
	BEGIN


		Set @EmployeeRef = replicate('0',6 - Len(RTRIM(LTRIM(STR(@GuardianId)))))+ LTRIM(RTRIM(STR(@GuardianId)))
		
		Select  @TitleOfCourtesy = Title,
				@FirstName = FirstName,
				@MiddleName = MiddleName,
				@LastName = LastName,
				@DateOfBirth = DateOfBirth,
				@TRN = TRN,
				@NISNo = NISNo,
				@AddressLine1 = AddressLine1,
				@AddressLine2 = AddressLine2,
				@City = City,
				@Phone = PhoneNo1,
				@Email = Email,
				@DepartmentId = DepartmentId,
				@BranchId = BranchId,
				@LocationId = LocationId
		FROM dbo.Guardian
		WHERE GuardianId = @GuardianId


		SET @SqlStr = N'EXEC SmartPay.dbo.AddEmployeeFromFosterCare @EmployeeRef , 
						@TitleOfCourtesy, @FirstName, @LastName, @MiddleName, 
						@AddressLine1, @AddressLine2, @City, @DateOfBirth, 
						@Gender, @TRN, @NISNo, @Phone, @Email, @CompanyId, 
						@RecordId, @BranchId, @DepartmentId, @LocationId, @JobTitle, 
						@StartDate, @LastModifiedBy, @RegularHours, @PayrollEmployeeId OUTPUT '


		SET @ParamDef = N'@EmployeeRef CHAR(10), @TitleOfCourtesy CHAR(5), @FirstName VARCHAR(50), 
						  @LastName VARCHAR(70), @MiddleName VARCHAR(70), 
						  @AddressLine1 VARCHAR(50), @AddressLine2 VARCHAR(50), 
						  @City CHAR(30), @DateOfBirth SMALLDATETIME, 
						  @Gender CHAR(1), @TRN CHAR(13), @NISNo CHAR(10), 
						  @Phone CHAR(16), @Email VARCHAR(100),  @CompanyId INT,  @RecordId INT,
						  @BranchId SMALLINT, @DepartmentId SMALLINT, @LocationId SMALLINT,
						  @JobTitle VARCHAR(30), @StartDate SMALLDATETIME, @LastModifiedBy VARCHAR(19), 
						  @RegularHours REAL, @PayrollEmployeeId INT OUTPUT' 

		execute sp_executesql
		@SqlStr,
		@ParamDef,
		@EmployeeRef = @EmployeeRef, @TitleOfCourtesy = @TitleOfCourtesy, @FirstName = @FirstName, 
		@LastName = @LastName, @MiddleName = @MiddleName, 
		@AddressLine1 = @AddressLine1, @AddressLine2 = @AddressLine2, 
		@City = @City, @DateOfBirth = @DateOfBirth, 
		@Gender = @Gender, @TRN = @TRN, @NISNo = @NISNo, 
		@Phone = @Phone, @Email = @Email,  @CompanyId = @CompanyId,  @RecordId = @GuardianId,
		@BranchId = @BranchId, @DepartmentId = @DepartmentId, @LocationId = @LocationId,
		@JobTitle = @JobTitle, @StartDate = @FosterCareStartDate, @LastModifiedBy = @LastModifiedBy, 
		@RegularHours = @RegularHours, @PayrollEmployeeId = @EmployeeId OUTPUT
				


		  IF @@ERROR <> 0
		  BEGIN
				ROLLBACK TRANSACTION
		  END


		

		UPDate Guardian
		SET PayrollEmployeeId = @EmployeeId
		Where GuardianId = @GuardianId

		IF @@ERROR <> 0
		BEGIN
			ROLLBACK TRANSACTION
		END

	END

	UPDATE Child
	SET GuardianId = @GuardianId
	WHERE ChildId = @ChildId

	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION
	END



END


Thanks for your help guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:20:03

what are trying to implement using above logic inside trigger? I feel like you're overcomplicating things a bit. whats the purpose dynamic sql SP calls inside trigger?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources