a problem in writting a storeprocedure

  • hello,

    i have a table with tis design:

    ID int

    Title nvarchar(500)

    Body nvarchar(max)

    Date_Time datetime

    Cat1_ID int

    Cat2_ID int

    Lang_ID int

    i want to write a storeprocedure to get (Date_Time) and a (Cat1_ID) or (Cat2_ID)

    and then give me all of the records with that or those Category in that date.

    thanks.

  • nazaninahmady_sh (12/29/2008)


    hello,

    i have a table with tis design:

    ID int

    Title nvarchar(500)

    Body nvarchar(max)

    Date_Time datetime

    Cat1_ID int

    Cat2_ID int

    Lang_ID int

    i want to write a storeprocedure to get (Date_Time) and a (Cat1_ID) or (Cat2_ID)

    and then give me all of the records with that or those Category in that date.

    thanks.

    tell me the format

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • i thinkk i need 4 variable for enter the parameters:

    1: Category name

    2: Category desc

    3:date&Time1

    4:date&Time2

    ii want this store procedure ask me these three and then give me he records:for example:

    ID Title Body Date_Time Cat1_ID Cat2_ID Lang_ID

    1 a aaaaa 01/01 Weather Internal 1

    2 b bbbbb 12/05 Water External 2

    3 c ccccc 01/05 water Internal 1

    4 d dddddd 01/03 water Internal 1

    Now

    1: Category name: Cat1_ID

    2: Category desc:water

    3:date&Time1 :01/03

    4:date&Time2 :01/05

    and it must give me this record:

    3 c ccccc 01/05 water Internal 1

    4 d dddddd 01/03 water Internal 1

  • One thing that is inconsistent in your question is that you have defined Cat1_ID and Cat2_ID as int in your table definition and then show character values in the example data. You may want to clarify that. Please read the articles in my signature line to learn how to better post sample data. In my answer I am assuming that Cat1_ID and Cat2_ID are varchar columns

    So, if I understand correctly, you want a stored procedure that accepts 3 parameters:

    1. The Category column to filter on, either Cat1_ID or Cat2_ID

    2. The data on which to filter the selected Category column. In your example, water.

    3. A date parameter to filter the oddly named Date_Time column on.

    If this is correct your SP would require either an IF ELSE or dynamic SQL. Here is a Dynamic SQL Solution:

    Create Procedure test

    (

    @filter_column nvarchar(10),

    @filter_value nvarchar(25),

    @date datetime

    )

    As

    Declare @SQL nvarchar(max)

    Declare @parameters nvarchar(Max)

    Set @parameters = '@filter_column nvarchar(25), @date datetime'

    Set @SQL = 'Select ID, Title, Body, Date_Time, Cat1_ID, Cat2_ID, Lang_ID from table Where '

    Set @SQL = @SQL + @filter_column + ' + @filter_column and Date_Time = @date'

    Exec sp_executesql @SQL, @parameters, @filter_column = @filter_column, @date = @date

  • Do you want only rows that match one of the dates exactly or rows with dates between the 2 dates?

    You can modify the code I provided in my previous post to add the second date parameter to the SP and the dynamic SQL. You should be able to figure it out from my example.

  • sorry dont you use from ( filter_value) ?

    and i dont understand

    Set @sql = @sql + @filter_column + ' + @filter_column and Date_Time = @date'

    Exec sp_executesql @sql, @parameters, @filter_column = @filter_column, @date = @date

  • Sorry, it's 3:30 in the morning/night where I am and I couldn't sleep, so I thought I'd answer some questions.

    The first part is building your select statement in string and I am using parameters in the string.

    sp_executesql is the safest way to use dynamic SQL. Basically you pass in the statement, @sql, and the parameters in the statement, @paramters, then the the values for the parameters (parameter_name = value). My example should be:

    Exec sp_executesql @SQL, @parameters, @filter_column = @filter_value, @date = @date

  • Sorry

    i think you understand mu problem correcctly but your Solution isdifficult for me

    i want to ask could we complete this WHERE clause and solve the problem

    Create Procedure test

    Declare @filter_column nvarchar(10)

    Declare @filter_value nvarchar(25)

    Declare @date datetime

    Select ID, Title, Body, CAt1_ID , Cat2_ID, Lang_ID from DB1.dbo.NewsRoom

    where

    ?

  • The where clause is in the SQL statement defined in @sql and the parameters are defined in @parameters and then passed in the execute SQL. If you run this code in a test database:

    -- create table for testing

    Create Table dbo.NewsRoom

    (

    id int,

    title char(1),

    body varchar(10),

    date_time datetime,

    cat1_Id varchar(10),

    Cat2_Id varchar(10),

    lang_Id int

    )

    -- create test dats

    Insert Into dbo.NewsRoom

    Select

    1, 'a', 'aaaaa', '01/01/09', 'Weather', 'Internal', 1

    Union All

    Select

    2, 'b', 'bbbbb', '12/05/08', 'Water', 'External', 2

    Union All

    Select

    3, 'c', 'ccccc', '01/05/09', 'water', 'Internal', 1

    Union All

    Select

    4, 'd', 'dddddd', '01/03/09', 'water', 'Internal', 1

    -- create test procedure

    Create Procedure usp_test

    (

    @filter_column nvarchar(10),

    @filter_value nvarchar(25),

    @date datetime

    )

    As

    Declare @SQL nvarchar(max)

    Declare @parameters nvarchar(Max)

    Set @parameters = '@filter_column nvarchar(25), @date datetime'

    Set @SQL = 'Select ID, Title, Body, Date_Time, Cat1_ID, Cat2_ID, Lang_ID from dbo.NewsRoom Where '

    Set @SQL = @SQL + @filter_column + '= @filter_column and Date_Time = @date'

    Exec sp_executesql @SQL, @parameters, @filter_column = @filter_value, @date = @date

    Return

    -- execute test procedure

    Exec usp_test

    @filter_column = 'cat1_id',

    @filter_value = 'water',

    @date = '1/5/09'

    -- drop table

    Drop table dbo.NewsRoom

    -- drop procedure

    Drop Procedure dbo.usp_test

    -- data returned

    IDTitleBody Date_Time Cat1_ID Cat2_IDLang_ID

    3cccccc2009-01-05 00:00:00.000water Internal1

    THe stored procedure works as asked.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply