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?
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".
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'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.
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.
Hi 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
i 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
Ok, 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)
I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?
Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?
I have a table-valued function in mssql 2005 as below: ALTER FUNCTION fn_test{ @test nvarchar(1000)}RETURNS@TEMP TABLE{ test nvarchar(1000)}ASBEGIN INSERT INTO @TEMP SELECT @test RETURNEND Everytime, I passed in chinese character (@test), such as 測驗, the function will return ????. What should I do for the table-valued function, so that the chinese character can be passed in? Please help. Note: I can search and get the chinese characters if I use stored procedures; and the columns in the tables can store chinese chararcters as well. Only table-valued function is not working with the chinese characters. Is it a bug from MSSQL 2005?
I have an SSRS report with several variables, some of which are multi-valued. The main query that feeds the report uses some table value functions. For example, the query looks something like this:
select tv1.a, tv1.b, tv2.x, tv2.y, tbl.m, tbl.n from tableValueFunction1(@MultiValueParam) as tv1 inner join tableValueFunction2(@MultiValueParam) as tv2 on tv1.xxx = tv2.xxx inner join regularTable as tbl on tv1.xxx = tbl.xxx
where tbl.abc in (@MultiValueParam).When I try to run this, I get an error saying that I'm feeding the wrong number of parameters into the table value functions. I've fed multi-valued parameters to stored procedures before and used a splitter function to split out the comma separated values, so I was planning to do the same thing in this scenario. However, this is different. When I look at the query in SQL Profiler, it shows discrete values for the multi value parameter. For example, the same query above looks like this when viewed through the profiler (using 1,2,3 as my multi-value selection):
select tv1.a, tv1.b, tv2.x, tv3.y, tbl.m, tbl.n from tableValueFunction1(1,2,3) as tv1 inner join tableValueFunction2(1,2,3) as tv2 on tv1.xxx = tv2.xxx inner join regularTable as tbl on tv1.xxx = tbl.xxx where tbl.abc in (1,2,3)
In other words, SSRS isn't converting the multi-value parameter to a comma separated string, like it normally does.
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
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?
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
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
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?
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?
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?
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.
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?
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
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 ?
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.
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.
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.
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
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>
