Delcaring Variables

  • Is there a way to declare a column name as a variable?  Maybe you can't do this but here is an example of what I am trying to do:

    DECLARE @FIELDNAME CHAR(50),

    @NEWPHONE CHAR(50)

    SET @FIELDNAME = 'phone'

    SET @NEWPHONE = SUBSTRING(@FIELDNAME,2,3)+SUBSTRING(@FIELDNAME,5,3)+SUBSTRING(@FIELDNAME,9,10)

    SELECT @FIELDNAME,

    @NEWPHONE

    FROM cn_phone

    WHERE @FIELDNAME like '(%)%-%'

    OR @FIELDNAME like '%-%-%'

  • Dynamic SQL...

    DECLARE @FIELDNAME VARCHAR(50), @NEWPHONE VARCHAR(50), @SQL VARCHAR(1000)

    SET @FIELDNAME = 'phone'

    SET @NEWPHONE = SUBSTRING(@FIELDNAME,2,3)+SUBSTRING(@FIELDNAME,5,3)+SUBSTRING(@FIELDNAME,9,10)

    SET @SQL = 'SELECT ' + @FIELDNAME + ', ' + @NEWPHONE

    SET @SQL = @SQL + ' FROM cn_phone '

    SET @SQL = @SQL + ' WHERE ' + @FIELDNAME + ' LIKE ''(%)%-%'''

    SET @SQL = @SQL + ' OR ' + @FIELDNAME + ' LIKE ''%-%-%'''

    EXEC (@SQL)

  • The problem I have now is that it is looking at the variable @FIELDNAME when setting the @NEWPHONE as just a string rather than a field in the database. 

    I've changed that so it has the theh full name, cn_phone.phone but then it says that it does not match with a table name or alias name used in the query.

  • Just an idea, what if you use syscolumns to set those column variables? Could this help?

    Becasue at the time the declare statement is being created, you are right on that, the @FieldName to be found is a string, if you use syscolumns that might help to identify the columns you are trying to use.

    Again, is just an idea and hope it helps

  • ..or using [Information_Schema] views may be more apt...You'll save yourself a headache in migrating to SQL Server 2005.


    Mathew J Kulangara
    sqladventures.blogspot.com

  • I think I understand your issue with @NEWPHONE name, however SET @NEWPHONE = SUBSTRING(@FIELDNAME,2,3)+SUBSTRING(@FIELDNAME,5,3)+SUBSTRING(@FIELDNAME,9,10) is always going to set a value based on the value of @FIELDNAME.

    How about...

    DECLARE @FIELDNAME VARCHAR(50), @SQL VARCHAR(1000)

    SET @FIELDNAME = 'phone'

    SET @SQL = 'SELECT ' + @FIELDNAME + ', SUBSTRING(' + @FIELDNAME + ', 2, 3) + '

    SET @SQL = @SQL + 'SUBSTRING(' + @FIELDNAME + ', 5, 3) + SUBSTRING(' + @FIELDNAME + ', 9, 10) '

    SET @SQL = @SQL + ' FROM cn_phone '

    SET @SQL = @SQL + ' WHERE ' + @FIELDNAME + ' LIKE ''(%)%-%'''

    SET @SQL = @SQL + ' OR ' + @FIELDNAME + ' LIKE ''%-%-%'''

    EXEC (@SQL)

     

  • Of course %-%-% is different from (%)%-% so....

    DECLARE @FIELDNAME VARCHAR(50), @SQL VARCHAR(1000)

    SET @FIELDNAME = 'phone'

    SET @SQL = 'SELECT ' + @FIELDNAME + ', CASE LEFT(' + @FIELDNAME + ', 1) WHEN ''('' THEN SUBSTRING(' + @FIELDNAME + ', 2, 3) '

    SET @SQL = @SQL + 'ELSE LEFT(' + @FIELDNAME + ', 3) END + '

    SET @SQL = @SQL + 'CASE LEFT(' + @FIELDNAME + ', 1) WHEN ''('' THEN SUBSTRING(' + @FIELDNAME + ', 6, 3) '

    SET @SQL = @SQL + 'ELSE SUBSTRING(' + @FIELDNAME + ', 5, 3) END + '

    SET @SQL = @SQL + 'RIGHT(' + @FIELDNAME + ', 4)'

    SET @SQL = @SQL + ' FROM cn_phone '

    SET @SQL = @SQL + ' WHERE ' + @FIELDNAME + ' LIKE ''(%)%-%'''

    SET @SQL = @SQL + ' OR ' + @FIELDNAME + ' LIKE ''%-%-%'''

    EXEC (@SQL)

  • Thanks Joe.  Your latest suggestions occurred to me while stuck in a long meeting this afternoon.  I did not, however think of your final solution for handeling both variations at once.  Thanks for all your help.  I also used this as a basis for passing an update query in the database to change these fields where the mask characters are present.

    Thanks again.

Viewing 8 posts - 1 through 7 (of 7 total)

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