March 29, 2016 at 6:57 am
Hi
I'll start with the first issue
I have a number of staging tables that, apart from the name, have an identical structure
The names are S_FACT_CLAIM_TRANSCTION_<Suffix>
I've created a stored procedure that populates the final table from all of these staging tables
In it's simplest form i.e. I've stripped out all the detail, I have:
create procedure testSP
(@MyTable nvarchar(20) = NULL)
as
set nocount on;
declare @sqlquery as nvarchar(max)
declare @ParamDefinition as nvarchar(2000)
select @ParamDefinition = ' @MyTable varchar(50)'
select @sqlquery = N'select CLAIM_TRANSACTION_BKEY from @MyTable'
execute sp_executeSQL
@ParamDefinition,
@MyTable
go
If I now call this using:
execute testSP S_FACT_CLAIM_TRANSCTION_SIC
[/code]
I get an error
Msg 1087, Level 16, State 1, Line 1
Must declare the table variable "@MyTable"
I'm aware I could create SQLQuery as one big string using ' + @MyTable + ' but doesn't that create an inefficient query plan?
Does anybody know what the issue is here?
Taking that a step further, I want to then write the results to the single destination table using SSIS
If I do create an OLE DB Source and put ' + @MyTable + ' here I get the error:
'testSP' contains dynamic SQL. Consider using the WITH RESULTS SETS clause to explicitly describe the result set
Then, amending the exec to:
exec testSP with result sets ('S_FACT_CLAIM_TRANSCTION_SIC')
I get incorrect syntax near 'S_FACT_CLAIM_TRANSCTION_SIC'
I've tried a few variations of 'with results sets' but can't seem to get it working
Where am I going wrong?
Am I approaching this the correct way?
Thanks
Damian.
- Damian
March 29, 2016 at 12:07 pm
Your first issue is that you can't use a variable directly as a table name. SQL is assuming that @MyTable is a table variable NOT a table name. You'll need to just concatenate to get it to work. I would suggest validating that the value in @MyTable is a valid table name by doing a check against sys.tables where name = @MyTable to help prevent SQL Injection. Or a better way would be to you a series of IF statements and having select statements from the correct table.
The second issue is that you aren't defining the RESULT SETS properly. You need something like this:
CREATE TABLE test (NAME varchar(128) DEFAULT 'Test');
GO
INSERT INTO test
DEFAULT VALUES
GO
CREATE PROCEDURE testSP (@MyTable nvarchar(128))
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS ( SELECT
1
FROM
sys.tables AS T
WHERE
T.name = CASE WHEN @MyTable LIKE '%.%' THEN SUBSTRING(@MyTable, CHARINDEX('.', @MyTable) + 1, LEN(@MyTable) - CHARINDEX('.', @MyTable))
ELSE @MyTable
END )
BEGIN;
DECLARE @sqlquery NVARCHAR(4000) = 'Select name from ' + @MyTable;
EXEC sys.sp_executesql @sqlquery;
END;
ELSE
BEGIN
RAISERROR('Access to Table %s denied', 10, 1, @MyTable);
END;
END;
GO
EXECUTE dbo.testSP @MyTable = 'dbo.test' WITH RESULT SETS ((name nvarchar(128)));
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2016 at 8:54 am
great, thanks
I have the basics working now
Now going to try and adapt this
...Adapted version working, problem solved!
Thanks
- Damian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply