SQL SERVER ISNUMERIC WITH DYNAMIC SQL TABLE

jeffreyjseaman
jeffreyjseaman used Ask the Experts™
on
I have a Stored Procedure that needs to check for ISNUMERIC on the 20 columns. The query is created through dynamic SQL.  This is what I want to do but don't know how to get it to work.

Here is what I'm trying to do. If you know a better way I'm all for it. Thanks
Declare
   @Tablename as NVarchar(1000)
   @Column_1 NVarchar(50)

SELECT
   @Column_ = Column1
FROM
  @Tablename

IF ISNUMERIC(@Column_1) = 0 And @Column_1 <> ' '
     BEGIN
         SET @ErrMsg = @ErrMsg + 'The Limit must be a numeric value.' + '~'
     END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
hmmm. do you want to check for the fact if the column is a numerical data type?or if the value of a specify column AND a specific row is numerical?or if ALL the values in that column (aka all rows) are numerical?please clarify

Author

Commented:
I want to see if the value in that column is numeric. I may only have up to 4 rows of data at the most.

Author

Commented:
I want to see if the value in that column is numeric. I may only have up to 4 rows of data at the most.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Commented:
SELECT                          
    'SELECT ' + column_name + '
     FROM ' + table_name + '
     WHERE ISNUMERIC(' + column_name + ') = 0'
FROM information_schema.columns
WHERE table_name = {{YourTable}}

That will return the fields in the table that are not numeric. Fill in {{YourTable}} with your table name with ticks.
Commented:
Oops - this one will give you the row that it's on (ID being the row id name that you have)

SELECT                          
    'SELECT ID, ' + column_name + '
     FROM ' + table_name + '
     WHERE ISNUMERIC(' + column_name + ') = 0'
FROM information_schema.columns
WHERE table_name = {{YourTable}}

Author

Commented:
@Rmm2001: Thanks, but that won't work only because there is a GUI that allows the end user to import a .csv file that generates a ##_tempfilename. All of the columns are set as Varchars.

This is what I ended up doing to make it work.

SET @SQL = 'SELECT Column_1
            FROM ' + @TableName + '
            WHERE ISNUMERIC(Column_1) = 0'
   exec sp_executesql @sql
   SET @Column_1 =  @@ROWCOUNT

IF @Column_1 > 0
   BEGIN    
     SET @ErrorDescription = @ErrorDescription + 'Column_1 must be a numeric value' + '~'
   END

Author

Commented:
This would have worked if I was doing it a different way. I figured it out. However for the help Thank You I'm awarding you the points.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial