Passing Column Name as pareameter

Ayush Patil

8y4.7k

ALTER PROCEDURE [dbo].[GetLoginByIdentifyPasswordDOB]

-- Add the parameters for the stored procedure here

@Identify varchar(50) ,

@Password varchar(50) ,

@Date_Of_Birth Datetime

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.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya8y ago
Accepted 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
Ayush Patil8y ago
thankx piyush and Sudipta for your efforts
sudipta sanyal8y ago
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 Patil8y ago
Piyush,
getting below error

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '1'.
sudipta sanyal8y ago
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 Patil8y ago
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 Patil8y ago
Result like as
Ayush Patil8y ago
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 Patil8y ago
no , i m trynig to dynamic way but i am faild to execute sp with column name as parameter