Dynamic View?

Hello Experts,

I am building an application that uses SQL Server. Soon this will change to Oracle but that is not my biggest concern. My main concern is about a view.

This view has the following select statement:
Select t$hfdl, t$date, t$wrko, t$beur from ttirbh016700 where t$uitv = 1 and t$afgm = 1 and t$odat <> 0 and t$dctb = 0

The table this is selected from, should be different depending on a value my Visual Basic application reads from an INI file

the '700' part should be dynamic.

I am not the most frequent user of SQL Server or Oracle, but as far as I know, views are static. Anyone that knows a way to pass my INI value to the view? Or am I right and is this simply not possible. If it isn't...what is the way to go? Stored Procedures? Please someone advice..I need this as soon as humanly possible..

Regards,
Max.
LVL 19
DreamMasterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

imrancsCommented:
Use dynamic sql to create the view, like

CREATE PROCEDURE uspCreateView
   @TableName  Varchar(100)
AS

--this will create view dynamically with the of table that you will pass to the storeed procedure

EXEC ('CREATE VIEW MyView AS Select t$hfdl, t$date, t$wrko, t$beur from ' + @TableName  + where t$uitv = 1 and t$afgm = 1 and t$odat <> 0 and t$dctb = 0')


GO



--You can use this stored procedure as following

EXEC uspCreateView 'ttirbh016701'


Imran
0
DreamMasterAuthor Commented:
Thanks for the quick reply Imran,

The view already exists on the server. I guess it can be altered in the same way...

If I would run this from Visual Basic, where I just run:

com_R5_cmd.CommandText = "SELECT * FROM R5_ONTVANGST"
rst_Triton_Project.Open cmd_R5_cmd.adOpenStatic, adLockBatchOptmistic

I'd have to change that to a stored procedure I call. This stored procedure could then call the information from my INI file and possibly alter my view to the correct one.

I am strongly thinking of changing the views to one stored procedure that has both table name and fields as parameters. Let me try out some options and I will get back to this asap...

Regards,
Max.
0
rafranciscoCommented:
What you are trying to do is better done using a stored procedure.  Here's how your stored procedure will look like:

CREATE PROCEDURE GetDate @TableSuffix VARCHAR(3)
AS
DECLARE @SQL VARCHAR(2000)

SET @SQL = 'Select t$hfdl, t$date, t$wrko, t$beur from ttirbh016' + @TableSuffix + ' where t$uitv = 1 and t$afgm = 1 and t$odat <> 0 and t$dctb = 0'
EXEC (@SQL)

Hope this helps.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DreamMasterAuthor Commented:
That is what I thought rafrancisco. Sadly I cannot test it at this very moment as I do not have the clients database. I will look over the answers given and either split or accept one accordingly...

Thanks you both.. :)

Regards,
Max.
0
DreamMasterAuthor Commented:
I am going to split points between the two of you...both comments have been helpfull to me...

Regards,
Max.
0
imrancsCommented:
Glade if I could help.


Imran
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.