jeffreyjseaman
asked on
SQL SERVER ISNUMERIC WITH DYNAMIC SQL TABLE
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
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
ASKER
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.
ASKER
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.
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.
'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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
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
ASKER
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.