date time convert

Question

I want to get result of below query in sp select * from Policy_Holder_Details where Pan_Number='CQVPB1111M' and Date_Of_Birth='14-Jun-1992' result:- but I Execute below sp then not get in result 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

Piyush Pansuriya · Answer

ALTER PROCEDURE [dbo].[GetLoginByIdentifyPasswordDOB] -- Add the parameters for the stored procedure here @Identify 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 PAN_Number=@Identify and Date_Of_Birth = CONVERT(DATETIME,@Date_Of_Birth) END Pass only two parameters : Pan_No as @Identify and @Date_of_Birth

Piyush Pansuriya · Answer

Because you are writing wrong as @Identify=@Password It will not match in table. It is like 1=0. So it will not return any records from table.

Ayush Patil · Answer

thankx Piyush , then is any solution for it

Cynthia Sathuragiri · Answer

You can pass the column name but you cannot use it in the Sql statement.To pass the column name as parameter would need to use dynamic sql In your procedure you can use like this :- Declare @columnname varchar(50)='columnName' DECLARE @sql nvarchar(max) = 'SELECT ' + @columnname + ' FROM [TableName]'; exec sp_executesql @sql, N'@columnname varchar(50)',@columnname

Ayush Patil · Answer

Thankx piyush its working but I can't understant when I pass the value with three parameters @Identify,@Password,@Date_Of_Birth here I was passed @Identify as a column Name and @password As column value , so why not work please explain to get clearification

Cynthia Sathuragiri · Answer

Please check whether the correct format of datetime parameter is passed to execute the SP

Ayush Patil · Answer

i exec the sp but not get the result , i want result which is shown in screen shot when i exec the simple query get the result but its not working in sp

Piyush Pansuriya · Answer

Please accept the answer as correct

date time convert

Answers (8)