I had to make a weekly export of regional data. To avoid server overload, it was decided to run the stored procedure with a different parameter (region) every day.
Using the following stored procedure, the PROC was working fine in SQL Server management studio. When running this stored proc from a C# program on my workstation, I was getting a different resultset (i.e. REGION_C instead of REGION_B.).
ALTER PROC [dbo].[sp_TSV_regionalInformation] AS
DECLARE @REGION VARCHAR(8)DECLARE @WD INT;
SET DATEFIRST 1
SET @WD = DATEPART (weekday , CURRENT_TIMESTAMP);– Based on the day of the week, will pick up a different Region
IF @WD = 1 SET @REGION=’REGION_A’
IF @WD = 2 SET @REGION=’REGION_B’
IF @WD = 3 SET @REGION=’REGION_C’
IF @WD = 4 SET @REGION=’REGION_D’
IF @WD = 5 SET @REGION=’REGION_E’
IF @WD = 6 SET @REGION=’REGION_F’
SELECT x, y, z FROM table WHERE theRegion = @REGION
As a matter of fact, the Stored Procedure will use the weekday defined at the client level, thus having different results from my C# application and the SQL management studio (1st day of the week set up differently).
In order to fix that, the following statement should be added before the DATEPART function:
Once, this statement has been inserted, the first day if the week will be Monday (in this case) all the time!
