none
create view for table ? RRS feed

  • Question

  • Hello

    I have a table. colums that id, userid, expanation, datetime, sum, category, subcategory.

    I want to create view for userid. I will do search that "SELECT * FROM table WHERE userid=@userid and (category is null or category = @category) and (subcategory is null or subcategory =@subcategory) and date between @date1 and @date2"

    how should be a view ?

    How can i create it ?

    and how can i do search ?

    Note: I use Ado.net for searching.

    Thanks.

    Thursday, April 18, 2013 8:26 PM

Answers

  • Hi,

    Or you could use create function. See the B sample ("inline table-valued function") at http://msdn.microsoft.com/en-us/library/ms186755.aspx

    My understanding is that you would like to be able to do something such as :

    SELECT * FROM dbo.MyView(1,'Category A', 'SubCategory Z','20130201','20130401')


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by MSDN.CSharp Tuesday, April 23, 2013 1:06 PM
    Friday, April 19, 2013 11:00 AM
  • Hi MSDN CSharp,

    To create a parameterized view in SQL Server 2008, we need to create function as Patrice has mentioned. According to your requirement, I think that a store procedure is enough. To pass parameter to requirement, just try the property:

    SqlCommand.Parameters 

            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@userid", SqlDbType.Int);
            command.Parameters["@userid"].Value = customerID;
    Hope it helps.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by MSDN.CSharp Tuesday, April 23, 2013 1:05 PM
    Tuesday, April 23, 2013 7:47 AM
    Moderator
  • Your SQL statements are same from performance perspective because SQL server expand views in statements. So you should choose whatever is more readable for you. If view is not used anywhere else except in this statement then I would recommend first option. In this scenario view is not needed. About parameters on views - no you can not send sql parameters to view, just to stored procedure. Indeed there is table valued functions objects in T-SQL (which are in fact parameterizied views) but you still have to make SELECT statement on this object like you do on table or view - so I think this is not what you want.

    if (helpful) then Vote();

    • Marked as answer by MSDN.CSharp Tuesday, April 23, 2013 1:05 PM
    Tuesday, April 23, 2013 8:02 AM

All replies

  • If you need a parametrized call you need to build a stored procedure rather than a view

    create procedure myprocedure

    (@userid int,

    @categoryid int = null,

    @subcategoryid int = null,

    @date1 smalldatetime,

    @date2 smalldatetime

    )

    as

    SELECT * FROM table WHERE

    userid=@userid and (category is null or category = @category)

    and (subcategory is null or subcategory =@subcategory)

    and date between @date1 and @date2


    And then you call it like this

    exec myprocedure 1, null, null, '01/01/2013', '01/12/2013'


    Ali Hamdar (alihamdar.com - www.ids.com.lb)

    Thursday, April 18, 2013 8:36 PM
  • Hello

    I know use stored procedure. I want to create a view to fast select result for per userid. But i dont know how i can use it and i create it.

    Thursday, April 18, 2013 8:53 PM
  • Create a view

    Creating a View containing one or more SQL Tables

    Another key advantage of a view is that it allows us to join multiple tables together. 

    IF ( OBJECT_ID('dbo.
    vw_occupied
    ') IS NOT NULL ) 
       DROP VIEW dbo.
    vw_occupied

    GO CREATE VIEW dbo.vw_occupied AS SELECT * FROM table WHEREuserid=@userid and (category is null or category = @category)

    and (subcategory is null or subcategory =@subcategory) and date between @date1 and @date2 GO

    Using our View

    View a single class

    SELECT  * FROM    dbo.vw_occupied

    See more information from 


    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Friday, April 19, 2013 2:30 AM
  • Hi,

    Or you could use create function. See the B sample ("inline table-valued function") at http://msdn.microsoft.com/en-us/library/ms186755.aspx

    My understanding is that you would like to be able to do something such as :

    SELECT * FROM dbo.MyView(1,'Category A', 'SubCategory Z','20130201','20130401')


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by MSDN.CSharp Tuesday, April 23, 2013 1:06 PM
    Friday, April 19, 2013 11:00 AM
  • Hello

    CREATE VIEW dbo.vw_occupied AS SELECT * FROM table WHERE userid=@userid and

    (category is null or category = @category) and

    (subcategory is null or subcategory =@subcategory) and

    date between @date1 and @date2

    and using is stored procedure that SELECT * FROM vw_occupied

    Or below will be more fast at a lot of records ?

    CREATE VIEW dbo.vw_occupied
    AS
    SELECT * FROM table WHERE userid=@userid 

    and stored procedure like below

    SELECT * FROM vw_occupied WHERE
    (category is null or category = @category) and (subcategory is null or subcategory =@subcategory) and date between @date1 and @date2   

    Which one is the best way ?

    I want to create for user ids. I have a lot of user id at a lot of records

    Table

    Userid category ......

    1       aaa

    1       bbb

    2      aaaaa

    2      ccccc

    And one more question. How can i send sql paremeters at Ado Net for view  ? same with stored procedure ?

    Thanks

     

    Friday, April 19, 2013 11:33 AM
  • Hi MSDN CSharp,

    To create a parameterized view in SQL Server 2008, we need to create function as Patrice has mentioned. According to your requirement, I think that a store procedure is enough. To pass parameter to requirement, just try the property:

    SqlCommand.Parameters 

            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@userid", SqlDbType.Int);
            command.Parameters["@userid"].Value = customerID;
    Hope it helps.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by MSDN.CSharp Tuesday, April 23, 2013 1:05 PM
    Tuesday, April 23, 2013 7:47 AM
    Moderator
  • Your SQL statements are same from performance perspective because SQL server expand views in statements. So you should choose whatever is more readable for you. If view is not used anywhere else except in this statement then I would recommend first option. In this scenario view is not needed. About parameters on views - no you can not send sql parameters to view, just to stored procedure. Indeed there is table valued functions objects in T-SQL (which are in fact parameterizied views) but you still have to make SELECT statement on this object like you do on table or view - so I think this is not what you want.

    if (helpful) then Vote();

    • Marked as answer by MSDN.CSharp Tuesday, April 23, 2013 1:05 PM
    Tuesday, April 23, 2013 8:02 AM