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)
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.
I'm running 2014 enterprise and getting an error on this form of a query...it says the multi part identifier "mns.col3" could not be bound. I'm aware that a cross apply would be more appropriate but i'm just prototyping and probably going to move to a set based approach anyway.The udf returns a table.
select mns.col1,
mns.col2
from table1 mns
left join dbo.udf_udf1(@firstofmonth,@lastofmonth, mns.col3) x
on 1=1
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
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
I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working. at the bottom of the SP is a line of code that calls a function, the function queries a View. When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function. This all works but I can't get the data returned by the function query onto a browser.
I am doing it this was as the parameters in my SP are also used when calling the function, works really well. Is GTotal a return value?
Here is my function:
USE [LOGONs]
GO
/****** Object: UserDefinedFunction [dbo].[FX_AnnaulBudget1] Script Date: 23/09/2015 12:27:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
...and here is my SP:
USE [LOGONs]
GO
/****** Object: StoredProcedure [dbo].[USP_AnnualBudget] Script Date: 23/09/2015 12:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_AnnualBudget]
[Code] .....
In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast and convert values and that does not work. The query that I am trying to use is the following:
DECLARE @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)
Thus can you show me the t-sql 2012 that I can use to accomplish my goal?
In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything
create table #temp
( zoneid bigint
)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(100000000000000000)
insert into #temp values(200000000000000000)
insert into #temp values(200000000000000000)
[code]...
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.
I am simplifying things here. Basically I have:
SELECT ......... (my selection criteria)
where Id in (@Haulers);
@Haulers is supposed to be a comma delimited list. What do I need to do to make it filter correctly?
I have a sp where query is as below.
DECLARE @ServerCIName varchar(5000)
SELECT * FROM dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
How do I query the data?
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 there,
I have this problem..
I created a function in MS SQL and through this fuinction I am also passing the table name... now when I call the table name through the function in a SELECT statement, MS SQL is giving me an error that I have to declare the variabel....
Can someone help me??
Thanks!!
Hi there..
I created the following FUNCTION and I am trying to pass as a parameter a variable name withe the TABLE name.
MS SQL is not accepting it because it is asking me to declare it... can someone help me??
Thankyou
"CREATE FUNCTION getNSR2 (@tablename varchar(30)) RETURNS decimal(9,0)
AS
BEGIN
DECLARE @TB varchar(30)
SET @TB = @tablename
DECLARE @SR decimal(9,0)
SET @SR= (
SELECT MAX(SysRef)+1
FROM @tablename )
RETURN(@SR)
END
"
when I am trying to perform below query,
INSERT INTO EMPLOYEE
SELECT TOP 100 *
FROM EMPLOYEE_LANDING;
I am getting Invalid length parameter passed to the LEFT or SUBSTRING function.
I know passing table/column name as parameter to a stored procedure isnot good practice, but sometimes I need to do that occasionally. Iknow there's a way can do that but forget how. Can someone refresh mymemory?Thanks.Saiyou
View 2 Replies View RelatedHello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.
There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint
There is a WETPAINT table:
ItemNo int,
Color varchar(20)
There is a DRYPAINT table:
ItemNo int,
Color varchar(20)
Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType
So I created a table function called getTable:
CREATE FUNCTION [dbo].[gettable]
(
@ItemType int = 1
)
RETURNS
@thistable TABLE
(
Color varchar(20)
)
AS
BEGIN
if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT
RETURN
END
This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:
SELECT MAIN.ItemNo, a.Color
FROM MAIN
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo
Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.
Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.
PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)
View 15 Replies View RelatedHi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.
TIA,
Hi,
i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.
The syntax is as follows:
select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car
When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'
The database version is SQL Server 2000 SP3.
What am I doing wrong? Is there a workaround for this error?
can i pass the name of the table and the "order by" column name to stored procedure?
i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?
How can a Stored Procedure use a variable table name and column name ?
The statement :-
SELECT @columnname FROM @tablename
gives error "Line 5: Incorrect syntax near '@tablename'."
I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).
If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,
i.e. SELECT @columnname FROM employeetable gives :-
surname
surname
surname
surname
surname
surname
How can I get the procedure to accept a variable table name and column name ?
I thought one way to do it would be to use the 'EXEC' statement :-
SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)
but this gives error : "Incorrect syntax near the keyword 'FROM' "
although the following table works :-
SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)
The problem seems to be mainly with variable column names.
Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)
Any advice would be appreciated.
Kind regards,
Ian.
(ian.mitchell@sds.no)
I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.
Query:
ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE
[code]....
Returns:
I execute this:
select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')
and get:
CerecerezNULLNULLa.BANNER_RANKb.[rank]
Dear All -
iam facing a problem for passing a parameter to update paradox table statment in the OLE DB Command
Briefly an OLE DB Command exist between OLE DB Source and OLE DB destination , all i want it to send the data that flow from the OLE DB Source to the update paradox table statment in OLE DB Command ,
i use the parameter annotation which is "?" to refer to the parameter in the update where clause and it sucess when i use the OLE DB Command to connect to SQL Server DB but when i use it with the Paradox Table an error appear saying that there is more than one data source column with the name "?" The data source column names must be unique.
i dont know what that means is it means that "?" it consider "?" as a column or what or is there another way to send parameter to Update statment for the paradox table differ from the SQL Server Update statment which is the "? "
i hope someone help me as soon as possible because i need it in work sOOon
Thanks,
Maylo
i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.
for example i want to use
select p1 = sp_diff d1,d2 from table1
sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value
Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?
View 1 Replies View RelatedI'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.
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!
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
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