April 25, 2005 at 4:51 pm
I am only cutting and pasting a part of my stored procedure...
I have an input parameter which is @p_sitecode.
Now i want my stored procedure to work as, WHEN I DON'T PASS ANYTHING FOR THE PARAMETER @p_sitecode THEN IT SHOULD SELECT ALL THE SITECODE IN THE TABLE.
The column is called siteglobalcode in my select statement.
So how do i do this?
CREATE PROCEDURE missing_iconnt_data(@p_sitecode INTEGER,@p_fromsalesdate DATETIME,@p_tosalesdate DATETIME)
AS
BEGIN
DECLARE
@v_validdate datetime
DECLARE cur_missing_data CURSOR for
SELECT sitename,siteglobalcode,salesdate,dbsequenceid
FROM crm_stg_icon
WHERE salesdate BETWEEN CONVERT(DATETIME, @p_fromsalesdate) and CONVERT(DATETIME, @p_tosalesdate)
and siteglobalcode = @p_sitecode
................
Thank you very much!
April 25, 2005 at 5:37 pm
Edited to correct syntax:
Where...And siteglobalcode =
Case
When @p_sitecode is null then siteglobalcode
Else @p_sitecode
End
April 25, 2005 at 6:20 pm
If siteglobalcode can be null then you may have to do something like this:
Where...And Isnull(siteglobalcode,'') =
Case
When @p_sitecode is null then Isnull(siteglobalcode, '')
Else @p_sitecode
End
Of course the second Isnull parameter would have to be something that is an invalid siteglobalcode.
Perhaps someone else can confirm.
April 26, 2005 at 12:00 am
You probably want to change def of proc to
CREATE PROCEDURE missing_iconnt_data(@p_sitecode INTEGER = Null,@p_fromsalesdate DATETIME,@p_tosalesdate DATETIME)
This means that if @p_sitecode is not specified it will take value Null
Your WHERE then needs to become
WHERE salesdate BETWEEN CONVERT(DATETIME, @p_fromsalesdate) and CONVERT(DATETIME, @p_tosalesdate)
and siteglobalcode = IsNull(@p_sitecode,siteglobalcode)
this means when @p_sitecode has a proper value it gets rows where siteglobalcode = that value otherwise it gets rows where siteglobalcode = siteglobalcode (i.e. any row where siteglobalcode is NOT null - I assume here that siteglobalcode always has a proper value)
James Horsley
Workflow Consulting Limited
April 26, 2005 at 8:04 am
I would change the order of the parameters like this:
CREATE PROCEDURE missing_iconnt_data
(
@p_fromsalesdate datetime,
@p_tosalesdate datetime,
@p_sitecode int = NULL
)
That way, you can call the stored procedure this way:
EXEC missing_iconnt_data @fromdate, @todate
If @p_sitecode int = NULL comes first, then you must either include a value (although that value could be NULL), or used named parameters.
April 30, 2005 at 7:37 am
and (@p_sitecode is null or siteglobalcode = @p_sitecode)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply