SQL Server 2012 :: Call A Split Parameter Function

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


ADVERTISEMENT

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 View Related

Function Call With Table As Parameter

Jul 23, 2005

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 Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

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 Related

SQL Server 2012 :: Table Returning Function With Input Table Name As Parameter

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

Split Function - Sql Server 2005

May 22, 2008

In my table, column1 having a comma separated values.I want to display in rowwise.
for example:
column1
aa,ss,ff
 
output should be
aa
ss
ff
 
Pls. help me..I want to do thr query.

View 22 Replies View Related

SQL Server Call C++ Function?

Dec 11, 2001

Hi,

Does anyone know if/how SQL server can call a function in a C++ library?

Cheers,

Xiaobing

View 4 Replies View Related

Function Call To Progress Linked Server

Nov 23, 2006

I have a Progress DB set up as a linked server.

To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.

I've tried

SELECT

{fn PRO_ELEMENT(fldarr1,1,1)} as fld1

from ls1..pub.tab



This just returns an unknown function message which I believe is on the SQL Server end of the call.

This statement works fine through Business Objects.



Any help greatfully received.

View 2 Replies View Related

SQL Server 2005, SQL Server Mobile, SQL Server Management Studio. Unsupported HTTP Function Call

Sep 5, 2005

Hi All,

View 14 Replies View Related

SQL Server 2014 :: Cross-database Function Call

Jul 2, 2015

I have 2 databases on the same server

One has a function, the other has the tables and views

using dba

select dbo.function(t.column) as x from dbb.dbo.table as t

gives m an invalid object name of dbo.table

using dba

select top 1 * from dbb.dbo.table works fine.

also if i create the function on the same db it works.

View 4 Replies View Related

SQL Server 2012 :: Call SP On The Basis Of Input?

Feb 15, 2015

I have create Store procedure for all the Source and destination table for loading new and updated record.

For example I have below Store procedure

1-SP_Archive_using_merge_Fdoor
2-SP_Archive_using_merge_Fdoop

now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .

below is the 2 sp .

--First SP
ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int

[code].....

View 1 Replies View Related

SQL Server 2012 :: Need To Split Numeric And Alphabetical Values From String

Jan 10, 2014

I need to split NUMERIC & ALPHABETICAL values from string.

for eg :-

1234heaven56-guy

output

123456 heaven-guy

View 3 Replies View Related

SQL Server 2012 :: Split String Into Columns Based On Special Character

Dec 4, 2013

How to get the required result in SQL 2012

Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

My Expected Output

Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS

View 1 Replies View Related

SQL Server 2012 :: Normalize Values From Column Split Using Tally Table?

Apr 23, 2014

I have a table that has the following structure:

EntryID int,
Categories varchar(200)

values look like:

541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'

what I want to do is extract the Categories column to a normalized separate table:

541,'A'
541,'B'
541,'C'
345,'B' ....

I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?

View 2 Replies View Related

SQL Server 2012 :: Split Total Days And Amount Between Start And End Month

Mar 21, 2015

I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required

For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.

Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.

As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month

Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85

I have uploaded a screenshot of how the result on this link: [URL] ....

Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money

[Code].....

View 4 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

Sep 23, 2014

I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.

Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:

I need my query to output in the following format:

How to write this query?

View 9 Replies View Related

Split Function

Apr 7, 2008

Hi

I have tried like this. but I cant execute my SP it ended up with errors.

IF (@FirstLetters IS NOT NULL)
BEGIN
SET @FirstLetter = 'SELECT SplitValue FROM dbo.FnSplitString('+@FirstLetters+','',''))'
print @FirstLetter
END

SELECT
P.PUB_ID AS ''PubId'',
P.PUB_TITLE AS ''PubTitle''

FROM HDS_PUBLICATION P
INNER JOIN HN_IM_JOIN IM
ON IM.PUB_UNID = P.PUB_UNID,
HDS_CUSTOM C,
WD_PUBLIC_SHELF S,
HDS_TOPIC T
WHERE P.PUB_UNID = C.PUB_UNID AND C.CUSTOM3 = ''False''
AND P.PUB_ID = S.PUB_ID
AND P.PUB_UNID = T.PUB_UNID
AND S.Audience = ''Public''
' + @FilterByLang + '
ANDP.PUB_TITLE LIKE '+ @FirstLetter +'''%''' +'

EXEC SP

Any one please clear me

Thanks,
shakthiA

View 3 Replies View Related

Split Function

Aug 21, 2006



Is there any split function in sql server 2005. I just want to pass a string to a stored procedure. it should split that string when it encounters a space. for ex, consider the string " the intel mother board" it should give "the,intel,mother,board" as result.



View 6 Replies View Related

Split Function In A Transaction

Sep 20, 2000

I have some sql results coming out as a string when it is a bunch of numbers separated with a coma. (1,2,3). I need to insert this figures as separate numbers in separate rows into a table in a database. How do I split them and how do I input them. I tried using a loop and it works well in an asp page but I need to do that in sql.

JG

View 1 Replies View Related

Split Function With Cursor

Apr 7, 2008

Hi,
I am trying to write a stored procedure that takes a comma separated letter. I have a split function that returns the splitted letters. I have select some values from the tables in the database where the title starts with each splitted letter. I thought I should use cursor that contains each letter and in the while loop i put my select statement with the conditions.
Is this the correct way. Or are there any ideas for this?

thanks,

Regards,
shakthi

View 2 Replies View Related

Function Call

Mar 28, 2006

can i make a function call from stored procedure

View 3 Replies View Related

Call A Function

Feb 26, 2008

Does anybody knows how to call a function from one VB source file to another VB source file??

I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??

View 1 Replies View Related

Need Help Understanding A Call To A Sql Function

Feb 25, 2008

Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.
 
1  ALTER              PROCEDURE [dbo].[GetPortalSettings]2  (3     @PartyId    uniqueidentifier,45  AS6  SET NOCOUNT ON7  CREATE TABLE #Groups8  (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)

View 4 Replies View Related

Call Custom SQL Function In ASP.NET

May 9, 2006

I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function

SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length

View 1 Replies View Related

Overload Function Call

Aug 1, 2006

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions



thanks,

View 11 Replies View Related

Call Function For Inner Join

Aug 10, 2007



I have a procedure which has query
like Query 1.

Query 1

Select Clinetid
from clinet
inner join {


select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c

on c.Centerid = client.Centerid


Query 2

declare @Center table ( centerid int)
insert into @Center

select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)

Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid





I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()

View 1 Replies View Related

How To Change The Value Of The Parameter When Call

Sep 24, 2007

Hi
I have below sql procedure
CREATE PROCEDURE Qty
@make as nvarchar(32)

AS
select department as category from tblproducts where department = @make
GO

Parameter (@make) will get the value from asp.net page

My Question: Is any possibilities to change the value of parameter when the procedure trigger. Examlpe like below

If @make = 'Sealed Lead Acid' then I need to pass parameter to the database as 'SLA'
elseIf @make = 'Photographic' then 'Multimedia'
elseif @make = 'cctv and accessories' then 'security'

and so on. I only have six categories. Please help

Advance Thanks

View 3 Replies View Related

Call Function From Stored Procedure

Jan 19, 2007

Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible.  So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures.  When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno.  I want to put that current batchno into the Update statement and update the record with the current batchno.  Can someone point me in the right direction?  Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View 2 Replies View Related

Trying To Call A Function On A Weekly Timer

Sep 17, 2007

I'm not sure this is the place for this question, but not sure where else to go.  I've written asp.net  code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server? 

View 6 Replies View Related

How To Call A Function In Stored Procedure

Sep 12, 2012

How to call a sql function in stored procedure using Sqlserver 2008?

View 4 Replies View Related

How To Call A Userdefined Function Within A Stored

Aug 27, 2007

Hello All,
How do i call a user defined function from within a stored procedure,
I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string.
That part works.


declare @fullname varchar(400)
@fullName=getFullName(@firstname,@lastname)


As always thanks for all your input

View 4 Replies View Related

Call Store Procedure From Function

Nov 13, 2007

Hi,is there any method to call a store procedure into a function?ThanksFabio

View 1 Replies View Related







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