Please modify the Stored procedure (Error:-incorrect syntax near '+')
-
hi.. the below is my SP.. i want to pass the parameter @name to the open query but it is showing the error incorrect syntax near '+' ALTER PROCEDURE [dbo].[cub] @name nvarchar(50) AS BEGIN SET NOCOUNT ON; select * from OPENQUERY(XHMNDGHFYUHFGHJFFRJF,'select {[Employee].[Dept].[Dept]*[Employee].[Empno].[Empno]} on 1, [Measures].[Salary] on 0 from [Employee] where [Employee].[Empname].&'+ @name +' ') END if i pass the value directly.. like this.. where [Employee].[Empname].&vasu ') it will execute fine.. but through the parameter name it will show error.. help me..
-
hi.. the below is my SP.. i want to pass the parameter @name to the open query but it is showing the error incorrect syntax near '+' ALTER PROCEDURE [dbo].[cub] @name nvarchar(50) AS BEGIN SET NOCOUNT ON; select * from OPENQUERY(XHMNDGHFYUHFGHJFFRJF,'select {[Employee].[Dept].[Dept]*[Employee].[Empno].[Empno]} on 1, [Measures].[Salary] on 0 from [Employee] where [Employee].[Empname].&'+ @name +' ') END if i pass the value directly.. like this.. where [Employee].[Empname].&vasu ') it will execute fine.. but through the parameter name it will show error.. help me..
The problem is 'OPENQUERY does not accept variables for its arguments.' The following link has a potential work around if you must use OPENQUERY: Passing-variables-into-an-OPENQUERY-argument.
-
hi.. the below is my SP.. i want to pass the parameter @name to the open query but it is showing the error incorrect syntax near '+' ALTER PROCEDURE [dbo].[cub] @name nvarchar(50) AS BEGIN SET NOCOUNT ON; select * from OPENQUERY(XHMNDGHFYUHFGHJFFRJF,'select {[Employee].[Dept].[Dept]*[Employee].[Empno].[Empno]} on 1, [Measures].[Salary] on 0 from [Employee] where [Employee].[Empname].&'+ @name +' ') END if i pass the value directly.. like this.. where [Employee].[Empname].&vasu ') it will execute fine.. but through the parameter name it will show error.. help me..
You have several options for this and they all have their downsides. Here's few. One way is to create the statement and concatenate the parameter values to a string variable. After that you can execute the contents of the variable using EXEC[^]. This may lead to problems when dates, numbers etc are used in the parameters. Another option is to create a permanent or temporary table and insert the parameter values to this table and join the openquery to this table. Of course you can use other conditions also (exists, 'column returned by openquery' < 'value in temp table' etc etc). Now you can build a data-type safe solution but you may encounter performance problems.
The need to optimize rises from a bad design.My articles[^]
-
hi.. the below is my SP.. i want to pass the parameter @name to the open query but it is showing the error incorrect syntax near '+' ALTER PROCEDURE [dbo].[cub] @name nvarchar(50) AS BEGIN SET NOCOUNT ON; select * from OPENQUERY(XHMNDGHFYUHFGHJFFRJF,'select {[Employee].[Dept].[Dept]*[Employee].[Empno].[Empno]} on 1, [Measures].[Salary] on 0 from [Employee] where [Employee].[Empname].&'+ @name +' ') END if i pass the value directly.. like this.. where [Employee].[Empname].&vasu ') it will execute fine.. but through the parameter name it will show error.. help me..