RE: Must declare the scalar variable, syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

RE: Must declare the scalar variable, syntax error

Author

Topic

celinehgl
Starting Member

3 Posts

Posted - 2010-03-17 : 05:54:41

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Celine Yong>
-- Create date: <14/03/10>
-- Description: <CUSTOMER>
-- =============================================
CREATE PROCEDURE SearchCustomer
-- Add the parameters for the stored procedure here
@customerCompanyName varchar(50),
@customerName varchar(50),
@salesTypeID varchar(10)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @SQLQuery AS nvarchar(4000)
DECLARE @ParamDefinition AS nvarchar(2000)
SET @SQLQuery = 'SELECT Customer.customerNo, customerCompanyName, customerName, customerAddress, customerPostalCode, customerTel, customerFax, salesTypeDescription, Customer.salesTypeID FROM Customer, SalesType WHERE SalesType.salesTypeID = Customer.salesTypeID'

If @customerCompanyName Is Not Null
Set @SQLQuery = @SQLQuery + 'And (customerCompanyName = @customerCompanyName)'

If @customerName Is Not Null
Set @SQLQuery = @SQLQuery + ' And (customerName = @customerName)'

If @salesTypeID Is Not Null
Set @SQLQuery = @SQLQuery + ' And (Customer.salesTypeID = @salesTypeID)'

Set @ParamDefinition = '@customerCompanyName varchar(50),
@customerName varchar(50),
@salesTypeID(10)'

Execute sp_Executesql
@SQLQuery, @ParamDefinition, @customerCompanyName, @customerName, @salesTypeID
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)

ErrorHandler:
Return(@@ERROR)

END

-----------------------------------------------------------------
I got this error when I execute the stored procedure with only this param: @salesTypeID

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@salesTypeID".

(1 row(s) affected)

For the param: @customerCompanyName, I got this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3

For the param: and @customerName, I got this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

(1 row(s) affected)

----------------------------------------------------------------
I have searched the internet and tried possible solutions - still not working. The SQL statements are working fine because I have tested.
Incorrect syntax near '='.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 06:01:41

You need default values
http://msdn.microsoft.com/en-us/library/ms189330.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-17 : 06:02:19

TRY THIS below,Dynamic sql i snot required..
@customerCompanyName varchar(50)=NULL,
@customerName varchar(50)=NULL,
@salesTypeID varchar(10)=NULL

SELECT Customer.customerNo, customerCompanyName, customerName, customerAddress, customerPostalCode, customerTel, customerFax, salesTypeDescription, Customer.salesTypeID FROM Customer, SalesType WHERE SalesType.salesTypeID = Customer.salesTypeID

and(@customerCompanyName is null or customerCompanyName=@customerCompanyName)
and(@customerName is null or customerName =@customerName)
..

celinehgl
Starting Member

3 Posts

Posted - 2010-03-17 : 06:17:14

I have made the changes by adding default values and changing my sql query, but I still have error on the 3 params which is Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

The scalar variable error is gone.

The changes I made are highlighted in bold.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Celine Yong>
-- Create date: <14/03/10>
-- Description: <CUSTOMER>
-- =============================================
CREATE PROCEDURE SearchCustomer
-- Add the parameters for the stored procedure here

@customerCompanyName varchar(50)=NULL,
@customerName varchar(50)=NULL,
@salesTypeID varchar(10)=NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @SQLQuery AS nvarchar(4000)
DECLARE @ParamDefinition AS nvarchar(2000)
SET @SQLQuery = 'SELECT Customer.customerNo, customerCompanyName, customerName, customerAddress, customerPostalCode, customerTel, customerFax, salesTypeDescription, Customer.salesTypeID FROM Customer, SalesType WHERE SalesType.salesTypeID = Customer.salesTypeID and(@customerCompanyName is null or customerCompanyName=@customerCompanyName)
and(@customerName is null or customerName =@customerName)'

Set @ParamDefinition = '@customerCompanyName varchar(50),
@customerName varchar(50),
@salesTypeID(10)'

Execute sp_Executesql
@SQLQuery, @ParamDefinition, @customerCompanyName, @customerName, @salesTypeID
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)

ErrorHandler:
Return(@@ERROR)

END

celinehgl
Starting Member

3 Posts

Posted - 2010-03-17 : 06:19:13

quote:
Originally posted by haroon2k9

TRY THIS below,Dynamic sql i snot required..
@customerCompanyName varchar(50)=NULL,
@customerName varchar(50)=NULL,
@salesTypeID varchar(10)=NULL

SELECT Customer.customerNo, customerCompanyName, customerName, customerAddress, customerPostalCode, customerTel, customerFax, salesTypeDescription, Customer.salesTypeID FROM Customer, SalesType WHERE SalesType.salesTypeID = Customer.salesTypeID

and(@customerCompanyName is null or customerCompanyName=@customerCompanyName)
and(@customerName is null or customerName =@customerName)
..

Why is dynamic sql not required? I need it to search customer.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-17 : 06:33:54

quote:
Originally posted by celinehgl

quote:
Originally posted by haroon2k9

TRY THIS below,Dynamic sql i snot required..
@customerCompanyName varchar(50)=NULL,
@customerName varchar(50)=NULL,
@salesTypeID varchar(10)=NULL

SELECT Customer.customerNo, customerCompanyName, customerName, customerAddress, customerPostalCode, customerTel, customerFax, salesTypeDescription, Customer.salesTypeID FROM Customer, SalesType WHERE SalesType.salesTypeID = Customer.salesTypeID

and(@customerCompanyName is null or customerCompanyName=@customerCompanyName)
and(@customerName is null or customerName =@customerName)
..

Why is dynamic sql not required? I need it to search customer.

did you try this first?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources