February 18, 2011 at 4:53 am
I have a stored procedure with several statements like
SELECT @a = p.LastName
FROM Person p
WHERE p.PartyID =2
SELECT @b-2 = p.FirstName
FROM Person p
WHERE p.PartyID =2
etc.
WHERE I would like to output all the parameters. How to do this?
This is just an example, the actual Select statements are complex with aggregate data.
Thanks for the help!!
Sam
February 18, 2011 at 6:10 am
Something like this?
CREATE PROCEDURE dbo.spOutputtest
@someint INT
, @somedate Datetime
, @SomeOutput intOUTPUT
, @SomeOutPutFlt FLOAT OUTPUT
, @SomeOutPut Char(25) OUTPUT
AS
SET NOCOUNT ON
Select @SomeOutput = fname from tbName;
Select @SomeOutPutFlt = sum(somefiled) from tbname
ect.
-Roy
February 18, 2011 at 3:16 pm
then how to call values? That's where I seem to be stuck.
exec spOutTest ...
February 23, 2011 at 3:11 am
hi,
i often use output procedure to return single value list.
here is an example of that
procedure :
USE [PalladiumShare]
GO
ALTER PROCEDURE [dbo].[WICompaniesOutput]
@CompanyId BIGINT,
@CompanyName NVARCHAR(200) OUTPUT,
@CompanyDescription NVARCHAR(MAX) OUTPUT,
@CompanyType NVARCHAR(10) OUTPUT,
@CompanyCode CHAR(10) OUTPUT,
@IsActive BIT OUTPUT,
@IsArchived BIT OUTPUT,
@LastModifiedById BIGINT,
@LastModifiedByUserName NVARCHAR(255)
AS /*
*/
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @CallingProcedure NVARCHAR(255)
SET @CallingProcedure = 'WICompaniesOutput'
DECLARE @PreIsArchived BIT
SET @PreIsArchived = 1 -- initialize to false state
DECLARE @rc INT
DECLARE @ErrorDescription NVARCHAR(MAX)
DECLARE @ErrorLine INT
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorNumber INT
DECLARE @ErrorProcedure NVARCHAR(126)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SET @rc = NULL
SET @ErrorDescription = 'Input parameters list: ' + ' @CompanyId = '
+ CAST(@CompanyId AS VARCHAR(255)) + ' @CompanyName = '
+ CAST(@CompanyName AS VARCHAR(255)) + ' @CompanyDescription = '
+ CAST(@CompanyDescription AS VARCHAR(255)) + ' @CompanyType = '
+ CAST(@CompanyType AS VARCHAR(255)) + ' @CompanyCode = '
+ CAST(@CompanyCode AS VARCHAR(255)) + ' @IsActive = '
+ CAST(@IsActive AS VARCHAR(255)) + ' @IsArchived = '
+ CAST(@IsArchived AS VARCHAR(255)) + ' @LastModifiedById = '
+ CAST(@LastModifiedById AS VARCHAR(255))
+ ' @LastModifiedByUserName = '
+ CAST(@LastModifiedByUserName AS VARCHAR(255))
SET @ErrorLine = NULL
SET @ErrorMessage = NULL
SET @ErrorNumber = NULL
SET @ErrorProcedure = 'WICompaniesOutput'
SET @ErrorSeverity = NULL
SET @ErrorState = NULL
DECLARE @dtCurrentDateTime DATETIME
SET @dtCurrentDateTime = GETDATE()
-- Ensure input parameters meet minimal requirements
IF @CompanyId IS NULL
OR @LastModifiedById IS NULL
OR @LastModifiedByUserName IS NULL
BEGIN
SET @ErrorDescription = 'Invalid Input Parameter '
+ @ErrorDescription
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription,
@ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorProcedure,
@ErrorSeverity, @ErrorState, @LastModifiedById,
@LastModifiedByUserName
RETURN 1
END
BEGIN TRY
SELECT @PreIsArchived = IsArchived
FROM WI_Companies
WHERE CompanyId = @CompanyId
END TRY
BEGIN CATCH
SET @ErrorDescription = 'Unknown Error - Procedure = '
+ @ErrorProcedure + ' ' + @ErrorDescription
SET @ErrorLine = ERROR_LINE()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorProcedure = ERROR_PROCEDURE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription,
@ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorProcedure,
@ErrorSeverity, @ErrorState, @LastModifiedById,
@LastModifiedByUserName
RETURN 1
END CATCH
IF @PreIsArchived = 1
BEGIN
SET @ErrorDescription = 'Invalid record value, the record either is already Deleted, or Archived. '
+ @ErrorDescription
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription,
@ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorProcedure,
@ErrorSeverity, @ErrorState, @LastModifiedById,
@LastModifiedByUserName
RETURN 1
END
IF @PreIsArchived IS NULL
BEGIN
SET @ErrorDescription = 'Invalid Record, the record was not found. '
+ @ErrorDescription
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription,
@ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorProcedure,
@ErrorSeverity, @ErrorState, @LastModifiedById,
@LastModifiedByUserName
RETURN 1
END
IF @PreIsArchived = 0
BEGIN
BEGIN TRY
SELECT @CompanyId = CompanyId,
@CompanyName = CompanyName,
@CompanyDescription = CompanyDescription,
@CompanyType = CASE WHEN CompanyType = 'C'
THEN 'Client'
ELSE 'Partner'
END,
@CompanyCode = CompanyCode,
@IsActive = IsActive,
@IsArchived = IsArchived
FROM WI_Companies
WHERE CompanyId = @CompanyId
AND IsActive = 1
AND IsArchived = 0
IF @@rowcount = 0
BEGIN
SET @ErrorDescription = 'Failed to select the record '
+ @ErrorDescription
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription,
@ErrorLine, @ErrorMessage, @ErrorNumber,
@ErrorProcedure, @ErrorSeverity, @ErrorState,
@LastModifiedById, @LastModifiedByUserName
RETURN 1
END
ELSE
BEGIN
RETURN 0 -- the record was found
END
END TRY
BEGIN CATCH
SET @ErrorDescription = 'Unknown Error - Procedure = '
+ @ErrorProcedure + ' ' + @ErrorDescription
SET @ErrorLine = ERROR_LINE()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorProcedure = ERROR_PROCEDURE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription,
@ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorProcedure,
@ErrorSeverity, @ErrorState, @LastModifiedById,
@LastModifiedByUserName
RETURN 1
END CATCH
END
-- if the code reaches here, an unknown error has occured.
SET @ErrorDescription = 'Unknown Error ' + @ErrorDescription
SET @ErrorLine = ERROR_LINE()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorProcedure = ERROR_PROCEDURE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
EXECUTE @rc = WI_ERR_ErrorDescriptionsCreate @ErrorDescription, @ErrorLine,
@ErrorMessage, @ErrorNumber, @ErrorProcedure, @ErrorSeverity,
@ErrorState, @LastModifiedById, @LastModifiedByUserName
RETURN 1
SET NOCOUNT OFF
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
execute statement:
USE [PalladiumShare]
GO
DECLARE@return_value int,
@CompanyName nvarchar(200),
@CompanyDescription nvarchar(max),
@CompanyType nvarchar(10),
@CompanyCode char(10),
@IsActive bit,
@IsArchived bit
EXEC@return_value = [dbo].[WICompaniesOutput]
@CompanyId = 11125,
@CompanyName = @CompanyName OUTPUT,
@CompanyDescription = @CompanyDescription OUTPUT,
@CompanyType = @CompanyType OUTPUT,
@CompanyCode = @CompanyCode OUTPUT,
@IsActive = @IsActive OUTPUT,
@IsArchived = @IsArchived OUTPUT,
@LastModifiedById = 1234,
@LastModifiedByUserName = N'shyam'
SELECT@CompanyName as N'@CompanyName',
@CompanyDescription as N'@CompanyDescription',
@CompanyType as N'@CompanyType',
@CompanyCode as N'@CompanyCode',
@IsActive as N'@IsActive',
@IsArchived as N'@IsArchived'
SELECT'Return Value' = @return_value
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply