Sql EXEC PROCEDURE: “must declare the scalar variable ”@p1"

Question

0.00/5 (No votes)

See more:

, +

<pre><pre><pre><pre>I am trying to fix a bug in someone else's code. The executeNonReader() command below produces the following error:

Must declare the scalar variable "@p1"

I don't understand why or how to fix as both parameters are being passed and having looked in qp3 I can see they both have the expected values. Any help would be appreciated. thanks

Stored Procedure:

SQL

USE [SERVER_NAME]
/****** Object:  StoredProcedure [dbo].[TEST_0]    Script Date: 16/12/2019 11:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TESTshp_0] 
    @p1 nvarchar(100),  
    @n nvarchar(100) 
AS 
EXEC('
    CREATE VIEW dbo.[' + @n + '] AS 
    SELECT 
        RTRIM([FT].[FT_CODE]) As FT_UID ,  
        RTRIM([FT].[FT_NAME]) As FT_UID ,  
        RTRIM([STRL].[FT_CODE]) AS FT_CODE,  
        RTRIM([STRL].[S_D]) AS S_D,  
        RTRIM([STRL].[E_DD]) AS E_DD,  
        RTRIM([STRL].[S_AC]) AS S_AC,  
        RTRIM([STRL].[R_EN_DATE]) AS R_EN_DATE,  
        RTRIM([STRL].[R_CH_DATE]) AS R_CH_DATE,  
        RTRIM([STRL].[R_DE_DATE]) AS R_DE_DATE,  
        RTRIM([STRL].[O_SY_UID]) AS O_SY_UID,  
        RTRIM([STRL].[V_N]) AS V_N,  
        RTRIM([STRL].[R_ID]) AS R_ID,  
        RTRIM([STRL].[WA_UID]) AS WA_UID,  
        RTRIM([STRL].[D_LE]) AS D_LE,  
        RTRIM([UR].[UR_NA]) As LE_UR_UID ,  
        RTRIM([RT].[RT_NAME]) As RT_UID ,  
        RTRIM([ST].[ST_NAME]) As ST_UID ,  
        RTRIM([STRL].[STT_DATE]) AS STT_DATE,  
        RTRIM([STRL_S].[STRL_S_NAME]) As STRL_S_UID ,  
        RTRIM([STRL].[ST_TOL]) AS ST_TOL,  
        RTRIM([A].[A_NAME]) As A_UID ,  
        [STRL].[S_GE] AS S_GE,  
        RTRIM([NSTRL].[STRL_UID]) AS STRL_UID,  
        RTRIM([CT].[CT_NAME]) As CT_UID ,  
        RTRIM([STRL].[CL_UID]) AS CL_UID,  
        RTRIM([STRL].[OW_UID]) AS OW_UID,  
        RTRIM([STRL_CLAS].[STRL_CLAS_NAME]) As STRL_CLAS_UID , 
        RTRIM([STRL].[XP]) AS XP,  
        RTRIM([STRL].[CSTART_X]) AS CSTART_X,  
        RTRIM([STRL].[CSTART_Y]) AS CSTART_Y,  
        RTRIM([STRL].[CEND_X]) AS CEND_X,  
        RTRIM([STRL].[CEND_Y]) AS CEND_Y 
    FROM STRL  
    LEFT OUTER JOIN FT ON STRL.FT_UID = FT.FT_UID 
    LEFT OUTER JOIN FT ON STRL.FT_UID = FT.FT_UID 
    LEFT OUTER JOIN SC ON STRL.LE_UR_UID = SC.UR_UID 
    LEFT OUTER JOIN RT ON STRL.RT_UID = RT.RT_UID 
    LEFT OUTER JOIN STT ON STRL.ST_UID = STT.ST_UID 
    LEFT OUTER JOIN LKP_STRL_S ON STRL.STRL_S_UID = LKP_STRL_S.STRL_S_UID 
    LEFT OUTER JOIN LA ON STRL.A_UID = LA.A_UID 
    LEFT OUTER JOIN NSCOUNT ON STRL.CT_UID = NSCOUNT.CT_UID 
    LEFT OUTER JOIN STRL_CLAS ON STRL.STRL_CLAS_UID = STRL_CLAS.STRL_CLAS_UID 
WHERE  
    ([STRL].[SH_GEL].STIntersects(Geometry::STGeomFromText(' + '@p1' + ', 0).MakeValid())>0) AND  
    ([STRL].[S_GE].STGeometryType() = ''POINT'')  '
)

Exec command (Visual Basic):

VB

Using con As SqlConnection = New SqlConnection("Data Source=" + dif.ServerName + ";Initial Catalog=" + dif.DatabaseName + ";Integrated Security=True")
  Using qp3 As SqlCommand = New SqlCommand("TEST_shp_0", con)
    qp3.CommandText = "TEST_shp_0"
    qp3.CommandType = CommandType.StoredProcedure

    If Not HttpContext.Current.Session("Parameters") Is Nothing Then
       For i = 0 To HttpContext.Current.Session("Parameters").Count - 1
       Dim STRLingParam = "'" + HttpContext.Current.Session("Parameters")(i).value().ToSTRLing() + "'"
       qp3.Parameters.AddWithValue("@p" + (i + 1).ToSTRLing(), STRLingParam)
       Next
    End If

    qp3.Parameters.AddWithValue("@n", "TEST_VIEW_" & name.Replace(" ", "_").Replace(".", "_") & "_" & j)

    con.Open()
    qp3.ExecuteNonQuery()
  End Using
End Using

What I have tried:

- giving the parameters in the stored procedure a value of null
- writing in individual parameters
- using commandType = CommandTypetext, and then adding an exec command as text

Posted 18-Dec-19 6:36am

Member 14476420

Updated 12-Mar-21 2:53am

v12

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2019-12-18T07:44:00

Solution 1

My advice is to make it a stored proc and call it from your app. At least that way, you can run it in SSMS and not be forced to run your app in order to watch it fail.

Posted 18-Dec-19 7:44am

#realJSOP

Wendelius · Answer 2 · 2019-12-18T07:52:00

Try debugging the calling side. Placing a breakpoint on the first line and executing each line of code one by one should show you if:
- HttpContext.Current.Session("Parameters") is nothing. This would leave the p1 parameter empty
- Or HttpContext.Current.Session("Parameters").Count is 0. This would cause the same problem

If either of these is the problem check that session parameters are set correctly for your HttpContext

MadMyche · Answer 3 · 2019-12-18T08:04:00

Q: What happens if HttpContext.Current.Session("Parameters") is nothing?
A: Required parameter @p1 is not added to the SQL Command

VB

If Not HttpContext.Current.Session("Parameters") Is Nothing Then
  For i = 0 To HttpContext.Current.Session("Parameters").Count - 1
    Dim stringParam = "'" + HttpContext.Current.Session("Parameters")(i).value().ToString() + "'"
     qp3.Parameters.AddWithValue("@p" + (i + 1).ToString(), stringParam)
  Next
End If

Richard Deeming · Answer 4 · 2021-02-04T01:16:00

No idea why this has popped back into the "active" list, or whether you're still looking for a solution. But the problem is obvious - the query you are passing to EXEC doesn't know anything about the parameters from the calling procedure.

To pass the parameter properly, you need to use sp_executesql[^]

SQL

ALTER PROCEDURE [dbo].[TESTshp_0] 
    @p1 nvarchar(100),  
    @n nvarchar(100) 
As
    EXEC sp_executesql N'CREATE VIEW dbo.' + QUOTENAME(@n) + ' AS 
    SELECT 
        ...
    FROM
        ...
    WHERE  
        ([STR].[SHAPE_GEOMETRY].STIntersects(Geometry::STGeomFromText(@p1, 0).MakeValid()) > 0) 
    AND  
        ([STR].[SHAPE_GEOGRAPHY].STGeometryType() = ''POINT'')
    ;',
    N'@p1 nvarchar(100)',
    @p1 = @p1;

Sql EXEC PROCEDURE: “must declare the scalar variable ”@p1"

4 solutions

Solution 1

Solution 2

Solution 3

Solution 6

Add your solution here

Preview 0