Stored Procedure Help

Transact-SQL
1

Hi All, I am new in SQL and trying to request data from the server based on multiple conditions. For this I am using Stored Procedure but some thing is wrong, it is asking me for the ZipCode input which I have declared. Please help.
Here is my procedure:

ALTER PROCEDURE [dbo].[EnergyStar]
@ZoneES NVARCHAR(50),
@Model NVARCHAR(50),
@ZipCode INT

AS

SET NOCOUNT ON;
SET @ZoneES = UPPER(LTRIM(RTRIM(@ZoneES)))

SET NOCOUNT OFF;

IF @ZoneES = 'North-Central'
BEGIN;
SELECT [GlassPack], [UFACTOR], [SHGC], [MODEL], [ZipCode], [ZoneES]
FROM [SPEC]
WHERE [UFactor] <= 0.30
AND [SHGC] <= 0.40
AND [Model] = @Model
AND [ZipCode] = @ZipCode
ORDER BY [UFactor]
END;
ELSE IF @ZoneES = 'Northern'
BEGIN;
SELECT [GlassPack], [UFACTOR], [SHGC], [MODEL], [ZipCode], [ZoneES]
FROM [SPEC]
WHERE [UFactor] <= 0.27
AND [SHGC] <= 1
AND [Model] = @Model
AND [ZipCode] = @ZipCode
ORDER BY [UFactor]
END;
More options here.....
ELSE
BEGIN;
SELECT NULL AS [GlassPack], NULL AS [UFACTOR], NULL AS [SHGC], NULL AS [MODEL], NULL AS [ZipCode], null AS [ZoneES]
END;

Then my html aspx markup is like this:

     <asp:SqlDataSource ID="SPECGlassPack" runat="server" 
         ConnectionString="<%$ ConnectionStrings:TechCenterConnectionString %>" 
         SelectCommand="EXEC EnergyStar @Model, @ZIPCode">
         <SelectParameters>
             <asp:ControlParameter ControlID="Model_dd" Name="Model" 
                 PropertyName="SelectedValue" Type="String" />
             <asp:ControlParameter ControlID="ZipCode_tb" Name="ZIPCode" PropertyName="Text" 
                 Type="String" />
         </SelectParameters>
     </asp:SqlDataSource>

I am trying to return data based on user's input of zip code in a text box and model selection from a dropdown menu. Error says my procedure is asking for @ZipCode. This happens after I enter a zip code in the text box. Thank you all for your help.

2

You have 3 parameters in the procedure whereas in the .net code you are passing only 2. Where is @ZoneES?

3

You have to assign a default value to a parameter to make in an optional input:

ALTER PROCEDURE [dbo].[EnergyStar]
@ZoneES NVARCHAR(50),
@Model NVARCHAR(50),
@ZipCode INT = -1

4

I would recommend you simplify this whole stored procedure. if tomorrow you have a new ZoneES , will you come in here and change the stored procedure. you should really make it data driven

Create a ZoneES table with the values you have for UFactor and SHGC etc.
Then join to this ZoneES table, remove all of these IF @ZoneES conditions.

I would recommend you make the default of @ZipCode INT = null
then in the where clause do

AND ( ZipCode is null or ZipCode = @ZipCode)
1 Like
5

Thank you for your reply yousiasz, but each one of my zoneES has different math operation, that is why I can't use a join. The two zones that I showed both have <= operation but the other zones that I am not showing some have just an = operation or >= or just > operation.

6

This portion is using positional parameters - not named parameters. If you want to use named parameters the format would be:

EXEC dbo.EnergyStar @Model = @Model, @ZIPCode = @ZIPCode;

Notice: I added the schema - which is best practice and something you should get in the habit of including in all SQL code.

Additionally - since you do not have any optional parameters the above call will fail because it doesn't include the parameter @ZoneES. The call you need would be:

EXEC dbo.EnergyStar @ZoneES = @ZoneES, @Model = @Model, @ZIPCode = @ZIPCode;

As for simplifying the procedure - I would create a table to identify the range values for each zone:

 Select *
   From (
 Values ('North-Central', -1, 0.30, -1, 0.40)
      , ('Northern', -1, 0.27, -1, 1)
      , ('Central', 1, 1, 1, 1)
        ) As zr(ZoneES, UFactorStart, UFactorEnd, SHGCStart, SHGCEnd)

Then - in your code you can either JOIN to this table or select the variables:

Declare @UFactorStart numeric(3,2)
      , @UFactorEnd numeric(3,2)
      , @SHGCStart numeric(3,2)
      , @SHGCEnd numeric(3,2);

 Select @UFactorStart = zr.UFactorStart
      , @UFactorEnd = zr.UfactorEnd
      , @SHGCStart = zr.SHGCStart
      , @SHGCEnd = zr.SHGCEnd
   From (
 Values ('North-Central', -1, 0.30, -1, 0.40)
      , ('Northern', -1, 0.27, -1, 1)
      , ('Central', 1, 1, 1, 1)
        ) As zr(ZoneES, UFactorStart, UFactorEnd, SHGCStart, SHGCEnd)
  Where ZoneES = 'North-Central';
   
 Select @UFactorStart, @UFactorEnd, @SHGCStart, @SHGCEnd

Now - in your code:

WHERE [UFactor] BETWEEN @UFactorStart AND @UFactorEnd
AND [SHGC] BETWEEN @SHGCStart AND @SHGCEnd
AND [Model] = @Model
AND [ZipCode] = @ZipCode

For = comparisons the start/end will be the same value. For < the start value will be the minimum allowable value and the end value will be upper limit value - and opposite for >.

You could also use this:

WHERE [UFactor] >= @UFactorStart AND [UFactor] < @UFactorEnd
AND [SHGC] >= @SHGCStart AND [SHGC] < @SHGCEnd
AND [Model] = @Model
AND [ZipCode] = @ZipCode

With this model - you would insure that the end range is one more than the actual value to be included and start range would be the minimum value to be included. For equals the start would be 1.00 and the end would be 2.00 - for <= the start could be 0.00 and the end could be 0.28, etc...

1 Like