Beginner help - creating stored proc and passing date parameter
-
Hi, can someone please help me in making this stored procedure work with passing a string for date? I was able to write a successful one that passes in an ID and date. But the date, I pass in isn't what I want. Here is what I am talking about. If you look at my stored proc, i'm passing in 4 ints. I would like to pass just the id, '2005-03-14'.
Works this way:
execute getXMLStationData 479, 2005, 3, 14
Want it to work this way:
execute getXMLStationData 479, '2005-03-14'
===========================================
create proc getStationData
@stationid int,
@yearpart int,
@monthpart int,
@daypart int
as
select StationRatingsWeekly.stationID,
StationRatingsWeekly.songID
from
sandbox.dbo.lcEditorialStationRatingsWeekly StationRatingsWeekly with (nolock)
left outer join framework.dbo.getStations st
on StationRatingsWeekly.stationID = st.stationID
where
StationRatingsWeekly.localityID = 249691
--and StationRatingsWeekly.yearpart = 2005
--and StationRatingsWeekly.monthpart = 3
--and StationRatingsWeekly.daypart = 14
and StationRatingsWeekly.yearpart = @yearpart
and StationRatingsWeekly.monthpart = @monthpart
and StationRatingsWeekly.daypart = @daypart
and StationRatingsWeekly.stationID = @stationid
================================================
Thanks!
-
Search BOL for the DATEPART() function. You can use this in the body of your SP to associate the year, month and day parameters; or simply use it in-situ!
-
execute getXMLStationData 479, '2005-03-14'
===========================================
create proc getStationData
@stationid int,
@dte datetime
as
select StationRatingsWeekly.stationID,
StationRatingsWeekly.songID
from
sandbox.dbo.lcEditorialStationRatingsWeekly StationRatingsWeekly with (nolock)
left outer join framework.dbo.getStations st
on StationRatingsWeekly.stationID = st.stationID
where
StationRatingsWeekly.localityID = 249691
and StationRatingsWeekly.yearpart = year(@dte)
and StationRatingsWeekly.monthpart = month(@dte)
and StationRatingsWeekly.daypart = day(@dte)
and StationRatingsWeekly.stationID = @stationid
-
Thanks that works perfectly. Although, I need to create an xml output from this. So all I did was append the FOR XML EXPLICIT. Now, when I run this I get the following error.
create proc getXMLStationData
@stationid int,
@dte datetime
as
select StationRatingsWeekly.stationID,
StationRatingsWeekly.songID,
from
sandbox.dbo.lcEditorialStationRatingsWeekly StationRatingsWeekly with (nolock)
left outer join framework.dbo.getStations st
on StationRatingsWeekly.stationID = st.stationID
where
StationRatingsWeekly.localityID = 249691
and StationRatingsWeekly.yearpart = year(@dte)
and StationRatingsWeekly.monthpart = month(@dte)
and StationRatingsWeekly.daypart = day(@dte)
and StationRatingsWeekly.stationID = @stationid
order by
StationRatingsWeekly.stationID
for XML EXPLICIT
===========================================================
execute getXMLStationData 479, '2005-03-14'
Server: Msg 6820, Level 16, State 1, Procedure getXMLStationData, Line 5
FOR XML EXPLICIT requires column 1 to be named 'TAG' instead of 'stationID'.
I know this leads into XML topic, but what needs to be changed? Thanks!
-
Would you mind to read at least BOL?
Article "Using EXPLICIT Mode" for example?
Or some basic books about XML in SQL 2000?
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply