November 10, 2016 at 2:27 am
I have a procedure that has this Update statement
@TableName varchar(128) ,@SourceCount int
if @TableName = 'AcctStatus'
BEGIN
update dbo.MonitorDetail
set NoOfRowsLoaded = isnull((select count(1)
from document.Status with (nolock)), 0),
NoOfRowsExpected = @SourceCount,
LoadEndDate = @CurrDate
from dbo.MonitorDetail md with (nolock)
where LoadNo = @LoadNo
and tablename = @TableName
END
I don't want to hardcode the table name, so I have another Table called ListedTables
CREATE TABLE [daily].[ListedTables](
[LoadNo] [int] NOT NULL,
[TableName] [varchar](128) NOT NULL)
, which list all the table names.
I want to use this ListedTables table to look up the table name instead of hardcoding the table, but I'm not sure about the logic, how to use it in the Update Statement above.
November 10, 2016 at 6:48 am
hoseam (11/10/2016)
I have a procedure that has this Update statement@TableName varchar(128) ,@SourceCount int
if @TableName = 'AcctStatus'
BEGIN
update dbo.MonitorDetail
set NoOfRowsLoaded = isnull((select count(1)
from document.Status with (nolock)), 0),
NoOfRowsExpected = @SourceCount,
LoadEndDate = @CurrDate
from dbo.MonitorDetail md with (nolock)
where LoadNo = @LoadNo
and tablename = @TableName
END
I don't want to hardcode the table name, so I have another Table called ListedTables
CREATE TABLE [daily].[ListedTables](
[LoadNo] [int] NOT NULL,
[TableName] [varchar](128) NOT NULL)
, which list all the table names.
I want to use this ListedTables table to look up the table name instead of hardcoding the table, but I'm not sure about the logic, how to use it in the Update Statement above.
When you say you don't want to "hard code" the table name, I'm not clear on what you mean. You appear to be accepting @TableName as a parameter to your procedure, and your code uses that parameter as part of the WHERE clause, so are you trying to change the WHERE clause, or are you talking about the IF statement where you test the value of the parameter? And if the latter, what do you want to do differently if the value is other than the one you tested for?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
November 10, 2016 at 6:53 am
and I have if @TableName = 'DEAAcctStatus'
I created this table
CREATE TABLE [daily].[ListedTables](
[LoadNo] [int] NOT NULL,
[TableName] [varchar](128) NOT NULL)
so that I can look up tables here, then have if @TableName = (Select TableName from [daily].[ListedTables])
November 10, 2016 at 7:30 am
Couldn't you use an inner join here and reference the table in the where statement
I'm not entirely sure what you want to achieve though
- Damian
November 10, 2016 at 3:09 pm
hoseam (11/10/2016)
and I have if @TableName = 'DEAAcctStatus'I created this table
CREATE TABLE [daily].[ListedTables](
[LoadNo] [int] NOT NULL,
[TableName] [varchar](128) NOT NULL)
so that I can look up tables here, then have if @TableName = (Select TableName from [daily].[ListedTables])
Yeah, I have to agree with Damian. The question here is WHY you need to do this. If you're just trying to validate the parameter to avoid SQL injection, then just validating the parameter exists in the ListedTables table makes sense, and you could use:
IF EXISTS (
SELECT 1
FROM daily.ListedTables
WHERE TableName = @TableName
)
BEGIN
... other SQL statements here
You'd probably also want to validate that the length of the @TableName parameter value is small enough to be valid, and limit the size of the parameter appropriately.
Failing that, I'm not sure what your objective is...
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply