SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
----1----+----2----+----3----+----4----+----5----+----6----+----7----+----
-- Name : usp_AttributeValueList_Select
--
-- Parameters :
-- Modification log:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----
CREATE Procedure [dbo].[usp_AttributeValueList_Select]
@iAttributeDefSeqNum_IN INT = 0
, @vcAttributeDefName_IN VARCHAR ( 512 ) = ''
AS
-----------------------------
-- Getting Attribute Value
-----------------------------
IF @iAttributeDefSeqNum_IN > 0
BEGIN
SELECT
AD.[Name]
, AD.AttributeDefSeqNum
, AV.[Value]
, AV.AttributeValueSeqNum
, AV.Description
FROM
AttributeDef AD
INNER JOIN AttributeValue AV ON AV.AttributeDefSeqNum = AD.AttributeDefSeqNum
WHERE
AD.AttributeDefSeqNum = @iAttributeDefSeqNum_IN
AND AV.IsDisabled = 0
ORDER BY
AV.OrderIndex, AV.[Value]
END
IF LEN(@vcAttributeDefName_IN) > 0
BEGIN
SELECT
AD.[Name]
, AD.AttributeDefSeqNum
, AV.[Value]
, AV.AttributeValueSeqNum
, AV.Description
FROM
AttributeDef AD
INNER JOIN AttributeValue AV ON AV.AttributeDefSeqNum = AD.AttributeDefSeqNum
WHERE
AD.[Name] = @vcAttributeDefName_IN
AND AV.IsDisabled = 0
ORDER BY
AV.OrderIndex, AV.[Value]
END
Hi Gurus,
I am newbie to c# + Store Procedure. Please help me to understand. Above is a store procedure. May I ask why it do not point to a table? Thank you.
Pradeep ShetPosted Mar 3, 2015, 8:26 AM
If you are having problem understanding the above procedure, then I can help you.
The procedure check for the passed parameter @iAttributeDefSeqNum_IN > 0
If it is greater than 0 then it execute the select query having join between AttributeDef and AttributeValue table with joining condition between column AttributeDefSeqNum . Along with some filtering condition in WHERE clause
Other condition says,
if length of @iAttributeDefSeqNum_IN > 0 then
Same above query but with one filtering condition AD.[Name] = @vcAttributeDefName_IN
And @vcAttributeDefName_IN parameter is passed from user.
Hope you want to know this only.
Manish Kumar ChoudharyPosted Mar 3, 2015, 8:22 AM
http://www.c-sharpcorner.com/UploadFile/6897bc/creating-and-using-stored-procedure-in-sql-server/