What SQL Function Criteria string replaces [forms]![myForm].[myField]?
I have a function that I want to pass criteria to from a drop down list. I tried using the same Access string in the Function but it does not work.
I am trying to do a query similar to this:
SELECT * FROM TRANSACCTION_HISTORY WHERE TXN_DATE=@RepDate
For the query parameter @RepDate, I would like to pass a function to it. The function is a calculation of date based on today's date (e.g. Today() function to be simple). So that users don't have to type in the date every time.
Now the question is: is it really possible to do that in RS? Because I am getting a type conversion error, when I put Today() in the Define Query Parameters disalog box.
Thanks for helping!
I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:
Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.
The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.
Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.
I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report?
THANKS!
p.s. I tried searching for other postings on this without any luck.
Hi
i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :)
colums:
VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
FROM tb_AVVirusLog INNER JOIN
__CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(__CustomerMachines002.folder_id = @folderId) [OR
(CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')]
GROUP BY tb_AVVirusLog.VLF_VirusName
HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
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
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies View RelatedOk, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)
Hi,
this has started from me wanting to write a report that allowed the user to choose from a drop down list of how many months they want the report to cover. IE i want the report for the last 3 months, or 5 months or ...
To do this i created a report with a value list parameter (values are negative integers to give the necessary start date for the reported rows) and had that parameter in the where clause, along the lines of ...
select *blah*
from *blah*
where ( *myfield* > dateadd (mm, @MonthParam, GetDate())
i get a sql error (ie an error from the database not reporting services) saying that the variable hasnt been declared.
I have worked around the problem in this instance but is there a 'feature' in RS that means report parameters cannot take part in functions?
TIA
I am writing a function which will take two parameters. One the fieldto be returned from a table and second parameter is the ID of therecord to be returned.Problem is it's not returning the value of the field specified in theparameter but instead returns the parameter itself. Is there afunction that will get the parameter to be evaluted first?ALTER FUNCTION [dbo].[getScholarYearData](-- Add the parameters for the function here@FieldName varchar(50), @ScholarID int)RETURNS varchar(255)ASBEGIN-- Declare the return variable hereDECLARE @ResultVar varchar(255)-- Add the T-SQL statements to compute the return value hereSELECT @ResultVar=EXECUTE(@FieldName)FROM dbo.qmaxScholarYearID INNER JOINdbo.tblScholarYears ONdbo.qmaxScholarYearID.ScholarID = dbo.tblScholarYears.ScholarID ANDdbo.qmaxScholarYearID.MaxOfScholarYearID =dbo.tblScholarYears.ScholarYearID-- Return the result of the functionRETURN @ResultVarEND
View 10 Replies View RelatedNeed to INSERT into a different table the function value results in SELECT from a table for PurchorderNum and QtyOrder and not sure how
ALTER proc [dbo].[spCreateContainerFill]
(@containerID as nvarchar(64),
@lotNum as bigint,
@customerID as int = 1164,
[code]....
I'm trying to write a function that I can run with passing the table name as a parameter. I want to return an integer. The tables wll be differnet types (different colums) but they all have a similar field that i want to get a count of. Someting alng the lines of this:
Create FUNCTION [dbo].[fn_GetItemsToPromote]
(
@TableName nvarchar(100)
)
RETURNS int
AS
BEGIN
Declare @SQL nvarchar(500)
return select count(*) from [@TableName]where promote = 1
END
It doesnt like the @TableName. Can anyone show me how to do this correctly?
Thanks!
I have to pass 3parameters in function,
@begindate,@enddate and @group_type..
but in @group_type should be - state,zipcode and country from salestable
inview :vwstzipcont
create view vwstzipcont
as
select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3
left outer join (select distinct stype,docdate from salesdisttable) s2
on s2.stype = s3.stype
where s2.soptype = 2
go
create function mystzipcont
( @begindate datetime, @enddate datetime, @group_type char(70))
RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,
p.index From Pubs P
inner join vwstzipcont vs
on vs.index = p.index
Where (vs.docdate between @begindate and @enddate)
and @group_type ) as t
order by t.itemnmbr,t.docdate
end
how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country
can anyone tell me? what condition should be in where clause for this variable?
thanks
I am trying to select a group of records based on a parameter valuepassed to the db from a web page. The value comes in as @Status andhas a list of statusID's: (1,2,5,9) I've tried to use"Where table.status IN (Select * from @Status AS ValueList)"And also tried"Where table.status IN (@Status)"And neither worked. Any suggestions?
View 2 Replies View Related
I've created a function that converts the rows of a column into a delimited string using a passed cursor and delimiter character. In the past I did this in Oracle and called it as shown in the following example:
SELECT Table1.ID, Table1.FirstName, Table1.LastName, fnDelimitRows(CURSOR(SELECT Table2.CourseName FROM Table2 WHERE Table2.StudentID = Table1.ID), ',') AS AssignedCourses FROM Table1
23 John Smith CS101,MT200,BIO100
43 Julio Johnson CS200,ENG100,MT300
How would I pass a cursor into a function in SS like I did above in Oracle?
Thanks!
Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.
DATEADD(hh, @hours, @startTime)
It works if I have it set such as DATEADD(hh, 8, @startTime), but I need that parameter there for what I need.
I get this error:
Error Source: System.Data
Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.
Any ideas?
I have a report with the following dataset query:
Code Snippet
SELECT ...,
dbo.getActivitySinceBeginningYear(..., @countryId ,...) AS HoursYear
FROM ....
WHERE cast(detail.COUNTRY_ID as nvarchar(max)) IN (@countryId)
AND ...
GROUP BY ...
countryId is defined as a String multi-valued parameter.
The getActivitySinceBeginningYear is defined as follows:
Code Snippet
ALTER FUNCTION dbo.getActivitySinceBeginningYear
(
...
@countryId ntext,
...
)
RETURNS decimal(18,5)
AS
BEGIN
declare @return decimal(18,5)
set @return = 0.0
SELECT @return = SUM(detail.HOURS_WORKED)
FROM ...
inner join charlist_to_table(@countryId, Default) f on detail.COUNTRY_ID = f.str
WHERE ...
RETURN @return
END
This function works as expected if I transmit one ID or several.
The report works fine if I check only one ID.
If I check several IDs, the report displays this error:
Procedure or function dbo.getActivitySinceBeginningYear has too many arguments specified.
Why???
My second problem is with the Where clause:
Code Snippet
WHERE cast(detail.COUNTRY_ID as nvarchar(max)) IN (@countryId)
If I do:
Code SnippetWHERE detail.COUNTRY_ID IN (@countryId)
I have the following error:
Conversion failed when converting the nvarchar value '3,5' to data type int.
(I transmitted 3 and 5 for countryId)
That's why I tried to use a cast, but the data returned by the query is wrong when I transmit several value.
??
I could successfully modify the package level variable using a script component (Control Flow Level) and execute the data flow task after this script component. The OLE DB Command has one parameter for which I'm using one of the user variable. Here's the SQL statement.
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM d_Time_School_Year
WHERE (Year_Key = ?)
This works fine. But I want to pass the year_key to a function which accepts a parameter. The SQL should be like this
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM fn_TimeDimension (?)
But SSIS doesn't like this. When I click on parameters command button I get and error like this
"Parameters cannot be extracted from the SQL command. The provider might not help.........
Syntax error, Permission Violation, or the non-specific error(Microsoft SQL native Client)"
Any clue how to utilize the variables in a SQL which gets data from a function instead of a table?
Thanks
Jemini Joseph
hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance
i have on table temp and it has column name fhID, now that column name and table name comes as parameter in stored procedure from c#.
now stored procedure has code somthing like below
@column nvarchar(50)
@tbname nvarchar(50)
-- both are parameter comes from c#
decalre @maxid
select @maxid=max(@column) from @tbname
rather than prints the maximum values of "fhid" it prints column name itself.
now i how can i achive actual working of max function using parameter.
is there any other way to pass parameter in aggregate function or it is not allowed to pass.???
plz help me,
thanks
vishal parekh
'fhid'
now how can
Hi all.
I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.
Now to my problem:
I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.
Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.
All ideas are welcome. Thanks.
Hello,
I would like to create a function that accepts a value of any type as parameter. I mean, it could receive a value of type int, datetime, char,...
In .net, I would use the type "object". Is there an equivalent for SQL server function ?
Regards,
mathmax
Hi all, I want to use a function with a tabel object as parameter. Doessomeone know a method to do this. I have read that a table as parameteris invalid.
View 4 Replies View RelatedHi Friends,Is it possible to pass a table as a parameter to a funtion.whos function declaration would look some thing like this....ALTER FUNCTION TempFunction (@TempTable TABLE, @nPId INT)my problem is: i have to access a temporary table created in an SP ina functionALTER PROCEDURE MySPBEGIN....DECLARE @TmpTable TABLE(...)....TempFunction(@TmpTable)....ENDThanksArunDhaJ
View 12 Replies View Relatedi want to make a function that will take a table as input parameter. this table will be the output of a stored procedure. while i were writing the function i have an error and when i read about it i found that i can not send a table as input parameter to a function till i create a new TYPE of this table with its columns and data types as UDT but i found that sql server 2005 does not support the type 'table'...
my question now is it possible technically to make this function? is it possible to write something like that :
SELECT dbo.MyFunction(exec dbo.MystoredProc)
and in my function i am using CLR-Integration as this :
create function MyFunction
(
@TempTable table
(
ContractID int,
ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount money,
CurrencyID nchar(3),
DateStart smalldatetime,
DateEnd smalldatetime,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30)
)
)
returns table( ContractNumber nvarchar(20),
Name_En nvarchar(80),
Name_Ar nvarchar(80),
ContractAmount decimal,
Currency_En nvarchar(30),
Currency_Ar nvarchar(30),
[Year] int,
[Month] int,
DomesticAmount decimal
)
as external name [AssemblyName].[PathOfTheFunctionInTheAssembly].[FunctionNameInAssembly]
please help me in this code as i need it urgently...
thanks in advance,
best regards,
Moustafa
How would one pass a subquery to a function as a parameter?
I have a function, f_Split that returns a table and has two parameters @List varchar(max) and @Delim char(1). I'd like to use it to normalize rows in a table and return the results for use in a report. I tried the following:
SELECT * FROM dbo.f_Split((SELECT Code FROM Codes WHERE ID = 10), '|')
I'm getting a syntax error for the subquery. Could anyone show me the proper way to pass a subquery to a function, even if the function is different from what I have defined here.
Thanks,
DD
In the provided CLR-function sample there is only one parameter from the SQL statement to the function. I can't find any info about how to pass more then one parameter. Is this possible somehow?
like:
Code Snippet
Select ShopCode,PickAValue(OrderDate,Article) as BestArticle
from T_Sales
Group by ShopCode
hans
In the snippet below, ExecuteSqlString is a stored procedure that accepts one parameter. SelectChangeDropdownRowsource is a function in my code behind page that generates the string I want to pass. I can't seem to find the correct syntax to get it to work. The way it is show below, the error comes back about incorrect syntax near ')' . Is this doable?
<asp:SqlDataSource ID="ChangeInfo" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"
DataSourceMode="DataReader" ProviderName="<%$ ConnectionStrings:xxx %>"
SelectCommandType=StoredProcedure
SelectCommand="ExecuteSqlString">
<selectparameters>
<asp:parameter name="sqlString" Type=String DefaultValue=SelectChangeDropdownRowsource()/>
</selectparameters>
</asp:SqlDataSource>
I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code:
SELECT CASE WHEN <CONDITION>
THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
ELSE 0
END AS CurrentDateAdj,
Year(CurrentDateAdj) + '_' + 'some text and processing')
FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
I've a scalar function which is killing my performance. I've been used the SQL profiler and also DMVs to catch execution information. I'd like to store the value received by this function and also the time that it happened, but I can't think in a way to do it.
View 5 Replies View RelatedHi
Let€™s say I have employees table that contains id column for the supervisor of the employee.
I need to create a function that gets coma separated string value of the supervisors€™ ids,
And return the ids of employees that the ENTIRE listed supervisors are there supervisor.
(some thing like €śSelect id from employees where supervisor=val_1 and supervisor=val_2 and €¦ and supervisor=val_N)
Is there a way to create this function without using sp_exec?
I€™ve created a function that splits the coma separated value to INT table.
(For use in a function that do something like:
€śSelect id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))
)
Thanks ,
Z
How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function .
my procedure like this:
ALTER PROCEDURE [dbo].[ORNEK10]
@YIL VARCHAR(4),
@TEKLIF_TURU VARCHAR(255)
AS
BEGIN
DECLARE @N1 FLOAT
DECLARE @N2 FLOAT
SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1')
SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2')
SET NOCOUNT ON;
--I want to avoid using if statements for Sum() function
IF(@TEKLIF_TURU='BASKAN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(BASKAN_TEKLIF) AS YILI,
((100+@N1)*SUM(BASKAN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='ENCUMEN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(ENCUMEN_TEKLIF) AS YILI,
((100+@N1)*SUM(ENCUMEN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='MECLIS_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(MECLIS_TEKLIF) AS YILI,
((100+@N1)*SUM(MECLIS_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
END
I have a function which accepts table parmeter. i have created a view and i need to use this function.
How can i do it. how can i pass dim table into the @table variable
select * from dim cross apply fnc_user(@table variable)