June 8, 2009 at 8:19 am
I want allow some users to create read only select query on the fly from a web based app.
I would like to give them options of tables to choose from etc.
I tried making a stored proc like
declare @Table table
set @Table='mytablename'
Select top 10 * from @Table
I keep getting must declare the variable @table I tried declaring it as a table and varchar to no avail.
I would also like to give the option of what columns they want to get etc.
I guess in the end I want the user to be able to create a custom query from a asp.net web app.
Similar to the way ACT from sage works.
June 8, 2009 at 8:29 am
thinking this through I guess I would create some views than the user would just choose the view and the columns form the view instead of an actual table.
Then they would not have to worry about joins etc. these users are not database people at all. Still not sure how to accomplish my main goals here.
June 8, 2009 at 8:37 am
how about creating your queries dynamically based on the columns they choose?
After they select the column you will know what tables they want and ifyou have acustom table that defines the joining between the different tables then you could jsut dynamically build your query about there column selection.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 8, 2009 at 10:41 am
I am not sure How I would do that either....
June 9, 2009 at 2:12 am
could you give me an example of two tables with a few columns from each table and then the relationship for those tables in a create script and then I will show you a possible solution
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 9, 2009 at 4:31 am
I would probably use a view to do this I think that's best for these circumstances.
here's the tables anyway. The view I have connects these tables and another one.
people table
peopleID int --Primary Key,
Firstname varchar (50),
LastNAme varchar(50),
Phone varchar (20),
DateEntered varchar (20),
LastUser Varchar (20)
-----
Visits Table
VisitID int, --PK
Date Smalldatetime,
Item varchar (25),
DateEntered smalldatetime,
LastUser (20)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply