Terence Terence - 2 months ago 7
SQL Question

Creating stored procedure getting Incorrect syntax near the keyword 'Declare'

I have no idea why I am getting this error


Msg 156, Level 15, State 1, Procedure usp_cloud_ClientAllSearch, Line 2

Incorrect syntax near the keyword 'Declare'.


Because I have declared the variable.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_cloud_ClientAllSearch]
DECLARE @Search NVARCHAR(30)

SELECT
client_Name, client_Surname, client_CompanyName,
clientContact_TelephoneNo, clientContact_MobileNo,
clientAddress_PostalCode
FROM
cloud_Client c
JOIN
dbo.cloud_ClientAddresses a ON a.client_ID = c.client_ID
LEFT JOIN
cloud_ClientContact cc ON cc.client_ID = c.client_ID
WHERE
c.client_Name LIKE @Search + '%' OR
c.client_Surname LIKE @Search + '%' OR
cc.clientContact_MobileNo LIKE @Search + '%' OR
a.clientAddress_PostalCode LIKE @Search + '%'

Answer

The @search should be an input parameter of the stored proc, (no Declare required), and you need an As between the signature block for the stored proc and the body of the proc.

Create PROCEDURE [dbo].[usp_cloud_ClientAllSearch]
 @Search NVARCHAR(30)
As

   SELECT client_Name, client_Surname, client_CompanyName, 
        clientContact_TelephoneNo, clientContact_MobileNo, 
        clientAddress_PostalCode
   FROM cloud_Client c
       Join dbo.cloud_ClientAddresses a
          ON a.client_ID = c.client_ID
       LEFT JOIN cloud_ClientContact cc
          ON cc.client_ID = c.client_ID
   WHERE c.client_Name LIKE @Search + '%' or 
         c.client_Surname LIKE @Search + '%' or 
        cc.clientContact_MobileNo LIKE @Search + '%' or 
         a.clientAddress_PostalCode LIKE @Search + '%'
Comments