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?

Expand|Select|Wrap|Line Numbers

  
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:

Expand|Select|Wrap|Line Numbers

  
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 :)

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers

  
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

passing a column name to a stored procedure

re: passing a column name to a stored procedure

re: passing a column name to a stored procedure

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about