T-SQL (SS2K8) :: How To Get Data Retrieved From Exec Function

Aug 14, 2014

How can I get the data retrieved from the exec function below into Excel

DECLARE @columns NVARCHAR(MAX) ,
@columns_n NVARCHAR(MAX) ,
@sql NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT @columns += N', X.' + QUOTENAME(aaTrxDim)

[code]....

View 5 Replies


ADVERTISEMENT

How To Display Data Retrieved In DataGridView Using SQL With Vb.net?

Feb 27, 2008

hi, could anyone tell me how to display data retrieved using sql in DataGridView?

View 6 Replies View Related

Exec In A Function

Oct 15, 2004

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.

Thanks much

View 1 Replies View Related

Exec A Function

Oct 11, 2007

Hi,

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 )


Best Regards

View 14 Replies View Related

Exec Function

Feb 1, 2008

In table

tbl_query
(id, sql)

field 'sql'
i have stored sql sentence (select * from ....)

and how can i run this sql sentence?

Exec (select sql from tbl_query where id = 1).

thank you

View 5 Replies View Related

Can Sqldatasource Automatically Sort Retrieved Data From Stored Procedure ?

Jan 6, 2006

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 ?
 
Thanks

View 1 Replies View Related

How To Exec SQL User Defined Function?

Aug 4, 2005

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

View 2 Replies View Related

Exec Sproc In A Update Function

Feb 5, 2004

Folks

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.


Thanx

View 3 Replies View Related

Exec In User-defined Function

Jun 1, 2006

Hi,

How can I do dynamical exec to query in user-defined function? At the end I need to return the result.

Thank's
Alexei

View 1 Replies View Related

How To Return The Result Of An EXEC From A Function

Sep 21, 2007

Hi,

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.

Anyone with any ideas?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

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?

View 8 Replies View Related

T-SQL (SS2K8) :: UDF Scalar Value Function

Jun 24, 2015

I build a UDF scalar function like this:

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

The value are:

1695
2211
41
10698
11697
13696

View 9 Replies View Related

T-SQL (SS2K8) :: Date Format - Convert Function

Mar 12, 2014

How to get this out put.

Details:

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

declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert

--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM

What is the correct date format to achieve this.

View 2 Replies View Related

T-SQL (SS2K8) :: POWER Function And FLOAT Limitation?

Apr 2, 2014

I am just wondering why

SELECT POWER(2.718, -34.08)
returns me 0.

Tested it in Microsoft Excel

=2.718^-34.08

and it is returning me 1.58774E-15 which is approximate 15 decimal points.

My table column defined as FLOAT which can take up to 54.

Is it the limitation in the POWER function ?

View 3 Replies View Related

T-SQL (SS2K8) :: Enhanced Find And Replace Function

Apr 17, 2014

I'm looking for a way to pull off a complex find+replace within some code, as follows:

@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'

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

View 2 Replies View Related

T-SQL (SS2K8) :: Display Values Up To 1 Decimal Without Function?

Sep 11, 2014

I am having values as below:

99.87
99.96
8.67

And my output should be as:

99.8
99.9
8.6

How can I do this

View 9 Replies View Related

T-SQL (SS2K8) :: Create A Table Valued Function?

Oct 20, 2014

I would like to create a table valued function using the following data:

create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG

[code].....

I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,

CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Function To Parse Each Command In The Table

Nov 19, 2014

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?

View 3 Replies View Related

T-SQL (SS2K8) :: Proper Use Of CTE In Scalar Valued Function?

Dec 1, 2014

I have troubles with this scalar-valued UDF:

I get the error:

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)

[code].....

View 4 Replies View Related

T-SQL (SS2K8) :: Case Query - Unpivot Function

May 6, 2015

I've this result from my 'case' query;

Jan Feb Mar April
1 2 3 4

I want ;

Month Value
JAN 1
Feb 2
Mar 3
April 4

View 3 Replies View Related

T-SQL (SS2K8) :: How To Pass Array Of Values As Parameter Of Function

Mar 4, 2014

I am trying to find out a way to pass an array of pbaseid's to a function.I have a function defind something like this

Fport(@portfoliobaseid, @reportingcurr, @rowno,@todate)
(
returning a table
)

trying to execute it like this but its giving error related to subquery must return single value

select * from Fport((select pbaseid from dbo.pbaseid),'us',1,'12/31/2013')

I think i need to find a way to pass this pbaseid one by one but i dont know how to do this ...

View 1 Replies View Related

T-SQL (SS2K8) :: Function To List Distinct Pax Name And Ticket Numbers?

Mar 14, 2014

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.

View 4 Replies View Related

T-SQL (SS2K8) :: Split Function On The Basis Of Contact Number

Jan 9, 2015

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

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Split Function That Allows Multiple Character Delimiter

Mar 25, 2015

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?

View 7 Replies View Related

T-SQL (SS2K8) :: Create Function For Dynamically Update Every Year?

May 27, 2015

UPDATE Report

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.

View 4 Replies View Related

T-SQL (SS2K8) :: Searching For Allowed Characters From Table Using Function?

Aug 10, 2015

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.

View 9 Replies View Related

T-SQL (SS2K8) :: Determine Geometric Function From A Series Of Spatial Points

Sep 22, 2014

Any function that determines the geometric function from a series of spatial points (geometric or geographic.)?

If not I am ready to build one ... I found a clean C++ implementation that I can translate to T-SQL.

View 9 Replies View Related

T-SQL (SS2K8) :: User Defined Function That Returns Start And End Date

Oct 15, 2014

Any UDF that accepts a Month and Year and returns Start Date and End Date.

For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.

View 8 Replies View Related

T-SQL (SS2K8) :: Table Valued Function For Active Directory Group Membership

Jun 8, 2012

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

[Code] .....

View 7 Replies View Related

T-SQL (SS2K8) :: Inline Table-valued Function - A Severe Error Occurred

Jan 21, 2015

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.

View 5 Replies View Related

Using Exec &&amp; Code Area Method In Data Tab

Dec 27, 2007





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

View 6 Replies View Related

Invalid Data For 'Numeric' When EXEC Returns Empty Row

Feb 1, 2006

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

View 1 Replies View Related

Can A Value Be Retrieved From An Insert?

Aug 12, 2005

I need to return the "autoID" number that is created at the time of an insert. Is there way of doing that?

Thanks,
Ed

View 4 Replies View Related







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