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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 28, 2006
can i make a function call from stored procedure
View 3 Replies
View Related
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
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
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
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
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
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
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
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
Sep 12, 2012
How to call a sql function in stored procedure using Sqlserver 2008?
View 4 Replies
View Related
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
Nov 13, 2007
Hi,is there any method to call a store procedure into a function?ThanksFabio
View 1 Replies
View Related