April 12, 2006 at 11:33 am
I am using following code to create the view in the stored procedure
alter PROCEDURE test_wfp @param1 datetime,@param2 datetime,@param3 datetime
as
begin
SET NOCOUNT ON
if exists (select name from sysobjects
where name = 'vLaborhrs' and type = 'V')
drop view vLaborhrs
exec (
'create VIEW vLaborHrs_temp
as
SELECT LbrFlxIDEb, convert(datetime,Convert(Char(10),LbrDateAdd, 101)) as LbrDateAdd,
HRSWorked = Sum(CASE WHEN LbrPayClass = ''1'' AND LbrSecPayCode= ''A'' AND SUBSTRING(LbrDistData,14,15) = ''00'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''')ELSE 0000 END),
OTHours = Sum(CASE WHEN LbrPayClass= ''9'' AND LbrSecPayCode= ''A'' AND SUBSTRING(LbrDistData,14,15)= ''47'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''') ELSE 000 END),
HolHours = Sum(CASE WHEN LbrPayClass= ''2'' AND (LbrSecPayCode= ''H'' or LbrSecPayCode= ''S'') AND
SUBSTRING(LbrDistData,14,15) = ''00'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''') ELSE 000 END),
RegVac = Sum(CASE WHEN LbrPayClass= ''3'' AND LbrSecPayCode= ''V'' AND SUBSTRING(LbrDistData,14,15) = ''F0'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''') ELSE 000 END),
Vac_In_Lieu = Sum(CASE WHEN LbrPayClass= ''3'' AND LbrSecPayCode= ''V'' AND SUBSTRING(LbrDistData,14,15) = ''A0'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''') ELSE 000 END),
PEHours = Sum(CASE WHEN LbrPayClass in ( ''1'',''7'') AND LbrSecPayCode= ''B'' AND SUBSTRING(LbrDistData,14,15) = ''00'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''')
WHEN LbrPayClass= ''7'' AND LbrSecPayCode= ''V'' AND SUBSTRING(LbrDistData,14,15) = ''99'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''') ELSE 000 END),
X_Vac_Hours = Sum(CASE WHEN LbrPayClass= ''9'' AND LbrSecPayCode= ''A'' AND SUBSTRING(LbrDistData,14,15) = ''09'' THEN
REPLACE(CONVERT(Char(10),LbrTotalHours),''.'','''') ELSE 000 END)
From Ebase LEFT OUTER JOIN ELabor on ebflxid = lbrflxideb
Where ebdateend is null and
LbrPayClass IN (''1'', ''9'', ''3'',''2'',''7'') AND LbrSecPayCode in (''B'',''A'',''V'',''S'',''H'') and
SUBSTRING(LbrDistData,14,15) in (''00'',''47'',''F0'',''99'',''09'',''A0'') and
lbrdateadd > = @param2 and lbrdateadd <= @param3
Group By LbrFlxIDEb, convert(datetime,Convert(Char(10),LbrDateAdd, 101)) '
)
end
This code compiles fine. But when I run the stored procedure with the following statement
exec test_wfp '03/26/06','03/27/06','03/30/06'
it it gives me an error message @param2 much be declared. I am passing @param2 and @param3 as the parameters to the stored procedure. It looks like view is not recognizing the parameters sent to the procedure.
Is there any way to pass these parameters to the view?
Thanks
Kavita
April 12, 2006 at 11:52 am
Your first issue is a scope issue. You are telling SQL Server to use @param2 within the dynamic SQL; however, it was declared outside of the dynamic SQL. The dynamic SQL string does not know about @param2, because it was declared outside of its scope. If you want to use the value of @param2 within the dynamic SQL, either append it to the string in the proper place to be executed using '{some SQL code} ' + @param2 + ' {some more SQL code}' format, or use sp_executesql and make it a parameterized dynamic query. The latter method is safer (i.e., SQL Injection).
The second issue is that you are creating a view dynamically inside a stored procedure anyway. That's not a good practice. If you really want a "parameterized view", look at turning this thing into an INLINE TABLE-VALUED FUNCTION with parameters.
Finally, you should get used to using standard SQL datetime formats in your code - i.e., "yyyy-mm-dd" in order to avoid confusion and problems down the road. This will be especially true if you start dealing with historical data; i.e., does "12/1/06" mean Dec. 1, 1906, January 12, 2006, or something else entirely? Using the standard date format eliminates possible confusion on the SQL side and on the front end.
April 12, 2006 at 12:11 pm
Mike makes soe good points. If you need an example of INLINE TABLE-VALUED FUNCTION see http://www.sqlservercentral.com/columnists/nboyle/userdefinedfunctions.asp
Francis
April 12, 2006 at 12:19 pm
Here's a sample to help you get started. Obviously I couldn't test it, since I don't have your tables and sample data, but this is the basic idea. I also modified a couple of your statements -- the REPLACE functions where you're replacing the decimal point means your SUM() statement is relying on an implicit conversion to a numeric type. I replaced this with CAST(... AS NUMERIC(10, 2)) * 100.0 to explicitly convert your data to a numeric type and eliminate the decimal (assumes two decimal points). I also noticed that your SUBSTRING() function calls will all return false every time. The format is SUBSTRING(string, start_pos, length). It looks like you might have used SUBSTRING(string, start_pos, end_pos). Finally I noticed that @param1 isn't even used in the SELECT statement?
Anyway, here is the Inline TVF for you (***disclaimer: not tested***):
CREATE FUNCTION dbo.udf_test_wfp ( @param1 DATETIME,
@param2 DATETIME,
@param3 DATETIME)
RETURNS TABLE
AS RETURN
(
SELECT LbrFlxIDEb, CONVERT(DATETIME, LbrDateAdd, 101) AS LbrDateAdd,
HRSWorked = SUM(
CASE WHEN LbrPayClass = '1'
AND LbrSecPayCode = 'A'
AND SUBSTRING(LbrDistData, 14, 2) = '00'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 0000
END),
OTHours = SUM(
CASE
WHEN LbrPayClass = '9'
AND LbrSecPayCode = 'A'
AND SUBSTRING(LbrDistData, 14, 2) = '47'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 000
END),
HolHours = SUM(
CASE
WHEN LbrPayClass = '2'
AND (LbrSecPayCode = 'H'
OR LbrSecPayCode = 'S')
AND SUBSTRING(LbrDistData, 14, 2) = '00'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 000
END),
RegVac = SUM(
CASE WHEN LbrPayClass = '3'
AND LbrSecPayCode = 'V'
AND SUBSTRING(LbrDistData, 14, 2) = 'F0'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 000
END),
Vac_In_Lieu = SUM(
CASE WHEN LbrPayClass = '3'
AND LbrSecPayCode = 'V'
AND SUBSTRING(LbrDistData, 14, 2) = 'A0'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 000
END),
PEHours = SUM(
CASE WHEN LbrPayClass IN ('1', '7')
AND LbrSecPayCode = 'B'
AND SUBSTRING(LbrDistData, 14, 2) = '00'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
WHEN LbrPayClass = '7'
AND LbrSecPayCode = 'V'
AND SUBSTRING(LbrDistData, 14, 2) = '99'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 000
END),
X_Vac_Hours = SUM(
CASE WHEN LbrPayClass = '9'
AND LbrSecPayCode = 'A'
AND SUBSTRING(LbrDistData, 14, 2) = '09'
THEN CAST(LbrTotalHours AS NUMERIC(10, 2)) * 100.0
ELSE 000
END)
FROM Ebase
LEFT OUTER JOIN ELabor
ON ebflxid = lbrflxideb
WHERE ebdateend IS NULL
AND LbrPayClass IN ('1', '9', '3', '2', '7')
AND LbrSecPayCode IN ('B', 'A', 'V', 'S', 'H')
AND SUBSTRING(LbrDistData, 14, 2) IN ('00', '47', 'F0', '99', '09', 'A0')
AND lbrdateadd >= @param2 AND lbrdateadd <= @param3
GROUP BY LbrFlxIDEb, LbrDateAdd
)
April 12, 2006 at 1:42 pm
Thanks much.That helped me a lot. I am going to try function instead of view.
Kavita
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply