Stored Procedure to pass column name as parameter

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

ALTER PROCEDURE [dbo].[checkUser_SP] 
	-- Add the parameters for the stored procedure here
	   @Fieldname varchar(150),
    @FieldValue VARCHAR(150)
AS

BEGIN

    DECLARE @isExist bit 
DECLARE @columnName  VARCHAR(150)
select  @columnName= @Fieldname
 SET NOCOUNT ON

    IF Exists ( Select null from dbo.TBL_Users WHERE  @Fieldname = @FieldValue  )
     begin
	set @isExist=1
	end
	else
	begin
	set @isExist=0
	end
select @isExist
end

It is the procedure I have created i need the result if particular column have the data that is passed then 1 else 0

SQL

exec [checkUser_SP] @Fieldname='[User_ID]', @FieldValue=1

I have the value in the table but it always shows result 0

Posted 10-Oct-14 4:45am

Moses Geo

Add a Solution

Comments

George Jonsson 10-Oct-14 11:04am

Is this really what you mean? WHERE @Fieldname = @FieldValue

Moses Geo 10-Oct-14 12:03pm

@Fieldname is the parameter that passes column name
@Fieldvalue is the value for that column
above (eg) where User_Id=1

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-10-10T04:57:00

You need to change your SP to enable dynamic queries

SQL

DECLARE @sql VARCHAR(MAX) = ''

SET @sql = 'SELECT * FROM WHERE ' + @FieldName + '=' + @FieldValue
EXEC(@sql)

For further informatin, please see:
http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]
Building Dynamic SQL In a Stored Procedure[^]
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1[^]
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures[^]

Stored Procedure to pass column name as parameter

1 solution

Solution 1

Add your solution here

Preview 0

Stored Procedure to pass column name as parameter

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us