Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: sql view

  1. #1
    Join Date
    Sep 2010
    Posts
    19

    Question Unanswered: sql view

    i need to replace a table with a query ,
    so i thought for using a views , but the
    problem that views can't take parameters !!
    so how can i replace a table with a query ??

    thnx

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but tables can't take parameters either !!

    perhaps you can explain what you're actually trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    19

    sql

    ok , thnx 4 ur help in advance
    what i need is this ,
    i have table in my database , this table have to delete and replace it with queries , the queries come from access data base and they take parameter , so the query of mine ( which is stored procedure ) take a parameter ,
    the problem is , that when i try to see where the deleted table is used ,
    it was used in view ! so i can't call stored procedure inside view .
    i can't find solution .

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try creating a User-Defined Table function rather than a view. UDFs are not as efficient, but they can take parameters and they can comprise multiple statements like a Stored Procedure.

    You will need to check first, though, whether MS Access can recognize and call UDFs. It may not. Are you using an Access Database with linked tables, or are you using an MS Access Data Project linked to the SQL Server database?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2010
    Posts
    19
    no , what iam working on is converting from access to sql data base , so i have to convert all access to sql ,,,,
    how can i replace table with query !!! ? and if that query take parameter , how i will use it instead of the deleted table ??

    thnx

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    A table contains data.

    A query is simply a specialized view of that data.

    You say that you are going to delete the table and you want a query to function in its place.

    That does not make sense.

    You first have to have data (table) before you can have a query.

    Where is the table going in this arrangement you are talking-about?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Sep 2010
    Posts
    19
    i will explain to you

    i have local tables in my database ,
    i need to delete those tables ,
    and when i need data that those tables have , i wanna to do some queries/ views so i can get those data again ,
    suppose you have data for each user , you don't wanna to keep it in tables ,
    when you need it you will perform query and the data come

    i hope you understand me now

    thnx

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    So the original source of this data is now, or will be, a table (or tables) in SQL Server and you want to write something that behaves like an Access query, allowing you to input parameters, run it, and it will return data, just like an Access query???

    If that is what you want, a stored procedure does that.

    Is the front end of this application still going to be in MS Access?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Sep 2010
    Posts
    19

    towerts our

    no it will be move all to sql database , the MS access will be deleted
    .......
    i tried the stored procedure and yes it works ,
    but the problem is the i need to replace where ever i used the table with
    the new stored procedure , and this's not work ,
    because the table was used in view , and i know that view can't call stored procedures or take parameters .

    so how can i do it
    thnx

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A user-defined function can be called referenced like a table or a view, and takes parameters.

    If this won't work for you, then I suspect the real problem is that you are used to MS Access prompting you with pop-dialogs for view parameters. SQL Server will NOT do this. You are going to need to rewrite your code to support an application that will prompt the user for parameters and supply them to the database when the query is called.

    If you are abandoning MS Access as an interface, what are you planning to use instead? SQL Server does not supply an application user interface.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Sep 2010
    Posts
    19
    there's new design for UI in .net ,
    i try functions also but it doesn't work to me .......
    how i can replace the deleted table field which is used in the view !!!!!

  12. #12
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Well then, your .NET UI should have an interface that allows the entry of parameters, and then calls a SQL stored procedure with those parameters.

    That is the simplest way to do it.

    The way you are describing this, there is a bit of confusion. Maybe draw it out and include the drawing as an attachment?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  13. #13
    Join Date
    Sep 2010
    Posts
    19
    thank you all ,
    i will try and try and tell you what happen with me ,

  14. #14
    Join Date
    Sep 2010
    Location
    Victoria, Australia
    Posts
    4

    Good transitional product

    Quote Originally Posted by blindman View Post
    ...

    If you are abandoning MS Access as an interface, what are you planning to use instead? SQL Server does not supply an application user interface.
    I recently was involved in transitioning a company away from Access and on to SQL Server. A good transitional product I've found is Iron Speed (I'm not affiliated with them). It's a .NET project code generator front end - you point it to a database and it will build a web-based front end to it. Takes tables or views, but I haven't found a way to use it for stored procs yet.

    When you build your view in SQL Server Management Studio, pick the columns you need to search on and put them at the top of the list - IronSpeed will automatically build search parameters for those items as drop-down boxes.

    Takes a bit of getting used to, but it's got it's own query generator (not quite as good as SSMS' view builder) and the resulting code it generates is pure three-tier VB or C#, aspx project code. Builds deployment .MSI's, copy over to your web server and hit the setup.exe.

    Saved us a major contract. I swear by it a lot more than I swear at it, if you know what I mean.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by NefariousWheel View Post
    I swear by it a lot more than I swear at it, if you know what I mean.
    That's all I ask of any productivity tool.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •