stored procedure querying data with no select statement

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 2000 Forums

SQL Server Development (2000)

stored procedure querying data with no select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-31 : 09:41:59

tarun writes "Hi
I am in the process of making design changes in our sql 7 db.
I am stuck in a problem wherein the stored pocedure having a query doesnt have the select statment in its query but has a where clause only.This procedure is using some DTS local package . I have checked the DTS package also ,it doesnt have the select statement either. This code is workng absolutely fine.Following is the code.

CREATE PROCEDURE sp_account_history_charges
@clientUser NVARCHAR(40),
@startDate DATETIME,
@endDate DATETIME,
@clientID INT,
@languageCode NVARCHAR(50),
@defaultDirectoryNoEndingSlash VARCHAR(100),
@serverName VARCHAR(40),
@remoteDirectory VARCHAR(100),
@defaultFTPScript VARCHAR(40),
@fileName VARCHAR(255) OUT
AS
DECLARE @oPKG int;
DECLARE @hr int;
DECLARE @returnCode int;
DECLARE @cmd varchar(500);
DECLARE @whereClause nvarchar(255);
DECLARE @newFilename varchar(255);
DECLARE @defaultDirectory varchar(100);
DECLARE @defaultFilename varchar(255);
DECLARE @dtsPackageName varchar(60);
SET @returnCode = -1;
SET @defaultDirectory = @defaultDirectoryNoEndingSlash + '\';
SET @defaultFilename = 'Charges-'
+ CAST(DATEPART(mm, GETDATE()) as varchar(2))
+ CAST(DATEPART(dd, GETDATE()) as varchar(2))
+ CAST(DATEPART(yy, GETDATE()) as varchar(4))
+ CAST(DATEPART(hh, GETDATE()) as varchar(2))
+ CAST(DATEPART(mi, GETDATE()) as varchar(2))
+ CAST(DATEPART(ss, GETDATE()) as varchar(2))
+ '.xls';
SET @dtsPackageName = 'AccountHistoryCharges';

-- Create the Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT 'sp_OACreate Error: ' + CAST(@hr as VARCHAR(30))
RETURN @returnCode
END

-- Load Package
SET @cmd = 'LoadFromSQLServer("' + @serverName + '", "", "", 256, , , , "' + @dtsPackageName + '")'
EXEC @hr = sp_OAMethod @oPKG, @cmd, NULL
IF @hr <> 0
BEGIN
PRINT 'LoadFromSQLServer Error: ' + CAST(@hr as VARCHAR(4))
RETURN @returnCode
END

-- construct the where clause
SET @whereClause = ' AND fc.date >= CAST(''' + CAST(@startDate as NVARCHAR(40)) + ''' as datetime)' +
' AND fc.date < CAST(''' + CAST(@endDate as NVARCHAR(40)) + ''' as datetime)' +
' AND fc.client_id = ' + CAST(@clientID as NVARCHAR(10)) +
' AND le.language_code = ''' + @languageCode + ''''

-- construct the new filename (with full path) where the excel file will be saved
SET @newFilename = @defaultDirectory + @clientUser + '\' + @defaultFilename

-- check variables
PRINT @whereClause
PRINT @newFilename

-- add new global variables
SET @cmd = 'GlobalVariables.AddGlobalVariable("whereClause", "' + @whereClause + '")'
EXEC @hr = sp_OAMethod @oPKG, @cmd, NULL
IF @hr <> 0
BEGIN
PRINT 'AddGlobalVariable Error: ' + CAST(@hr as VARCHAR(4))
RETURN @returnCode
END
SET @cmd = 'GlobalVariables.AddGlobalVariable("newFilename", "' + @newFilename + '")'
EXEC @hr = sp_OAMethod @oPKG, @cmd, NULL
IF @hr <> 0
BEGIN
PRINT 'AddGlobalVariable Error: ' + CAST(@hr as VARCHAR(4))
RETURN @returnCode
END

-- Create the directory needed for the new excel file to save
SET @cmd = 'md ' + @defaultDirectory + @clientUser
EXEC @hr = master..xp_cmdshell @cmd
-- ignore error checking even if the directory has already been created, since the whole point
-- here is just to make sure that the client user directory exists

-- Run DTS package
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF ( @hr <> 0 )
BEGIN
PRINT 'DTS package creation fail, id = ' + CAST(@hr as varchar(4))
RETURN @returnCode
END

-- Destroy the object.
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT 'sp_OADestroy Error: ' + CAST(@hr as VARCHAR(4))
RETURN @returnCode
END

I have checked AccountHist

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources