November 15, 2011 at 10:46 am
I have a stored procedure that has table name as one of it's parameters and I want to be able to execute a select statement using a string variable with the table name.
Here is a bit of my code
--Here is the call
EXEC spGetHeader 'DP5116', 2
--Here's the Procedure
CREATE PROCEDURE [dbo].[spGETHEADER_New2]
(@ATTUID VARCHAR(10),
@MonthNum int
)
AS
DECLARE @VPATTUID VARCHAR(10)
DECLARE @SQL1 VARCHAR(1000)
DECLARE @MonthVar VARCHAR(20)
-- Insert statements for procedure here
SET @MonthVar = (Select tblName from dbo.tbl_CDE_MultiMonth WHERE tblIndex = @MonthNum)
SET @SQL1 = '(SELECT VP_ATTUID FROM ' + @MonthVar + ' WHERE ATTUID = ''' + @ATTUID + ''')'
SET @VPATTUID = EXEC(@SQL1)
When I run this code I get the following error:
"Incorrect syntax near the keyword 'EXEC'."
Can anyone tell me what I'm doing wrong? Everything that I've been reading tells me that Dynamic SQL is a bad idea, but what's the alternative?
Thanks for any help from this forum,
Greg
November 15, 2011 at 10:56 am
For your first question, the reason to avoid dynamic sql is twofold. First is security, dynamic SQL is ripe for risk if you don't handle it properly. The second is cache and plan optimization and using enforced parameterization to help deal with constant recompiles.
For the second question...
You can't use EXEC that way, as the right side of an equation. You'll need to use sp_executeSQL and use output parameters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 10:59 am
Ok, I have seen some samples of this method. But do you know a better way for me to approach this other than using Dynamic SQL?
November 15, 2011 at 11:01 am
how many values are in dbo.tbl_CDE_MultiMonth, which is where you were gettting the tablenames from?
Lowell
November 15, 2011 at 11:03 am
18 records (One entry form each month going back 18 months).
November 15, 2011 at 11:11 am
well, i would consider creating a view that pulls all 18 tables together...
then the proc can select directly from the view and contine from there.
depedning on what you are doing with the @VPATTUID variable in the rest of the procedure,, a CTE might be better.
CREATE VIEW dbo.vw_CDE_MultiMonth
AS
SELECT 1 AS MonthVar,ColumnList From tblName1 UNION ALL
SELECT 2 AS MonthVar,ColumnList From tblName2 UNION ALL
--...
SELECT 18 AS MonthVar,ColumnList From tblName18
GO
CREATE PROCEDURE [dbo].[spGETHEADER_New2]
( @ATTUID VARCHAR(10),
@MonthNum int
)
AS
DECLARE @VPATTUID VARCHAR(10)
SELECT @VPATTUID = VP_ATTUID
FROM dbo.vw_CDE_MultiMonth
WHERE MonthNum=@MonthNum
AND ATTUID = @ATTUID
--continue the rest of the processing
Lowell
November 15, 2011 at 11:50 am
Lowell (11/15/2011)
well, i would consider creating a view that pulls all 18 tables together...then the proc can select directly from the view and contine from there.
Good call Lowell, I hadn't realized this was basically a partitioned view. Didn't read that far into it, nice catch.
Check out this link: http://msdn.microsoft.com/en-us/library/ms190019.aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 11:52 am
You guys are Awesome!! Thanks for the suggestions, I will re work my stored procedure this way and use this technique going forward.
I really appreciate the help!!:cool:
November 15, 2011 at 12:00 pm
gregory.perry2 (11/15/2011)
You guys are Awesome!! Thanks for the suggestions, I will re work my stored procedure this way and use this technique going forward.I really appreciate the help!!:cool:
No problem, and it's our pleasure. C'mon back when you reach head-desk levels with partitioned views and crappy execution plans. It can take a few times to really get it depending on how complex you make it, so don't feel bad about asking questions.
... and remember to enforce constraints on the base tables so you can kill one of the usual worst offenders right out of the gate.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply