December 29, 2008 at 11:40 pm
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.
December 30, 2008 at 12:02 am
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
December 30, 2008 at 12:21 am
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
December 30, 2008 at 12:49 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2008 at 1:00 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2008 at 1:37 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2008 at 1:51 am
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
?
December 30, 2008 at 9:23 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply