passing a column name to a stored procedure

[richkid]

Good Day,
I'm Trying to pass parameters to represent a column name and value to a stored procedure to execute but havinfg difficulties... can anyone help?

declare @columnName as nvarchar(30)
declare @value as nvarchar(30)
declare @condition as nvarchar(10)

set @columnName = 'last_name'
set @type = 'LIKE'
set @condition = '%g%'



    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)
    
    SELECT @SQLStatement = 'select * from Users where ' + @columnName + ' ' + @type + ' ' + '' + @value + ''
    -- Execute the SQL statement
    EXEC(@SQLStatement)

[ck9663]

First your @type variable is not declared.


Second, you did this: set @condition = '%g%'
but did this:

SELECT @SQLStatement = 'select * from Users where ' + @columnName + ' ' + @type + ' ' + '' + @value + ''

You use @value instead of @condition.

I modified it a bit.

Try this:

declare @columnName as nvarchar(30)
declare @value as nvarchar(30)
declare @condition as nvarchar(10)
declare @type varchar(40)


set @columnName = 'name'
set @type = 'LIKE'
set @value = '%sys%'



-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = 'select top 5 id, name from sysobjects where ' + @columnName + ' ' + @type + ' ' + '''' + @value + ''''
-- Execute the SQL statement
select (@SQLStatement)

exec(@SQLStatement)

Happy Coding!

-- CK

[richkid]

Thanks Much.... I was trying so many variations thats why some variables not decared... but thanks again for the help :)

First your @type variable is not declared.


Second, you did this: set @condition = '%g%'
but did this:

SELECT @SQLStatement = 'select * from Users where ' + @columnName + ' ' + @type + ' ' + '' + @value + ''

You use @value instead of @condition.

I modified it a bit.

Try this:

declare @columnName as nvarchar(30)
declare @value as nvarchar(30)
declare @condition as nvarchar(10)
declare @type varchar(40)


set @columnName = 'name'
set @type = 'LIKE'
set @value = '%sys%'



-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = 'select top 5 id, name from sysobjects where ' + @columnName + ' ' + @type + ' ' + '''' + @value + ''''
-- Execute the SQL statement
select (@SQLStatement)

exec(@SQLStatement)

Happy Coding!

-- CK