Passing Columns To CLR Function

Jul 31, 2007



Hello,
I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:




Code Snippet
public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z)
{
//Do Something...
x = ....
y=...
z=...
}


I want to call this code from SQL code:






Code Snippet

create table #Temp (float a,float b)
declare @x float
declare @y float
declare @z float
exec dbo.DoSomething(a,b,@x,@y,@z) ---????






Do someone have an idea?

View 1 Replies


ADVERTISEMENT

Passing Parameters To LIKE Function

Jul 22, 2007

 Hi,I want to pass parameter in to LIKE functionIs there anyway to do thatThanks,Janaka  

View 6 Replies View Related

Passing Parameter In Function

Mar 10, 2008

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

View 14 Replies View Related

Passing Table Name As Parameter To Function

May 24, 2008

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.

View 1 Replies View Related

Passing A Variable To Aggregate Function

Aug 29, 2013

I have cursor that loops through a table (the table only contains columnnames of several tables) the cursor has a variable declared @columnname. when i run the following it works fine

select @columnname,0,0,0,0
from temp_prt

it gives me my expected output

mtr_5120,0,0,0,0
mtr_3247,0,0,0,0
mtr_5160,0,0,0,0
etc........

now i want to get the min of each column name like so

select @columnname,min(mtr_5120),0,0,0
from temp_prt ------> this works for min(mtr_5120)
mtr_5120,34.5,0,0,0

now I want to generalize so I try to pass in the variable name and I do the following

select @columnname,min(@columnname),0,0,0
from temp_prt
(the columname (@columnname) exists in the table temp_prt)

but now i get an error
Msg 8114, Level 16, State 5, Line 29

Error converting data type varchar to decimal.how can i pass the colunmame into the min and max functions or is that at all ppossible. I also tried the following:

select @columnname,'min(' + @columnname + ')',0,0,0
from temp_prt

but i get the same error
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to decimal.

View 2 Replies View Related

Passing A Suquery To A Function As A Parameter

Feb 28, 2008



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

View 3 Replies View Related

Transact SQL :: Passing Parameters Through SP To A Function

Sep 23, 2015

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] .....

View 2 Replies View Related

Passing Variable To String Compare In Function

Dec 8, 2007

I have created a function with:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity

RETURN @Output


END

how can I pass the variable @item correctly for the string comparison

WHERE item = @item AND week = @week

to work correctly please?

WHERE item = '@item' AND week = @week

won't work and

WHERE item = @item AND week = @week

won't work.

View 2 Replies View Related

Passing Variable To Table Function In Join

Dec 26, 2007

Hello, 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.

View 2 Replies View Related

Passing A Specific Cursor Record To A Function

Jul 20, 2005

Hello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS

View 5 Replies View Related

Transact SQL :: Passing Table Parameter To A Function

Nov 3, 2015

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)

View 4 Replies View Related

Passing MS SQL2005 Query Result Into Javascript Function

Mar 20, 2007

I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function.
This is how I retrieve the longitude:
 <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>"
SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)">
</asp:SqlDataSource>
I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this:
var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); 
 However, lat & long do not contain the retrieved result but rather a useless System.something string.
How do I assign the retrieved results to these variables and port them over to Javascript as required?
Many thanks!

View 14 Replies View Related

Passing Table Variable To Stored Proc / Function

Nov 6, 2002

Hi 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,

View 3 Replies View Related

Problem Passing A Variable Into A Table-valued Function

Sep 26, 2007

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?

View 10 Replies View Related

SQL Server 2012 :: Passing Parameters To Table Valued Function?

Mar 13, 2014

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]

View 7 Replies View Related

Passing Parameter Query From SQL Function To Access Project Report

May 20, 2006

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.

View 1 Replies View Related

Transact SQL :: Passing Column To Table Valued Function And Getting Could Not Be Bound

Nov 10, 2015

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

View 3 Replies View Related

String Or Binary Data Would Be Truncated // Is There A Maximum Length For Passing An Nvarchar Max To A Function?

Mar 18, 2008

I'm running into this error message when passing in a few records in particular to a function, the only difference I could find is that these recods have about 60k characters on the field that I'm passing to a function.

is there a max lenght for passing to a function?

select function ( field) as results

It's been working fine until today and all of the related fields are declared as nvarchar(max)

Thank you.

View 5 Replies View Related

SQL Server 2014 :: Passing Multiple Columns Values From Query To One Variable?

Aug 10, 2014

Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)

[code].....

View 1 Replies View Related

Text To Columns Function?

Sep 11, 2013

I have the following Table:

ORDER_ID SKUS

12345678 SCINC, SCNRQ, SRPPR

All the values in the 'SKUs' column are combined together, seperated by a comma.

Is there an easy to way "de-pivot" this so that the Order ID gets repeated as follows:

ORDER_ID SKU

12345678 SCINC
12345678 SCNRQ
12345678 SRPPR

Here's some code:

SELECT ORDER_ID, SKU FROM ORDERS

View 4 Replies View Related

Scalar Function Columns

Jul 20, 2005

Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler

View 4 Replies View Related

Need Help With MAX Function Using Two Tables With Common Columns

Apr 9, 2006

I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan

View 4 Replies View Related

COUNT FUNCTION ON MULTIPLE COLUMNS

Nov 14, 2006

I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.

View 1 Replies View Related

Function That Returns Highest Of Two Columns?

Jul 20, 2005

Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???

View 2 Replies View Related

SQL Server 2008 :: Using Decimal Columns In SUM Function

Jul 21, 2015

Recently I have come across a requirement where i need to design a table.

There are some columns in table like below with DECIMAL Datatype:

BldgLength

BldgHeight

BldgWeight

Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.

Now as per MSDN,

Precision => 1 - 9
Storage bytes => 5

so i can create column as:

BldgLengthDECIMAL(6,2) DEFAULT 0

OR

BldgLengthDECIMAL(9,2) DEFAULT 0

Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.

So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.

So my question is what should be value of DataType for above column ?

View 8 Replies View Related

Transact SQL :: Subtract Two Columns With Windows Function

Dec 5, 2015

How can i subtract two columns:

I have table:
       
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2

But to continue for each row and value is reduced by the previously value.

My output should be like as:

ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100

View 3 Replies View Related

Using COUNT Function On Multiple Columns With GROUP BY Restrictions

Mar 10, 2014

Consider the following dataset:

COL1 | COL2 | COL3 | COL4
1 | FD | DR. A | Y
2 | FD | DR. A | Y
3 | FD | DR. A | N
4 | FD | DR. A | Y
5 | FD | DR. A | Y
6 | PF | DR. A | Y
7 | FD | DR. B | Y
8 | PF | DR. B | N

Consider the script below:

SELECT
COL2, COL3, COUNT(COL1) AS TOTALS
FROM CASES
GROUP BY COL2, COL3
ORDER BY COL3, COL2

The script above produces the following output:

COL2 | COL3 | TOTALS
FD | DR. A | 5
PF | DR. A | 1
FD | DR. B | 1
PF | DR. B | 1

I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:

COL2 | COL3 | TOTALS | NEWCOL
FD | DR. A | 5 | 4
PF | DR. A | 1 | 1
FD | DR. B | 1 | 1
PF | DR. B | 1 | 0

I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.

View 2 Replies View Related

Aggregate Function To Concatenate Columns Data Into A Single Row

Aug 9, 2007

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid

Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22

Any advise would be much appreciated.

View 6 Replies View Related

SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record

Aug 25, 2006

Hello,

I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.

Any help you can provide will be greatly appreciated!

Thank you,
Nathan

View 17 Replies View Related

Transact SQL :: Function To Return Comma Delimited Values From Table Columns

May 12, 2015

I've to write a function to return a comma delimited values from a table columns

If a table has Tab1 ( Col1,Col2,Col3).

E.g. as below ( the columnName content I want to use as columns for my pivot table

CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;

I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....

View 13 Replies View Related

How To Keep The Toggle Columns Function When I Render A Tabular Report To An Excel Format?

Apr 15, 2008



Hi all,

Currently, I am using the SSRS 2005 and try to toggle the report columns similar to Excel's column grouping function. Users can expand "+" / collapse "-" the columns in the render excel report. It works fine when I excute the tabular report in the web yet the toggle function is missing when I render the report to an Excel report. I am not sure the toggle function will only work for the row items or both as the excel report can keep the toggle function for the row items. Please help.

View 2 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View 1 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved