I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
How to exec the following function from my select statement and get my return value:-
ALTER FUNCTION [dbo].[fn_qty] (@qty decimal (10,4), @price decimal (10,4), @pieces int, @mpt int=0)
returns decimal (10,4)
AS begin declare @totamt decimal (10,4) if @pieces = 0 begin SET @totamt = (@QTY * @price) end else begin SELECT @mpt = (SELECT case mpq when 1 then 10 when 2 then 100 else 100 end FROM ims.parm) SET @totamt=(((FLOOR(@QTY)*@PRICE))+(((@qty-FLOOR(@QTY))* @mpt)/@pieces)*@price) end return @totamt end
I can exec the function as exec command as follows:- exec @totamt( erpinv.dbo.fn_qty 3.5,6,24 )
Hi, This is my problem : I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2. I wrote one stored procedure to retrieve data from my sql database like : select Id, Name,Address,Amount from KalakaDB
Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order how can i connect my sqldatacontrol to the stored procedure ?
Hi,How to exec a SQL user defined function in query analyzer when it accepts parameters.. I know for a stored procedure we can write EXEC nameofstored procedure abc (@abc is the parameter passed).. But How to run a SQL function ?Thanks
Here is a query which updates certain values. GetAddress is another sproc which returns addrId. I have to pass certain values ie strAddress1 strCity .....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in not a recognized function name. Is the syntax correct to exec sproc GetAddress.
update Persons set Persons.strLastName=H.strLastName, Persons.strNameSuffix=H.strNameSuffix, Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4), Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4) from ALSHeadr H where Persons.lngSSN=H.lngFedTaxID
FYI I can post GetAddress sproc but it is working properl. I just want to know how to pass the values in ALSHeadr table into the sproc.
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue] ( -- Add the parameters for the function here @TypeID int, @CaseID int ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1 BEGIN SET @RTN = 'SELECT PickItem I + CASE D.IsStartDateEstimated WHEN 0 THEN CAST(StartDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar) END + CASE D.IsEndDateEstimated WHEN 0 THEN CAST(EndDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar) END
FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND P.CaseID = ' + CAST(@CaseID as varchar) END ELSE BEGIN SET @RTN= 'SELECT I.PickItem FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND CaseID = ' + CAST(@CaseID as varchar) END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
CREATE FUNCTION VerificaAcessoPerfil ( @codigo INT ) RETURNS INT
[Code] ....
Curiously when i call my function the same one return always the same value, ex:
Select VerificaAcessoPerfil(2)
the return value is : 698 ??
but if i run the Select statment like this:
SELECT DISTINCT codigo, (case codigo WHEN 1 THEN 695 WHEN 11 THEN 697 WHEN 2 THEN 211 WHEN 10 THEN 698 WHEN 13 THEN 696 WHEN 4 THEN 1 END)[codigo] FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp WHERE codigo IN (1,11,2,10,13,4) ORDER BY 1 ASC
declare @deadline Datetime = '2014-03-23 15:30:10.000' SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert ----With this I am able to produce that like ----o/p: Mar 23 2014 3:30PM
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).
I am debugging a DB maintance script which creates a table of index maintainance commands which are created separately for each index according to the level of fragmentation and other factors.
For the debugging process, I'm looking for a way to parse each command in the table without actually running them to locate any syntax errors. In other words, as if you clicked the blue check on each one.
Does such a function exist in SQL 2008 (the version I'm doing this on) or other versions?
Select statements included within a function cannot return data to a client.
Is this a proper way to include a CTE in a function?
USE [DB1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef HQ 123 1 Jim 4444 ABCDE HQ 123 2 Bob 5555 ABCDE HQ 123 3 Jim 6666 ABCDE HQ 123 4 Bob 7777 ABCDE HQ 124 1 Jenny 8888 FGHIJ HQ 124 2 Jenny 9999 FGHIJ HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE Jim 4444/ 6666 Bob 5555 7777 --BookingRef FGHIJ Jenny 8888/ 9999/ 3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table tbl_1 (txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive, finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596 jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
I have a table with a varchar column that is delimited by char(227)+char(228) i.e.,
OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE
Looking for split function that accepts a multiple character value as a delimiter?
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012' when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013' when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014' when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015' when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016' when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017' End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
I want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.
For example -
'Bill123?', 'Jones12.z-' 'John&12/', 'QWERT123&4'
Wanted results – the single quotes are there to show the space for the replaced characters.
'Bill123 ' 'Jones12.z ' 'John&12 ' 'QWERT123 4'
Example SQL data
CREATE TABLE [Common].[AllowedCharacters] ( [Character] [varchar](1) NOT NULL, [Replacement] [varchar](10) NULL, [AlwaysInclude] [bit] NOT NULL) GO SET ANSI_PADDING OFF
[code]....
The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.
For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?
The following works fine:
SELECT DISTINCT sAMAccountName FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC'' WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a WHERE sn IS NOT NULL
The following gives me the error:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function. CREATE FUNCTION [dbo].queryADGroupMembers ( @group nvarchar(255) ) RETURNS @rtnTable TABLE
I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)
The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):
Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:
CREATE TYPE test_list AS TABLE(a int); GO CREATE FUNCTION test_function (@p int, @theTable test_list READONLY) RETURNS TABLE AS RETURN ( WITH cte AS (SELECT a FROM @theTable) SELECT cte.a FROM cte); GO DECLARE @t test_list; INSERT @t VALUES(1); SELECT * FROM test_function(1, @t);
When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)
The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:
- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)
- The SELECT statement has a CTE
- The TVP is accessed within the CTE
- The outer FROM clause references the CTE
- There is also a scalar parameter on the function (scalar type does not seem to matter).
- The scalar parameter precedes the TVP in the parameter list.
So I have an easy work-around: put the TVP first in the parameter list.
Hi, i'm new to reporting services, rather new to heavy processing in reporting services. i have scenario for which i need your help. so here it goes,
i have a method in Code area of the report, i'm passing parameter values to it. the method willl return me a swl query in string format. i need to execute it in data tab area. the codei have used in data tab is as below. please let me know. wat to do to make it right. thx
EXEC ('Code.Main(Parameters!Param1)' + UNION + 'Code.Main(Parameters!Param2) ' + UNION ALL + 'Code.Main(Parameters!Param3)')
Thx again
i need to get this report done really soon, so please, if u have any idea let me know ASAP
Hi, whenever the underlying query being called by EXEC in the followinghas an empty result set I get the following error -- Invalid Data for'Numeric' when EXEC returns empty row. However if I call the querywithout using REPLACE (which I'm forced to do, because openquery doesnot allow variables), I get just an empty result set. Whenever theunderlying query returns a non-empty result set, the code works withouterror (regardless of wether there are nulls in the numeric column).set @switch ='5707550'set @start_date = '01-JAN-2006'set @end_date = '27-JAN-2006'set @month = 1set @year = 2006set @sql_str='SELECT * FROM(select MSC_KEY,to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)), 5)as "PER_CPU_UTIL"FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view vWHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +@switch + ')and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @start_date + '00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') andto_date(''' + @end_date + ' 23:59:00'', ''DD-MON-YYYYHH24:MI:SS'')group by MSC_KEY, trunc(tstamp))WHERE rownum < 10000'SET @sql_str = N'select * from OPENQUERY(VISION, ''' +REPLACE(@sql_str, '''', '''''') + ''')'EXEC (@sql_str);Is there anyway to prevent this error?Thanks,Crazy