Post reply

Column Name & matching string as variables

  • September 22, 2011 at 4:20 am

    #257773

    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

    #1385040

    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

    #1385054

    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

    #1385056

    it would look like

    WHERE '+ ' [' + @FieldName + '] '+ ' = ' + Char(39) + @SearchText + Char(39)

  • September 22, 2011 at 5:34 am

    #1385060

    thanks TedT & GhanShyam it solved the issue.

    Regards,

    Hem Singh

    Thanks & Regards,
    Hem Singh

  • September 22, 2011 at 6:40 am

    #1385105

    welcome 😛

  • September 22, 2011 at 6:48 am

    #1385113

    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

    #1385124

    u want to send search string as datetime then just change it to this

    WHERE ' +' [' +@FieldName +'] '+ ' = ' +'''' +@SearchDate+''''

  • September 22, 2011 at 7:20 am

    #1385133

    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

    #1385463

    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

    #1385533

    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

    #1385536

    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

    #1385544

    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

    #1385551

    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

    #1385681

    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 16 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy