Need stored procedure that shows one value for null and another for not null

I have a stored sprocedure with two parameters that currently searches a table for a matching record see SQL below:

**************************************************************

@.WorkOrderNum numeric(18,0)

,@.StackNum numeric(18,0)

AS

--This procedure is used in iFIX. It looks for matching

--Work Order Number / Stack Number combination.

SELECT

work_order_no

,stack_no

FROM

dbo.prod_data

WHERE

(dbo.prod_data.work_order_no = @.WorkOrderNum)

AND (dbo.prod_data.stack_no = @.StackNum)

******************************************************************************

What I need is a stored procedure that will look for a matching criteria and if it finds some it will return a value of "2", if it does not find any criteria it needs to return a value of "1". This value does not need to be stored, for display only.

Any help would be greatly appreciated

You could do below:

Code Snippet

select case when exists(

SELECT *

FROM

dbo.prod_data as p

WHERE

p.work_order_no = @.WorkOrderNum

AND p.stack_no = @.StackNum
) then 2 else 1 end as matched

|||

Thanks it worked perfectly

Need Parameter Optionally Omitted

Is it possible to have a parameter ignored? I have a report that I am
web-deploying with a large set of parameters, but a user may not wish to
include some for a given execution of the report. For example, I have a
Boolean checkbox that I cannot get the report to ignore. I have tried
toggling the following settings: allow null, allow blank, setting defaults,
not setting defaults. I then recast as char and used a true/false drop-down
but still could not get the report to optionally use it.
Thanks for any helpMike,
It sounds like you're using the Report Parameter to make a Query
Parameter to use in your SQL query. If that's the case, the problem may
be that when the parameter is null, your SQL query fails because it's
expecting a query parameter that doesn't exist.
If this is the case (and I can't be sure since you didn't give the
exact error) what you can do is write a function that checks all of
your parameters, performs whatever logic you need, and returns a sql
statement as a string. In your dataset you will have something like
=Code.GetSQL()
instead of the sql statement you have now.
Just make sure you're using the Generic Query Designer instead of the
Query Builder, or it'll have a fit.|||I am not getting an error, just incorrect results from the query when I
don't want the param used. If I delete the param, I get the results I would
expect if the param were ignored. In the grid colum 'Criteria' I add
'@.param'. Then from the menu Report\Report Parameters I add the addtional
attributes for the param as I mentioned earlier.
I am not a SQL power-user so I am tring for a modest report, accepting some
of the known limitations of the tool esp regarding use of params. I was
hoping to at least get basic function though.
Also, Is there a distinction between a report vs query paramter?
Many thanks!

Need Help: How to schedule a report that has parameter?

Hi All:
I have created a report that is based on stored procedure and also has some
parameters. Manually after entering proper parameter values, I can run the
report properly.
My questions are:
1. How can I schedule this report?
2. While scheduling this report, is there anyway I can provide the
corresponding proper parameter values so that I can get my required report?
I would appreciate your help.
Thanks.
SamHave you try to use a subscription?
"Sue" wrote:
> Hi All:
> I have created a report that is based on stored procedure and also has some
> parameters. Manually after entering proper parameter values, I can run the
> report properly.
> My questions are:
> 1. How can I schedule this report?
> 2. While scheduling this report, is there anyway I can provide the
> corresponding proper parameter values so that I can get my required report?
> I would appreciate your help.
> Thanks.
> Sam
>|||Yes. But did not work out properly. Need help.
Here is my Case
+++++++++++++++++++++++++++++
I have a common report object residing on Reporting Service. The report
shows Monthly Activity for a Department. This report is based on a Stored
Procedure and has Department ID as a parameter.
Through an application different departments either can run this report or
create a schedule so that at the end of each month, any department can get
the report related to only its data.
How can I do that?
+++++++++++++++++++++++++
"Soan" wrote:
> Have you try to use a subscription?
> "Sue" wrote:
> > Hi All:
> >
> > I have created a report that is based on stored procedure and also has some
> > parameters. Manually after entering proper parameter values, I can run the
> > report properly.
> >
> > My questions are:
> >
> > 1. How can I schedule this report?
> >
> > 2. While scheduling this report, is there anyway I can provide the
> > corresponding proper parameter values so that I can get my required report?
> >
> > I would appreciate your help.
> >
> > Thanks.
> >
> > Sam
> >
> >

Need help writing stored procedure involving dates

I am trying to write a stored procedure which would execute following logic:

- The stored procedure takes 2 optional parameters @.StartDate and @.EndDate

@.StartDate Datetime = null
@.EndDate Datetime = null

- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @.StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @.EndDate as the ED and MIN() of the Date field as SD

I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null


AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND LName LIKE ISNULL(@.LName,'%' + NULL)
AND ID LIKE ISNULL(@.ID,'%' + NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

Any help would be appreciated
Thanks in advance :)I'm a little confused. What is your question ?

did you mean except for incoporating NULLS ?

Cheers,
-Kilka|||I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
To make it simple lets say I have a simple table with 3 columns: FName, LName and Date

FName LName Date
Aab 02/05/2005
Abc Bb 02/06/2005
Aaaa Bbb
02/07/2005
Baaaa Bbb
Baaca 02/07/2005
Caa Bbbb 02/07/2005

As can be seen that FName, LName and Date columns accept NULL values.
Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @.FName, @.LName, @.StartDate and @.EndDate and do a search on this table.
I am having trouble handling these NULL fields.

When I execute the stored procedure, I should get results as follows:
- If user enters @.FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
- Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields

I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
Thanks again for your time :)|||Do you really have '%' + NULL in the code? I would think you just want '%'.

Are you getting any data back from this query when you have data that looks like your example?|||You guys are good sorry forgot to update the stored procedure.
No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:

CREATE PROCEDURE SearchDocumentTable

@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null

AS

IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document

Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'

From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND (LName LIKE ISNULL(@.LName,'%') OR LName IS NULL)
AND (ID LIKE ISNULL(@.ID,'%') OR ID IS NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode

GO

This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
Any ideas how can I modify the stored procedure to avoid returning null fields.

Thank you again guys, I really appreciate your help|||Do you get the correct results if you take out OR LName IS NULL and
OR ID IS NULL and OR DateInputted IS NULL?

If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.|||First, decide on precedence. What if the user passes all three parameters?

Assuming the precedence is LastName, FirstName, Date:Declare @.t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)

Insert @.t (FirstName, LastName, EndDate)
Select 'Kim', 'Cat', GetDate()
Union
Select 'Pat', 'Dog', Null
Union
Select 'Ted', Null, GetDate()
Union
Select 'Jim', Null, Null
Union
Select Null, 'Fox', GetDate()
Union
Select Null, 'Fox', Null
Union
Select Null, Null, GetDate()
Union
Select Null, Null, Null

Select * From @.t

Declare @.FirstName varchar(3)
, @.LastName varchar(3)
, @.EndDate datetime

Select @.LastName = 'F'

Select *
From @.t
Where (@.LastName Is Not Null And LastName Like @.LastName + '%')
Or
(@.LastName Is Null And @.FirstName Is Not Null And FirstName Like @.FirstName + '%')
Or
(@.LastName Is Null And @.FirstName Is Null And EndDate > = Coalesce(@.EndDate, '01/01/1950') )|||FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.

Need help with UDF useage. Trying to get away without using cursor.

I have a Function (say X) that takes 2 parameters and returns back a table result of multiple records.

And I have a query (say Q) that return rows of 2 columns that I need to feed Function X.

They way I do it right now is I have a cursor that loops through the result of Query Q
and call Function X as I pass the 2 values the the function.

As Function X return with the result set, I load it into a temporary table.

At the end of the cursor processing, I query the temporary table to return the complete result set.

Is there a way do this without using a cursor?

Here is my Function X top part:

alter FUNCTION ReturnItem
(
@.tableName varchar(50),
@.ItemID int
)
returns @.returnTable table
(
ItemName varchar(50),
ItemValue varchar(50),
[Timestamp] datetime
)

JB..

You want to use the CROSS APPLY capability of SS2k5 to "apply" the rows of one table to a UDF. Here is an example:

CREATE TABLE QuerySource
(
c1 INT,
c2 INT
)

INSERT QuerySource VALUES (1,1)
INSERT QuerySource VALUES (2,3)
INSERT QuerySource VALUES (10,15)
INSERT QuerySource VALUES (16,13)

CREATE FUNCTION ReturnItem
(
@.p1 INT,
@.p2 int
)
returns @.returnTable table
(
AddResult int,
SubtractResult int,
TimesResult int,
DivideResult int
)
AS
BEGIN
INSERT @.returnTable SELECT @.p1+@.p2, @.p1-@.p2,@.p1*@.p2,@.p1/@.p2
RETURN
END

SELECT *
FROM QuerySource qs CROSS APPLY dbo.ReturnItem(qs.c1, qs.c2)

|||Hello. Thank you very much for you help. That is exactly what I wanted to do. I never knew such feature exist. Anyway, I was afraid that when the number of data being passed becomes really big, the cursor will slow things down hence, I have to find this solution.

Thank you again.

JB..