SQL Server 2012 :: Set Default Parameter For Function Parameter?
Jan 13, 2014
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
View 5 Replies
ADVERTISEMENT
Aug 11, 2015
@pvColumnName  VARCHAR(100) = Default,  
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
View 4 Replies
View Related
Sep 23, 2015
I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.
See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0
CREATE TABLE #temp
( Id int, Name char(30), YesNo bit )
INSERT INTO #temp (Id, Name,YesNo)
Select 1, 'a', 0
INSERT INTO #temp (Id, Name,YesNo)
[Code] ....
View 5 Replies
View Related
Sep 15, 2014
In t-sql 2012, the followinng sql works fine when I declare @reportID.
IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION
[code]...
However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:
FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)
GO
how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?
View 2 Replies
View Related
Dec 19, 2014
Is there a way to set a default value for a sp parameter using a select statement(see code bellow)
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event
View 9 Replies
View Related
Jan 30, 2007
Hi,
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
Thanks.
Kong
View 6 Replies
View Related
Oct 15, 2007
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
View 7 Replies
View Related
Jul 24, 2007
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
View 11 Replies
View Related
Aug 3, 2015
I have a multi value  parameter called "Location" and this depends on another multi valued  parameter value. The default value for the parameter "Location" comes from the another another multi valued  parameter. Now say when the default value is set for the parameter Location like the below:
The Location parameter data set has values from the Query and default values has been set as shown below:
=Iif(array.IndexOf(Parameters!Program.Value,"A")>-1,nothing,"N/A")
I get an error on preview saying that . The Default Expression for the report parameter "Location" contains error:
Unable to cast object of type 'System.String' to type 'System.Array'.
View 2 Replies
View Related
Nov 19, 2014
I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
View 9 Replies
View Related
Apr 22, 2015
I have a function that accepts a date parameter and uses getdate() as its default value. If a date is passed in, I'm going to have to find records using the datediff method based on input. If no date is passed, I am going to bypass the datediff logic and search for records based on a column called "is_current" which will reduce the query time.
However, I don't know how to tell if the date value in the function came from an input or was the default.
View 4 Replies
View Related
Jul 15, 2014
I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.
currently I am ordering it by:
c.CurrentItemNumber
, p.PriorItemNumber
among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).
Is this possible?
This is my stored procedure for reference:
ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)
[Code] ....
View 5 Replies
View Related
Jul 29, 2015
In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.
USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
and LKC.seq = 1
) AS LKC2 (comboID,lockID,seq,combo)
What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes
and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus
can you pass a parameter value to the CTE that I just listed above? If so, would you show me
the t-sql to accomplish this goal?
View 2 Replies
View Related
Jan 29, 2015
Is it possible to pass multiple values to a TVF, such as using an IN clause?
View 6 Replies
View Related
Jun 2, 2014
I have a procedure (a) where i call another procedure (b) passing @message as a parameter to procedure B.
Ex:
Create procedure a
as
Begin
declare @prevyear
exec b @message = 'avvasdva' + cast(@prevyear as varchar)
End
When i execute above procedure, i get error at + sign i.e. @message parameter. how can i concatenate string with another parameter when passing parameters
View 3 Replies
View Related
Mar 18, 2004
I'm using a stored procedure that receives one parameter namely @EmployeeID INT
but when I want to give this parameter a default value, my SP fails.
I did it like with a varchar where it works.
---
@Employee INT = '%'
---
Is this correct or did I use a wrong syntac/wildcard?
Greetings,
Geoff
View 6 Replies
View Related
Apr 26, 2007
Hi
I have a report parameter that may contain one item or it may contain more than one item. Is there a way I can default the parameter if it contains one item, to that item. But have no default if there is more than one option?
Cheers
View 3 Replies
View Related
Oct 21, 2014
Assume I built a stored proc (dbo.testproc) that will return the OUTPUT parameter @RandomInteger.
I could pass a specific value for the parameter...
EXEC dbo.testproc 7
Or I could return a value from the proc...
DECLARE @ReturnInteger
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger
But I want to do both which, if this actually worked, might look like this...
DECLARE @RandomInteger
SET @RandomInteger = 7
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger
I want to pass a specific value and return the result from the proc. Do I need to use two parameters for this?
View 7 Replies
View Related
Jun 16, 2015
I need looping through each row of Table valued parameter. I have user defined type
CREATE TYPE [dbo].[PCS_SPEC_ATTR_VALUE] AS TABLE(
[ATTR_NAME] [varchar](256) NULL,
[ATTR_VAL] [varchar](4000) NULL
)
I am using this type in my procedure like
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@P_PART_CLS_ATTR PCS_SPEC_ATTR_VALUE readonly
I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting.. How to access the values in row by row?
View 4 Replies
View Related
Sep 29, 2015
i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....
View 5 Replies
View Related
May 11, 2007
Hi... need to default the customerId on a sub form.
Insert string is:
InsertCommand="INSERT INTO CustomerNotes(Note, CustomerID) VALUES (@Note,@KEY)"
Parameter:
<asp:Parameter Name="KEY" DefaultValue='123456789'/>
Need to make the '123456789' the value of the request.querystring('ID")
How is this done?
Thanks in advance....
View 2 Replies
View Related
Jul 7, 2014
in a StoredProc I want depending on whether a parameter is passed formulate the Where clause accordingly.
If parameter IS NOT NULL
THEN take all Rows
Else take the row with the ID (INTEGER)
How do I formulate the where clause?
View 3 Replies
View Related
Jun 12, 2008
I am using a query to grab information about the parameters of stored procedures. I am trying to determine if a parameter has a default value. I have tried adding a default value to the parameter of the stored procedure, but the returned value of params.has_default_value stays at zero. What is going on?
Here's the query to pull the stored procedure information:
SELECTprocs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output,
params.has_default_value
FROMsys.procedures procs
LEFT OUTER JOINsys.all_parameters params
ONprocs.object_id = params.object_id
LEFT OUTER JOINsys.types types
ONparams.system_type_id = types.system_type_id
ANDparams.user_type_id = types.user_type_id
WHEREprocs.is_ms_shipped = 0
ANDprocs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
ORDER BYprocname,
params.parameter_id
Here's the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = 'a'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT*
FROMBENEFICIAL_USES_DM
WHEREDISPOSAL_AREA_NAME = @DISPOSAL_AREA_NAME
END
END
View 14 Replies
View Related
Dec 26, 2007
I have a report which has paramets as startdate and enddate. I want to set default value as yesterday. So when It opens it shows yesterday data.
I have a expression like this.
(DATEDIFF(dd, 0,MoneyFlowTransaction.ProcessedTime) = DATEDIFF(dd, 0, GETDATE()) - 1)
But I'm wondering how I set this as dafault value.
can anyone help me pls?
View 10 Replies
View Related
Sep 28, 2007
Hi everyone,
I have a report that a user can enter parameters in. If the user opens the report the parameter box is at the top and expanded. The report is also linked to by another report, so when a user clicks on something in this other report it becomes the parameter for the one I'm having the problem with. The problem is the user can select the number of days that are displayed, so when I link from the other report I just pass 10 by default, but now in the report the parameter box is collapsed and I'm worried that users won't find it. Is there a way to force it to be expanded? Sorry this is probably confusing to read.
Thank you,
Keith
View 3 Replies
View Related
May 9, 2008
I set parameter as Multipul selection.
how to set it as "Select all" by default?
Thank you!
View 12 Replies
View Related
Nov 2, 2007
Hi Folks,
I'm having a problem showing my boolean default parameter on report server in our test environment.
The report server shows my boolean parameter with a yes and no radio button. I assigned my boolean parameter with a 'No' default. The default shows and runs fine when I run the report within Visual Studio.(I hope I'm using the correct terminology.) However, on the report server the 'No' radio button is not checked by default. What am I doing wrong?
Thanks in advance for your assistance.
View 3 Replies
View Related
Jan 30, 2007
Hi all,
Does anybody know how to set the parameter's default value dynamically?
I'm working on a report with some parameters against datacube, and I hope the default value of one of the parameters could be set dynamically based on the user's login.
Thanks,
Jone
View 1 Replies
View Related
Oct 19, 2007
While converting Crystal reports to Reporting services I noticed that the existing crystal reports puts todays date as default value for the date parameter. Users also want it that way. I was unable to find a way to put a default value for a parameter in SSRS. I am a newbie in this so may be I missed something.
View 1 Replies
View Related
May 21, 2008
I am trying to set the default date in my datetime parameter to yesterday's date, so the user does not have to select using the datepicker calendar. I am using Visual Studio 2005.
I put the following code into the Report Parameters non-queried default values function line, but it adds the timestamp to the date.
=DateAdd(€?d€?, -(WeekDay(Now()))+1, Now())
How can I get yesterday's date to default but without the timestamp?
Thanks!
Marissa
View 6 Replies
View Related
Aug 21, 2007
Friends,
I have a report which has a Multi Value Parameter. I want the default to be selected as 'Select All'. Can anyone tell how to get this?
Thanks,
S Suresh
View 3 Replies
View Related
Oct 29, 2007
I have two parameters for my report. For simplicity, let's say the first one is a pet and contains of a combobox with the avaliable values: "Dog", "Cat" and "Bird".
The second parameter is favourite food and contains the values "Fish", "Seed", "Sousage" and "Apples".
Each of the Pet's has a 'default' favourite food that should be selected by default when a pet is selected. So, when I select 'Cat' in the first combobox, 'Fish' is selected in the second. This works fine, by setting the default value in the second parameter to the value of a dataset (the dataset retrieves the default favourite food for the selected pet).
The problem occurs when I first select a cat, recieving 'fish' in the second parameter, and then selects 'Bird' in the first parameter. What happens is that the second parameter is NOT changed from 'Fish' into 'Seed'. I guess this is because the second parameter already has a value ('fish') so a new default value is not calculated.
So, my question is, is there any way to force a 'recalculation' of the default value of my second parameter when the first parameter is changed?
Regards Andreas Brosten
View 4 Replies
View Related
May 31, 2007
Any way to have the default value of a parameter for a multi select parameter be the first value of the query of that parameter?
View 7 Replies
View Related
