CODEWITHSUNDEEP
mysqlsql
user190881
user190881

Reputation: 169

How to pass column name along with other parameters

I have below SPROC in which i am passing column name(value) along with other parameters(Place,Scenario).

ALTER PROCEDURE [dbo].[up_GetValue] 

    @Value varchar(20), @Place varchar(10),@Scenario varchar(20), @Number varchar(10)   
AS BEGIN
    SET NOCOUNT ON;
    DECLARE    @SQLquery  AS NVARCHAR(MAX)
    set @SQLquery =  'SELECT '  + @Value + ' from PDetail where Place = ' + @Place + ' and Scenario =  ' + @Scenario + '  and Number = ' + @Number 

    exec sp_executesql @SQLquery

END
GO

when executing : exec [dbo].[up_GetValue] 'Service', 'HOME', 'Agent', '123697'

i am getting the below error msg

Invalid column name 'HOME'. Invalid column name 'Agent'.

Do i need to add any thing in the sproc??

Upvotes: 2

Views: 82

Answers (3)

A.Akram
A.Akram

Reputation: 412

First: You tagged your question as mysql but I think your code is MSSQL.

Anyway, your problem is that you need to add quotes around each string valued parameter.

Like this:

alter PROCEDURE [dbo].[up_GetValue] 
    @Value varchar(20), @Place varchar(10),@Scenario varchar(20), @Number varchar(10)   
AS BEGIN
    SET NOCOUNT ON;
    DECLARE    @SQLquery  AS NVARCHAR(MAX)
    set @SQLquery =  'SELECT '  + QUOTENAME(@Value) + ' from PDetail where Place =  ''' + @Place + ''' and Scenario =  ''' + @Scenario + '''  and Number = ''' + @Number +''''
    print @SQLquery
    exec sp_executesql @SQLquery

END
GO

Update:

Use QUOTENAME to make sure it works.

QUOTENAME:

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

Upvotes: 1

Jacob Nelson
Jacob Nelson

Reputation: 3016

Try using a prepared statement instead of concatinating the string.

Example:

PREPARE stmt1 FROM 'SELECT ? from PDetail where Place = ? and Scenario = ? and Number = ?;
EXECUTE stmt1 USING @Value, @Place, @Scenario, @Number;

Upvotes: 0

marekful
marekful

Reputation: 15361

You need to quote column names with ` (backtick) and string values with ".

set @SQLquery =  'SELECT `'  + @Value + '` from PDetail where Place = "' + @Place + '" and Scenario =  "' + @Scenario + '"  and Number = ' + @Number

Upvotes: 0

Related Questions