We help IT Professionals succeed at work.

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

Anushart
Anushart asked
on
205 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
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks anneshattingal ...It worked !!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.