Passing Column Name as pareameter

Question

ALTER PROCEDURE [dbo].[GetLoginByIdentifyPasswordDOB] -- Add the parameters for the stored procedure here @Identify varchar(50) , @Password varchar(50) , @Date_Of_Birth Datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select * from Policy_Holder_Details where @Identify=@Password and Date_Of_Birth = @Date_Of_Birth END Here i m Pass @ Identify as column name and in @Password as column value.

Piyush Pansuriya · Answer

So, This is the solution for that, You have to use query string. ALTER PROCEDURE [dbo].[GetLoginByIdentifyPasswordDOB] -- Add the parameters for the stored procedure here @Identify varchar (50) , @ Password varchar (50) , @Date_Of_Birth Datetime AS BEGIN DECLARE @query nvarchar( max ) -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; SET @query = 'SELECT * FROM Policy_Holder_Details where ' + @Identify+ ' = ' '' + @ Password + '' ' AND Date_Of_Birth = ' '' + CONVERT ( VARCHAR (50),@Date_Of_Birth) + '' ' ' EXEC (@query) END

sudipta sanyal · Answer

Try with this ALTER PROCEDURE [dbo].[GetLoginByIdentifyPasswordDOB] -- Add the parameters for the stored procedure here @Identify varchar(50) , @Password varchar(50) , @Date_Of_Birth Datetime AS BEGIN DECLARE @query nvarchar(max) -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @query = 'SELECT * FROM Policy_Holder_Details where '+ @Identify+' = '''+ @Password +''' AND Date_Of_Birth = '''+ CONVERT(VARCHAR(50),@Date_Of_Birth) + ''' ' EXEC(@query) END

Ayush Patil · Answer

thankx piyush and Sudipta for your efforts

Ayush Patil · Answer

Piyush, getting below error Msg 102, Level 15, State 1, Line 10 Incorrect syntax near '1'.

sudipta sanyal · Answer

CREATE TABLE #tempPolicy_Holder_Details( Policy_Holder_Detail_ID INT, First_Name NVARCHAR(50), Last_Name NVARCHAR(50), SEX CHAR(1), DATE_Of_Birth DATETIME, PAN_Number NVARCHAR(10) ) INSERT INTO #tempPolicy_Holder_Details ( Policy_Holder_Detail_ID , First_Name , Last_Name, SEX, DATE_Of_Birth , PAN_Number ) SELECT 594, 'Nitin', 'Patil', 'M', '1992-05-01 00:00:00.000', '123456789' INSERT INTO #tempPolicy_Holder_Details ( Policy_Holder_Detail_ID , First_Name , Last_Name, SEX, DATE_Of_Birth , PAN_Number ) SELECT 596, 'Nitin1', 'Patil1', 'M', '1992-05-01 00:00:00.000', '3456789123' ------------------------------------------------------------ DECLARE -- Add the parameters for the stored procedure here @Identify varchar(50) , @Password varchar(50) , @Date_Of_Birth Datetime DECLARE @DynSql NVARCHAR(MAX) DECLARE @ParamDefinition NVARCHAR(500) SELECT @DynSql = '' SELECT @Identify = 'PAN_Number', @Password = '3456789123', @Date_Of_Birth = '1992-05-01 00:00:00.000' SELECT @DynSql = @DynSql + 'select * from #tempPolicy_Holder_Details where ' + @Identify + ' ='''+@Password+''' and Date_Of_Birth = '''+CONVERT(VARCHAR(10),@Date_Of_Birth,101)+''' ' EXECUTE( @DynSql) DROP TABLE #tempPolicy_Holder_Details

Ayush Patil · Answer

Getting Below Error whene I am exec sp Exec GetLoginByIdentifyPasswordDOB 'Pan_Number', 'CQVPB9999M','1992-05-01 00:00:00.000' or Exec GetLoginByIdentifyPasswordDOB 'Pan_Number', 'CQVPB9999M','01-May-1992' Error: Msg 241, Level 16, State 1, Procedure GetLogin, Line 21 Conversion failed when converting date and/or time from character string.

Ayush Patil · Answer

Result like as

Ayush Patil · Answer

ok, In @Identify passing value from dropdown List which is my column name and in @Password passing the perticuler column value if i am select First_Name from Dropdown then i am Passing @Password As Nitin

Ayush Patil · Answer

no , i m trynig to dynamic way but i am faild to execute sp with column name as parameter