using variables as tablenames?
-
I am trying to use variables for tablenames and I keep being prompted to declare the var. Can someone shed some light on the error?
CREATE PROCEDURE dbo.GetHist
@type int,
@Start datetime,
@end datetime
AS
BEGIN
Declare @TableName Varchar(25), @StartRows int
IF @type = 0
set @TableName = 'goldhist'
if @type = 1
set @TableName = 'Silverhist'
if @type = 2
set @TableName = 'PlatinumHist'
if @type = 3
set @TableName = 'PalladiumHist'
if @type = 4
set @TableName = 'CrudeHist'
if @type = 5
set @TableName = 'USDHist'
Select @StartRows = count(*) from @TableName
Where DT >= @Start and DT <= @end
Select @StartRows = @StartRows + 30
Set RowCount @StartRows
Select * into #TempHist from @TableName
Where DT <= @End order by dt desc
Select * from #TempHist order by dt
Set RowCount 0
END
GO
-
you should really look at the DB Design you are implementing:
For starters
1. you could have only one table if they are all similar
2. If you cant change the Design of that try implementing a View with
"union all" and perform your query there
3. If the above is not possible then you will have to use dynamic SQL to make your procedure work ... e.g: exec(@stm)
* Noel -
Unfortunately I have to use what is already in place. I am not familiar with the example you gave. How can that be implemented?
-
This is an example of Dynamic SQL
...
Declare @stm varchar(2000)
set @stm = 'Select @StartRows = count(*) from ' + @TableName +
'Where DT >= ' + convert(varchar(20), @Start, 112 
+ ' and DT <= ' + convert(varchar(20), @end, 112 EXEC sp_executesql @stm, N' @StartRows int output', @StartRows
...
But are you positive you can not create a view? I am just trying to guide you in the Right direction so that you use Dynamic SQL as the last resort!
HTH
* Noel -
I'm not completly sure but wanted to get an idea of what you were refering to before I jumped into either. Many thanks
-
See, if this helps explaining in detail what noeld meant:
http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
tryusing sp_executesql, it does parameterized batches for a TSQL statement you design and addresses most of the issues raised here. See BOL for more.
-
Dang, Joe... lighten up! All of your suggestions are spot on but you have to remember that, sometimes, it's a third party database that these folks have to deal with and they can't change it. Saying things like "It says that you have no idea what you are doing" just doesn't help in these kinds of situations... they came to this forum to get some help, not get bashed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
... they came to this forum to get some help, not get bashed.
I think so too!
* Noel -
Like I said, Joe... all of your explanations are spot on and I agree with telling folks of the dangers that lurk out there... but there's two ways to tell them... you don't need to be so sarcastic and you don't need to insult people looking for help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Joe,
Have you ever made a mistake? and if you have... have someone offended you because od that ?
IMO there is a difference between correcting peoples mistakes and OFFENDING them
* Noel -
Thank you all for your suggestions. As previously mentioned at times we are stuck dealing with what we have to work with, bad or good. With all your help I have been able to work within the confines of this DB design, which is not my own may I point out.
When I can find the time to re-write the 20+ sites that incorpoarate these DB's, beleive me, I will gladly. However, I have to use bandaids and duct tape to keep it all together for now.Thanks again. Kyle
-
Joe, Thank you for your honest opinions. Yes, I have heard of SQL injection attacks and have safe gaurded myself against such threats. I do not use raw statements in query strings and I also use parameterized querys. The example code that I shared is actually a sub page of the actual and will not accept any parameters that are not outside the scope of the accepted range.
I do appreciate your comments, however you may want to understand the whole situation and whom your talking about before your start throwing around tougue lashings.
-
Hi Noeld,
I like your replies and I 100% agree to that.
I have a question. Could you please explain in detail.
I have a stored procedure "getbyloancust"
The logic of the stored procedure is something like this.
CREATE PROCEDURE dbo.getbyloancust
@LoanCust as int
AS
IF @Mode = 0
select * from table1 where colmod = @Mode
ELSE IF @Mode = 1
select * from table1 where colmod = @Mode and defr = 0
ELSE IF @Mode = 2
select * from table1 where colmod = @Mode and bkrup = 0
ELSE IF @Mode = 3
ELSE IF @Mode = 4
ELSE IF @Mode = 5
-
Sorry...the full portion of the question is here.
Hi Noeld,
I like your replies and I 100% agree to that.
I have a question. Could you please explain in detail.
I have a stored procedure "getbyloancust"
The logic of the stored procedure is something like this.
CREATE PROCEDURE dbo.getbyloancust
@LoanCust as int
AS
IF @Mode = 0
select * from table1 where colmod = @Mode
ELSE IF @Mode = 1
select * from table1 where colmod = @Mode and defr = 0
ELSE IF @Mode = 2
select * from table1 where colmod = @Mode and bkrup = 0
ELSE IF @Mode = 3
select * from table1 where colmod = @Mode and repos = 0
My question is:
1. Use dynamic sql to add the where condition. i.e for simplicity I have referred one table, but the actual sql query will have several joins in it.
2. Create 4 more stored procedure and copy the same sql there and call those stored procedures individually.
Please explain me which one is the optimized way of doing.
Thanks,
Ganesh
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply