January 24, 2012 at 5:20 pm
Hi,
I never used the OUTPUT parameter before. I trying to use an existing procedure and it has OUTPUT parameter.
what value should I give for OUTPUT parameter @retval
USE [master]
GO
/****** Object: StoredProcedure [dbo].[LitespeedBackupDB] Script Date: 01/24/2012 15:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LitespeedBackupDB]
@Dbname varchar(100),
@BackupPath varchar(2000),
@retval int OUT
AS
DECLARE @BackupFile varchar(130),
@hr int
IF NOT EXISTS (SELECT name FROM master..sysdatabases WHERE name = @Dbname)
BEGIN
RAISERROR ('Database doesn''t exits.',15,57)
SET @retval = 57
RETURN
END
IF RIGHT(@BackupPath,1) <> '\'
SET @BackupPath = @BackupPath + '\'
SET @BackupFile = @BackupPath + @dbname + '.lsbak'
EXEC @hr = master.dbo.xp_backup_database
@database = @dbname,
@filename = @BackupFile ,
@threads=3
SET @retval = @hr
January 24, 2012 at 9:35 pm
gmamata7 (1/24/2012)
Hi,I never used the OUTPUT parameter before. I trying to use an existing procedure and it has OUTPUT parameter.
what value should I give for OUTPUT parameter @retval
SET @retval = @hr
The output variable can be used to return values in many ways.
Normally I used to capture the @@error in to the output. From the application I can see whether the sp completed sucessfully. This was in sqlserver 2000.
Now a days better error handling are available in sqlserver 2005 and 2008.
You could assign the output any values and retrive it once the sp is completed.
---- Babu
January 25, 2012 at 6:24 am
Ah, good ol' Litespeed. Was this is a script generated by the Litespeed console?
What exactly are you trying to accomplish with the OUTPUT variable?
January 25, 2012 at 7:51 am
You could execute this procedure as such...
DECLARE @returnVal INT;
EXEC [dbo].[LitespeedBackupDB] @Dbname = '<database name>', @BackupPath = '<backup path>', @retval = @returnVal OUTPUT;
SELECT @returnVal;
Jared
CE - Microsoft
January 25, 2012 at 12:59 pm
Hi Tarvin,
Ah, good ol' Litespeed. Was this is a script generated by the Litespeed console?
What exactly are you trying to accomplish with the OUTPUT variable?
I have an existing script from our DBA and I'm trying to understand how this OUTPUT parameter is being used in the backup procedure.
Thanks
January 26, 2012 at 6:16 am
gmamata7 (1/25/2012)
Hi Tarvin,Ah, good ol' Litespeed. Was this is a script generated by the Litespeed console?
What exactly are you trying to accomplish with the OUTPUT variable?
I have an existing script from our DBA and I'm trying to understand how this OUTPUT parameter is being used in the backup procedure.
Thanks
The output value isn't being used (yes, that's poor coding on the vendor's part). It's being set to a specific value, yes, but it's not returning anything so far as I can tell. Usually an output variable has to be selected upon or have a RETURN() applied to it in order to pass the output outside of the proc.
What is happening is the first set of the @retval grabs the error 57 if the database name does not exist. The RAISEERROR() function terminates the backup and returns the error message if there is no database. WHich obviates the need for the @retval variable.
The second setting of the variable occurs later in the code. The execution of the database backup proc produces a result which is saved in @hr which is then saved in @retval. But there is no RETURN(@retval) after the SET statement, which means @retval will never OUTPUT a value (which is 0 if the backup was done correctly.) However, there is output sent to the message screen if you run the proc manually (Litespeed messages from xp_backup_database) that is sent to the Litespeed engine if you run the backup via the Litespeed console. Again, this obviates the need for the @retval variable.
I've seen @retval used for error trapping by any number of people, but it's becoming obsolete with the Try...Catch availability in T-SQL.
I've done a little testing and verified that this is indeed a Litespeed generated proc, not a user-created one. I wouldn't worry about it as the proc itself works precisely as intended.
January 26, 2012 at 6:49 am
Brandie Tarvin (1/26/2012)
But there is no RETURN(@retval) after the SET statement, which means @retval will never OUTPUT a value (which is 0 if the backup was done correctly.)
Maybe I am misunderstanding you, but I don't believe that is true.
CREATE PROC test
@retval int OUT
AS
BEGIN
SET @retval = 5
END
Will certainly return the value 5 with the output parameter.
DECLARE @outputValue int
EXECUTE test @retval = @outputValue OUTPUT
SELECT @outputValue
Which I believe is the whole point of using an OUTPUT parameter.
Jared
CE - Microsoft
January 26, 2012 at 6:58 am
SQLKnowItAll (1/26/2012)
Brandie Tarvin (1/26/2012)
But there is no RETURN(@retval) after the SET statement, which means @retval will never OUTPUT a value (which is 0 if the backup was done correctly.)Maybe I am misunderstanding you, but I don't believe that is true.
It is true because most people who use that proc don't go the full distance in getting the @retval properly returned.
Not that it matters. This is a vendor proc that isn't intended to be run outside of the Litespeed console. The Litespeed folks just give that option for the more advanced user who has jobs they need to create outside of the automated Litespeed setup.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply