September 22, 2011 at 4:20 am
hi,
I am trying to get records with this stored procedure(SP) in its where clause column name is a variable and the text to match is also a variable but this is giving me error. have a look on my SP:
CREATE PROCEDURE cj_Customers_Search
@SearchText nvarchar(250),
@FieldName nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @selectResult nvarchar(max);
SELECT @selectResult = 'SELECT [customerId]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[PinCode]
,[ResPhone]
,[Mobile]
,[Email]
,[DateOfBirth]
,[Married]
,[WedAnniversary]
,[Sex]
FROM cj_Customers
WHERE '+@FieldName + ' = ' + @SearchText;
print @selectResult
EXEC sp_sqlexec @selectResult
END
GO
When I have tried to run it with the following code"
Declare @fieldName varchar(250)
Declare @SearchText varchar(100);
SET @fieldName = 'FirstName';
SET @SearchText = 'pawan';
EXEC cj_Customers_Search @SearchText,@fieldName
on sql then it gives me the following error:
Msg 207, Level 16, State 1, Line 16
Invalid column name 'pawan'.
Can anyone tell me the solution please.
Thanks,
Hem Singh
Thanks & Regards,
Hem Singh
September 22, 2011 at 4:51 am
The single quotes are only closing the SQL string - they're not identifying the string you're passing to it as a string.
WHERE '+@FieldName + ' = ' + @SearchText
Could be changed to:
WHERE '+ Char(39) + @FieldName + Char(39) + ' = ' + Char(39) + @SearchText + Char(39)
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
September 22, 2011 at 5:27 am
above solution will work or u can just add '[' and closing brackets ']' just before and after your column name variable.
September 22, 2011 at 5:28 am
it would look like
WHERE '+ ' [' + @FieldName + '] '+ ' = ' + Char(39) + @SearchText + Char(39)
September 22, 2011 at 5:34 am
thanks TedT & GhanShyam it solved the issue.
Regards,
Hem Singh
Thanks & Regards,
Hem Singh
September 22, 2011 at 6:40 am
welcome 😛
September 22, 2011 at 6:48 am
but what if I have to use datetime in place of string e.g.
WHERE ' + @FieldName + ' = ' + @SearchDate
Thanks,
Hem Singh
Thanks & Regards,
Hem Singh
September 22, 2011 at 7:06 am
u want to send search string as datetime then just change it to this
WHERE ' +' [' +@FieldName +'] '+ ' = ' +'''' +@SearchDate+''''
September 22, 2011 at 7:20 am
my search string is coming in string format from dateTimePicker of winform and i am trying like this:
WHERE '+@FieldName + ' = ' + CAST(@SearchText AS datetime);
-- I have also tried yr below one but not worked:
WHERE '+@FieldName + ' = ' +'''' + CAST(@SearchText AS datetime)+'''';
my @FieldName is working without square brackets([])
Thanks,
Hem Singh
Thanks & Regards,
Hem Singh
September 22, 2011 at 2:58 pm
HemSingh (9/22/2011)
my search string is coming in string format from dateTimePicker of winform and i am trying like this:
WHERE '+@FieldName + ' = ' + CAST(@SearchText AS datetime);
-- I have also tried yr below one but not worked:
WHERE '+@FieldName + ' = ' +'''' + CAST(@SearchText AS datetime)+'''';
my @FieldName is working without square brackets([])
Thanks,
Hem Singh
You should probably leave the [ to avoid issues if there is a space in the field name. ('My Field Has Spaces')
You also need to consider sql injection. What happens if you pass '; delete from SomeTable;'???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 12:06 am
hi,
I am getting error when my search string is a date. the SP i am using is:
ALTER PROCEDURE cj_Customers_Search
@SearchText varchar(250),
--@SearchDate DateTime,
@FieldName varchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @selectResult nvarchar(max);
DECLARE @SearchDate DateTime
IF @FieldName = 'DateOfBirth'
BEGIN
SET @SearchDate = CONVERT(datetime, SUBSTRING(@SearchText,7,10)+SUBSTRING(@SearchText,4,2)+LEFT(@SearchText,2))
SELECT @selectResult = 'SELECT [customerId]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[PinCode]
,[ResPhone]
,[Mobile]
,[Email]
,[DateOfBirth]
,[Married]
,[WedAnniversary]
,[Sex]
FROM cj_Customers
WHERE '+@FieldName + ' = ' + @SearchDate; -- problem is here in @SearchDate
END
problem is in where clause of @SearchDate var and the error is:
Conversion failed when converting date and/or time from character string.
I have tried with the following code on sql server:
DECLARE @SearchText varchar(250),
@FieldName varchar(100)
SELECT @SearchText = '23-09-2011', @FieldName = 'DateOfBirth'
EXEC cj_Customers_Search @SearchText,@FieldName
Any suggestion please.
Thanks,
Hem Singh
Thanks & Regards,
Hem Singh
September 23, 2011 at 12:30 am
I think your date format is wrong
@searchstring should be in 'yyyy-mm-dd' format or you can convert it to datetime
convert(@searchstring as datetime)
September 23, 2011 at 1:02 am
ghanshyam.kundu (9/23/2011)
I think your date format is wrong@searchstring should be in 'yyyy-mm-dd' format or you can convert it to datetime
convert(@searchstring as datetime)
I have already tried convert and cast; and also which is in my previous post's SP:
SET @SearchDate = CONVERT(datetime, SUBSTRING(@SearchText,7,10)+SUBSTRING(@SearchText,4,2)+LEFT(@SearchText,2))
it is converting properly but when using in WHERE clause it is giving error which I have earlier told;
And I think this error is same as earlier without CHAR(39), BUT THAT TIME IT WAS FOR VARCHAR(250) FIELD AND NOW IT IS FOR A DATETIME FIELD
any suggestion.
Thanks & Regards,
Hem Singh
September 23, 2011 at 1:28 am
ALTER PROCEDURE cj_Customers_Search
@SearchText varchar(250),-- its a date '2011-02-02'
--@SearchDate DateTime,
@FieldName varchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @selectResult nvarchar(max);
DECLARE @SearchDate DateTime
IF @FieldName = 'DateOfBirth'
BEGIN
--SET @SearchDate = CONVERT(datetime, SUBSTRING(@SearchText,7,10)+SUBSTRING(@SearchText,4,2)+LEFT(@SearchText,2))
--why u r doing this do u need any calculation to get the date
SELECT @selectResult = 'SELECT [customerId]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[PinCode]
,[ResPhone]
,[Mobile]
,[Email]
,[DateOfBirth]
,[Married]
,[WedAnniversary]
,[Sex]
FROM cj_Customers
WHERE ['+@FieldName + '] = cast( ' + @SearchDate+' as datetime)'
print @selectReseult
--just try a print statement of @selectResult and see where its going wrong
END
try this and do post the result of print query that we can check the outcome
September 23, 2011 at 6:57 am
Hi Guys,
Here is the Solution:-
1. For Searching Date of Birth, put DOB in 'yyyymmdd' format
DECLARE @FIELDNAME NVARCHAR(200)
DECLARE @SEARCHTEXT NVARCHAR(200)
DECLARE @SQL VARCHAR(2000);
SET @FIELDNAME = 'DateOfBirth'
SET @SEARCHTEXT = '20100601' --yyyymmdd format
set @sql = 'SELECT [customerId]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[PinCode]
,[ResPhone]
,[Mobile]
,[Email]
,[DateOfBirth]
,[Married]
,[WedAnniversary]
,[Sex]
FROM cj_Customers WHERE [' + @FIELDNAME + '] LIKE ('''+ CAST(@SEARCHTEXT AS NVARCHAR(200)) + ''')'
EXEC (@sql)
2. For Searching Name (String), then either put 'JACK' or '%JACK%' (LIKE will run)
DECLARE @FIELDNAME NVARCHAR(200)
DECLARE @SEARCHTEXT NVARCHAR(200)
DECLARE @SQL VARCHAR(2000);
SET @FIELDNAME = 'FirstName'
--SET @SEARCHTEXT = '%jack%' --If you are not sure about the name SET @SEARCHTEXT = 'jack' -- If you are sure about the name
set @sql = 'SELECT [customerId]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[State]
,[PinCode]
,[ResPhone]
,[Mobile]
,[Email]
,[DateOfBirth]
,[Married]
,[WedAnniversary]
,[Sex]
FROM cj_Customers WHERE [' + @FIELDNAME + '] LIKE ('''+ CAST(@SEARCHTEXT AS NVARCHAR(200)) + ''')'
EXEC (@sql)
regards
Palash Gorai
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply