March 4, 2008 at 12:18 am
HI,
Have some Stored Procedures some part of data is static for all SP want to use is in smarter way. How can i call or use that part of code keeping in one place
..Any body help me
March 4, 2008 at 1:46 am
guptaopus (3/4/2008)
HI,Have some Stored Procedures some part of data is static for all SP want to use is in smarter way. How can i call or use that part of code keeping in one place
..Any body help me
The choices are rather limited. If it is data that is likely to change, you could put it into a helper table, and query it (you will need only read access to it). Alternatively, you could encapsulate this frequent code into another stored procedure(s).
Regards,
Andras
March 4, 2008 at 3:07 pm
I'm not sure from the question, but I think the thing you are probably looking for is user defined functions.
If you find you are constantly having to do certain calculations in stored procedures, for example, you can put the calcuations in a UDF, and then have the procs pull the data from there.
Is that what you're talking about?
(A sample of the code you are talking about would be helpful.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2008 at 3:45 pm
As GSquared rightly says, we need to see code, but I would be wary about utilising a UDF - you could end up with RBAR.
RBAR? - search this site and you'll find plenty of discussion.
Common Table Expressions (CTEs) may also be useful - again we need to see code to fully understand what you are trying to achieve - please post some.
March 4, 2008 at 9:38 pm
Thanks for replying,
Here is the sample code of it...Code is pretty big, i am sure you people can understand.
This function checks Customer Arabic address data quality, the function returns a list of invalid record entries along with the error description.
Function Logic:
On a daily bases an ETL job has to run and implement the following validation:
1.Filter records where Address Type (CUSTADDR.ADDTYPE) = ‘MA’
2.Check that (CUSTADDR.NAMADDR1) do not contains English character set.
=====================Code==========================
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ODS_VLD_JCU07
AS
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @JobID INT,@LoadInstanceID INT, @ODSTableID INT,@DataTypeID SMALLINT,
@InvalidCharacterSet SMALLINT,
@ADDTYPEDataTypeID SMALLINT,
@NamaDDR1DataTypeID SMALLINT,
@NamaDDR2DataTypeID SMALLINT,
@NamaDDR3DataTypeID SMALLINT,
@NamaDDR4DataTypeID SMALLINT,
@WORKADDR1DataTypeID SMALLINT,
@WORKADDR2DataTypeID SMALLINT,
@WORKADDR3DataTypeID SMALLINT,
@WORKADDR4DataTypeID SMALLINT,
@CharacterRange nvarchar(11)
SET @InvalidCharacterSet=10
SET @CharacterRange=N'%[?-?,?-?]%'
--Check That ODS_VLD_JC07 Is There or Not
IF NOT EXISTS(SELECT JobID
FROM dbo.V_DQ_AllowedJob
WHERE JobExistingRef='ODS_VLD_JCU07')
BEGIN
INSERT V_DQ_AllowedJob
(jobID,JobExistingRef,JobName,JobDesc,JobExpectedDurationInMinutes,JobTypeID)
VALUES(7,'ODS_VLD_JCU07','ODS_VLD_JCU07',
'Retail Customer Arabic Address Validation Job',
0,3)
END
--Get JobID
SET @JobID=(SELECT JobID FROM DBO.V_DQ_AllowedJob
WHERE JobExistingRef='ODS_VLD_JCU07')
--Get LoadInstanceID
SET @LoadInstanceID=(SELECT LoadInstanceID
FROM dbo.V_DQ_LoadInstance
WHERE LoadInstanceStartDateTime=(SELECT
MAX( LoadInstanceStartDateTime)
FROM dbo.V_DQ_LoadInstance
WHERE StatusID=3 ))
-- Check that whether load Instance ODSTable Data is there or not and
-- after Insert the Latest load Instance ODSTable Data
EXEC ODSValidationCommon_InsertODSTable 'RETDCUST_CUSTOMER'
--Get ODSTableID
SET @ODSTableID=(SELECT ODSTableID FROM DBO.V_DQ_odstable
WHERE SourceExistingRefName='RETDCUST_CUSTOMER'
AND LoadInstanceID =@LoadInstanceID)
--Check Whether V_DQ_RETDCUST_CUSTOMER is there or not in schema
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'V_DQ_RETDCUST_CUSTOMER' AND type = 'V')
BEGIN
CREATE TABLE #Result( JobID INT,ODSTableID INT,
ObjectExistingReferencePrimaryKey VARCHAR(40),
ObjectFieldName VARCHAR(256),
ObjectFieldValue NVARCHAR(256),
ObjectReference NVARCHAR(256),
ObjectReferenceName VARCHAR(256),
DataTypeID SMALLINT,
ResultID SMALLINT)
============Till here all procedures use same code but with a change of Job code============
-- 1. Customer ADDTYPE Validation
--Get DataType id
EXECUTE ODSValidationCommon_InsertObjectDataType 'V_DQ_RETDCUST_CUSTOMER','ADDTYPE',@DataTypeID OUTPUT
SET @ADDTYPEDataTypeID=@DataTypeID
INSERT INTO #Result( JobID ,ODSTableID ,
ObjectExistingReferencePrimaryKey,
ObjectFieldName ,
ObjectFieldValue,
ObjectReferenceName ,
ObjectReferencedExtendedinfo ,
DataTypeID ,
ResultID )
SELECT @JobID AS JobID ,@ODSTableID AS ODSTableID ,
CUSNO AS ObjectExistingReferencePrimaryKey ,
'ADDTYPE' AS ObjectFieldName ,
ADDTYPE AS ObjectFieldValue,
'"' AS ObjectReferenceName,
'"' AS ObjectReferencedExtendedinfo,
@ADDTYPEDataTypeID AS DataTypeID,
@InvalidCharacterSet AS ResultID
FROM
WHERE
=============After this again following code is static it does not change anyware=========
IF EXISTS(SELECT ODSValidationID
FROM V_DQ_ODSValidation V,
V_DQ_odstable T,
V_DQ_LoadInstance L
WHERE V.ODSTableID=T.ODSTableID
AND T.LoadInstanceID=L.LoadInstanceID
AND L.LoadInstanceID=@LoadInstanceID
AND JobID =@JobID
)
BEGIN
--Delete this job Data From ODSValidation
DELETE FROM V_DQ_ODSValidation
WHERE ODSValidationID IN (SELECT ODSValidationID
FROM V_DQ_ODSValidation V,
V_DQ_odstable T,
V_DQ_LoadInstance L
WHERE V.ODSTableID=T.ODSTableID
AND T.LoadInstanceID=L.LoadInstanceID
AND L.LoadInstanceID=@LoadInstanceID
AND JobID =@JobID)
--Insert the latest Data into ODSValidation from #Result
INSERT INTO V_DQ_ODSValidation(JobID ,ODSTableID ,
ObjectExistingReferencePrimaryKey,
ObjectFieldName,
ObjectFieldValue,
ObjectReferenceName,
ObjectReferenceExtendedInfo ,
DataTypeID,
ResultID)
SELECT JobID ,ODSTableID,
ObjectExistingReferencePrimaryKey,
ObjectFieldName,
ObjectFieldValue,
ObjectReferenceName,
ObjectReferencedExtendedinfo,
DataTypeID,
ResultID
FROM #Result
WHERE JobID IS NOT NULL
AND ODSTableID IS NOT NULL
AND DataTypeID IS NOT NULL
AND ResultID IS NOT NULL
END
ELSE
BEGIN
--Insert the New Data into ODSValidation from #Result
INSERT INTO V_DQ_ODSValidation(JobID ,ODSTableID ,
ObjectExistingReferencePrimaryKey,
ObjectFieldName,
ObjectFieldValue,
ObjectReferenceName,
ObjectReferenceExtendedInfo ,
DataTypeID,
ResultID)
SELECT JobID ,ODSTableID,
ObjectExistingReferencePrimaryKey,
ObjectFieldName,
ObjectFieldValue,
ObjectReferenceName,
ObjectReferencedExtendedinfo,
DataTypeID,
ResultID
FROM #Result
WHERE JobID IS NOT NULL
AND ODSTableID IS NOT NULL
AND DataTypeID IS NOT NULL
AND ResultID IS NOT NULL
END
END
IF @@ERROR=0
BEGIN
COMMIT TRANSACTION
-- Return 0 to the calling program to indicate success.
-- PRINT "The new ODSValidation information has been loaded"
RETURN(0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new ODSValidation"
RETURN(99)
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 5, 2008 at 2:14 am
From looking at your code - I would go with what Andras suggested and remove the common code into separate stored procedures
March 5, 2008 at 5:38 am
Hi,
In the sample Code given above there is query which checks for some condition, if matches that condition it deletes old records and inserts new records else it will insert new records.
As code is same for insert is it possible to keep that code in one place and call within that stored procedure?
March 5, 2008 at 11:58 am
A table-value UDF (not an inline scalar UDF) would probably be a good way to go on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply