Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help Me in writting a stored procedure ...I have written most of it using normal programming syntaxes

Posted on 2007-10-10
3
Medium Priority
?
180 Views
Last Modified: 2012-05-05
Can you please hellp me in writting this stored procedure in correct format...I have no knowledge of SP but I worte this as we write using programming language with comments
Below I am pasting two stored procedures please ask me if you are not clear
1 procedure is SP_DATAEVAL which calls 2nd Procedure SP_DATAEVAL2 after set of steps

1)
CREATE PROCEDURE dbo.SP_DATAEVAL
/*below are three inputs for my SP*/
      @carrierid as int,
      @pulseresitor as double,
      @onboardres as double      
AS

if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(2,3)
{
     If(@plugresistor =0)
     {
    Print :"Error"
    }
    Else
    declare @paravalue as varchar(10)
    set @paravalue = (SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN                  (SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM                 TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
     If @paravalue >= @plugresistor * 0.98 And @paravalue <= @paravalue * 1.02
      {
/* should call a sub procedure
Me assume it as EXEC SP_DATAEVAL2 */
EXEC DATAVAL2 @carrierid
      }
Else

Print:"Error"
      /* SET NOCOUNT ON */
      RETURN
}
Else
{
 EXEC DATAEVAL2 @carrierid
}
......................................................................................
2) SP_DATAEVAL2(Note this procedure is working fine)

ALTER PROCEDURE dbo.SP_DATAEVAL2
      @Carrierid as int,
      @abc as int
      
AS
declare @recipetype as int

set @recipetype = (SELECT DISTINCT RECIPETYPE FROM TBLRECIPE WHERE (RECIPEID IN(SELECT RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid)AND CARRIERRECIPEISACTIVE = 'TRUE')))

      /* SET NOCOUNT ON */
      RETURN @recipetype
0
Comment
Question by:Anushart
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20050877
CREATE PROCEDURE dbo.SP_DATAEVAL
/*below are three inputs for my SP*/
      @carrierid as int,
      @plugresistor as bigint,
      @onboardres as bigint      
AS

if(SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN(SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =1)) in(2,3)
BEGIN
     If(@plugresistor =0)
     BEGIN
    return (-201)
    end
    Else
    declare @paravalue as varchar(10)
    set @paravalue = (SELECT PARAMETERVALUE FROM TBLPARAMETER WHERE (STEPID IN                  (SELECT STEPID FROM TBLSTEP WHERE (RECIPEID IN(SELECT DISTINCT TOP 1 RECIPEID FROM                 TBLCARRIERRECIPE WHERE CARRIERID IN(SELECT CARRIERID FROM TBLCARRIER WHERE CARRIERNUMBER = @carrierid) AND CARRIERRECIPEISACTIVE = 'True' )AND STEPORDER=1 AND STEPTYPE=1))AND PARAMETERTYPE =2))
     If @paravalue >= @plugresistor * 0.98 And @paravalue <= @paravalue * 1.02
      BEGIN
/* should call a sub procedure
Me assume it as EXEC SP_DATAEVAL2 */
EXEC DATAVAL2 @carrierid
      end
Else

return (-201)
end
Else
BEGIN
 EXEC DATAEVAL2 @carrierid
end
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 20050924
0
 

Author Comment

by:Anushart
ID: 20051161
Thanks anneshattingal ...It worked !!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question