Transact SQL :: Return Recordset From Dynamic Table

Sep 25, 2015

I tried to create a dynamic table, fill in it and return it as recordset. The codes as this:

Declare @tbl Table(id int, name varchar(100), age int) 
Insert Into @tbl(id, name, age)
Values(1, 'James, Lee', 28),
   (2, 'Mike, Richard', 32),
   (3, 'Leon Wong', 29)
Select * From @tbl Order By age

It works well in "SQL Query Ananizer". But return no records in ASP page.

View 5 Replies


ADVERTISEMENT

Transact SQL :: Use Dynamic Table Names And Get Return Value From The Query

Sep 16, 2015

I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.

Things I have learned:

 - SQL Functions cannot use Exec to execute query strings.
 - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.

So how can I write a stored procedure that will
 
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?

Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.

View 14 Replies View Related

Dynamic SQL Return Table Variable

Nov 2, 2007

Is it possible in SQL 2000 to return a table variable from dynamic sql? We need to have some code that looks kind of like this:

declare @qry varchar(8000)
declare @sourcetable varchar(100) -- name of source table
declare @mytable table (ID_Num int identity(1,1), Child_Key varchar(100))

set @sourcetable = (select tablename from tblConfig where app = 1)

set @qry = 'insert into @mytable


select * from @sourcetable'

execute (@qry)





if (select count(*) from @mytable) > 0
begin

'insert into myFinalTable

select * from @mytable where blah blah blah



I can get the first part to work by declaring the table variable (mytable) in the dynamic sql, but I can't figure out how to return the table object.

View 7 Replies View Related

Dynamic Function To Return Number Of Records In Table

Aug 5, 2014

I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.

CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT

[Code] .....

View 1 Replies View Related

Transact SQL :: Return Most Recent Entry On Joined Table?

Sep 29, 2015

Empid 1 has 2 entries for the date 09/01/2015 and my left join returns both of those entries.  What do I need to alter to make it so that only the most recent entry is returned not both entries?

Create Table #one
(
empid varchar(100)
,empbadgeid varchar(100)
,emplunchtime decimal(18,4)
,empbreaktime decimal(18,4)
,empworktime decimal(18,4)

[code]....

View 5 Replies View Related

Transact SQL :: How To Return Content Of Table As Columns In Query

Oct 1, 2015

I have

Customer table, tblCust: ID; Name

1 Peter2 Mary
Product table, tblProduct: ID; Name

1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount

1 ;2 ;$20 – means Peter ordered $20 oranges

How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?

Name Banana Orange Apple 

Peter 0 20 0

View 4 Replies View Related

Transact SQL :: Profile Variable To Return From Queue Table?

Jun 18, 2015

Trying to create a report... Report should show * documents on hold then depending on the "on-hold type" look in the corresponding table and SELECT a few fields. Here is what I have. Where do I SET the @profile variable to return the profile from my queue table?

DECLARe
@profilevarchar(256)
SELECT
q.[profile],q.on_hold,q.on_hold_message,q.dbc_state 
FROM
QueueASq

[code]...

View 5 Replies View Related

Transact SQL :: Dynamic Partition For Partitioning Table

Jul 31, 2015

I am new to Partitioning tables. My scenario is as listed below.

I am getting Monthly Transaction data on Every First Monday of the Month and I want to do partition for those data.

For Example: Let's say I will get my next monthly data on August 3rd 2015 which is First Monday of the month of August.

I want those Transaction data to go in new partitioned FileGroup in my existing partitioned table. How can I do partition for this kind of scenario ? Can we create one or multiple Stored Procedure which will create New Partition and load data in that partition ? 

FYI, this monthly data will be loaded in Staging table and that table has LoadDate column which will have 2015-08-03 in it.  I am using SQL 2012 Enterprise edition.

View 17 Replies View Related

How Can I Use The Last Recordset In A SP That Return 3 Recordsets ?

Mar 9, 2005

Hello,

I have a SP1 that is calling another SP2,
SP2 is using select and returning records

So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)

When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."


How can I access the last recordset ?

View 3 Replies View Related

Transact SQL :: Creating Table In Particular Database When Run Dynamic Script

May 13, 2015

I have a question regarding the dynamic sql. I have a script which which deletes all the foreign keys and re- creates it. So, I have created a table instead of a temp table. Now I need to create that foreign keys table in such a way that in which ever database I run the script, this  foreign keys table should be created in that particular database.

For example:

IF OBJECT_ID('tempdb..keys','U') IS NOT NULL
DROP TABLE keys
CREATE TABLE keys.[@sourceserver],[@database]. (RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200)

View 2 Replies View Related

Return Recordset And A Variable From Procedure

May 28, 2005

Hi

I want a functionality such that I want to return a select query resultset and a varchar variable from a procedure. How can I achieve that,and moreover how can I fetch them in ASP??

Waiting for someone to shed a light of hope.
Thanx a lot

View 1 Replies View Related

Stored Procedure To Return A Recordset

Dec 7, 2005

In a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.

I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.

The Stored Procedure;

CREATE PROCEDURE dbo.sp_eeLinksByName
@EmployerNum char(6)

AS

SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName
WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum)
ORDER BY dbo.TIMS_eeLinksByName.Employee

returns 169 records when I run it directly from the MS Visual Studio environment.


However whe I try to run it from VBA with the following code;

Dim sp_eeLinksByName As String
Dim ConnectionString As String
Const DSeeLinksByName = "SOS-1"
Const DBeeLinksByName = "Insync"
Const DPeeLinksByName = "SQLOLEDB"

Dim objeeLinksByNameConn As New ADODB.Connection
Dim objeeLinksByNameRs As New ADODB.Recordset
Dim objeeLinksByNameComm As New ADODB.Command

ConnectionString = "Provider=" & DPeeLinksByName & _
";Data Source=" & DSeeLinksByName & _
";Initial Catalog=" & DBeeLinksByName & _
";Integrated Security=SSPI;"

' Connect to the data source.
objeeLinksByNameConn.Open ConnectionString

' Set a stored procedure
objeeLinksByNameComm.CommandText = sp_eeLinksByName
objeeLinksByNameComm.CommandType = adCmdStoredProc
Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn

' Execute the stored procedure on
' the active connection object...
' "CurrTSCalendar" is the required input parameter,
' objRs is the resultant output variable.
objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs

' Display the result.
'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
Select Case objeeLinksByNameRs.RecordCount
Case 0
'Do Nothing
Case Is > 0
'Get the Employee List
objeeLinksByNameRs.MoveFirst
Do While Not objeeLinksByNameRs.EOF
MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee"))
objeeLinksByNameRs.MoveNext
Loop
End Select

'Clean up.
'objRs.Close
objeeLinksByNameConn.Close
Set objeeLinksByNameRs = Nothing
Set objeeLinksByNameConn = Nothing
Set objeeLinksByNameComm = Nothing

I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?

View 7 Replies View Related

Can I Return A Range Between Two Integers On A Recordset?

Feb 15, 2008

I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc

Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc

I'm using MSSQL 2005

tks

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

Transact SQL :: Unpivot With Dynamic Columns Or Redesign Relational Table?

Sep 3, 2015

I want to use column name to be join another tables.

I have invoice table to store detail of invoice and post some column ' s record to another table .

Invoice table
Invoice_Name  |   Invoice_Amount |  Invoice_Vat | Invoice_Total
Inv001            |          1000           |       70          |     1070             

Account_table
Account_No |  Account_Number | Data_Source
JV001          |     1111               |    Invoice_Amount  ---->1000
JV001          |     1112               |    Invoice_Vat         ---->    70
JV001          |     1113               |    Invoice_Total       ----> 1070 

I want to join  Invoice table to Account_table

ON  Invoice_Amount , Invoice_Vat , Invoice_Total with Data_Source

The way i got so far I unpivot  Invoice table  column into row and join with Account_table .

The problem is ,  if the column in Invoice_table are created , I must used dynamic columns to do this in sql query.

View 7 Replies View Related

Transact SQL :: How To Insert Dynamic Column Values Of A Table To Variables

Jul 18, 2015

I am trying to insert different number of columns into variables.  This is what it does If I use a static columns.

declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)

#MonthtoDate  temp table is created using a dynamic pivot query. 

Data looks like this :

Account Type  1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13

Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3]  from #MonthtoDate 

However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .

We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .

View 6 Replies View Related

T-SQL (SS2K8) :: Always Return X Number Of Records Even If Less In Recordset

Jun 27, 2015

I am using SQL Server 2008 as a back end for a Microsoft Access front end. I have created a report that is essentially a Bill Of Lading. The detail section lists all the purchase orders that are being shipped on a single load. The problem with the Access Report is that I always need a set number of records (8) so that the layout is consistent. So, if the query returns 5 records, I need an additional 3 blank records returned with the recordset. If there are 2 records, I need an additional 6, and so on. For simplicity sake the query is:

SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber=@BOLNumber;Now, I can get the results I want by using a union query for the "extra" records.

For instance, if there are 6 records returned for BOLNumber '12345', I can get the expected results by this query:

SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber='12345'
UNION ALL SELECT '12345',Null
UNION ALL SELECT '12345',Null;

Another solution would be to create a temporary table with the "extra" records and then have only one Union statement. Not sure which is better, but I'm not really sure how to programmatically do either of these. I'm guessing I need to do it in a stored procedure. How do I programmatically create these extra records? One other note.... If there are more than 8 records, I need to return 8 of these "blank" records and none of the real records (hard to explain the reason behind this, but it has to do with the report being only a summary when there are more than 8 records while the actual records will go on a different supplemental report).

View 8 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Difficult Query: Return Recordset From Concatenated Strings?

Jul 20, 2005

Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray

View 7 Replies View Related

SQL 2012 :: SSIS / Dynamic Updating Of A Recordset Variable?

Jul 8, 2015

updating a recordset contained in an System.Object variable during runtime.

I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.

Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.

My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?

View 5 Replies View Related

Transact SQL :: Limiting Recordset Output

Jun 9, 2015

I’m thinking about the best way to run these queries, which need to be run regularly.

The first query is two tables linked, one is a data table containing a unique person identifier and three activity fields, the second a lookup table with an activity_type linked to the activity in the table data.

Data Table
PersonID
Lots of other fields
Activity1
Activity2
Activity3

The ACTIVITY fields can contain data anywhere between all being NULL and all being complete.

Lookup Table
ActivityID
ActivityDesc ( which appears in Activity 1 – 3)
ActivityType

I’d like to create a function which will create a recordset containing the Person ID and the Activity Type. I am unsure as to whether to do this in a way which will create one record for each person, or potentially 3 records for each person. This is how I have done the 3 records:

SELECT PersonID, Activity1 As Sport, ActivityType
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity1 = dbo.tblLUActivityType.ActivityDesc
UNION
SELECT PersonID, Activity2 As Sport, ActivityType

[Code] ...

And this is how I have done the 1 record:

SELECT ClientID,
Activity1,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity1) As ActivityType1,
Activity2,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity2) As ActivityType2,
Activity3,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity3) As ActivityType3
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity3 = dbo.tblLUActivityType.ActivityDesc
Order by PersonID

The reason I would like to do this is because I need to create a stored procedure which returns one record per person with two fields (Person Id, ActivityType) which states their ActivityType depending on certain rules:

Rule 1: If any of Activity 1 – 3 are ‘O’ report the Person ID and ActivityType = ‘O’

Rule 2:  Of the rest of the recordset, if any of Activity 1 – 3 are ‘N’ but none of Activity 1-3 are ‘O’ or ‘A’ then report the Person ID and ‘N’.

Rule 3:  Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ but none of Activity 1-3 are ‘O’ or ‘N’ then report the Person ID and ‘A’.

Rule 4: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ and one of the is ‘N’ then report the Person ID and ‘AN’.

At the end this I’m looking for one recordset with two fields containing a personID and one of ‘O’, ‘A’, ‘N’ or ‘AN’. I can do the first part of this in any way necessary, as long as the second part returns the recordset I need. 

View 4 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

Return Value - Dynamic SQL

Nov 20, 2006

I need to get a return value from a sproc. I know how to do it this way:

CREATE PROCEDURE A_SPROC (
@RETVAL REAL OUTPUT
)
AS
SET @RETVAL=(SELECT A_VALUE FROM A_TABLE)
RETURN @RETVAL
GO


What if I am using dynamic SQL? How do I set @RETVAL?

View 4 Replies View Related

How To Return Value From Dynamic Query Or Set The Value

Nov 28, 2007

Hii I am Varun  i have a problem with the dynamic stored procedure
This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1
(@TeacherID_OnLeave numeric(9),
@DateFrom datetime ,@DateTo datetime , @UserID numeric(9)
)
AS
declare @flag as numeric(9)
declare @year as varchar(4)
set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0)
if( @year=null or len(@year)=0)
set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+')
begin
insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+');
end')
else
set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave)
return @flag
--exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'
--')
--return @flag
--exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')

View 1 Replies View Related

Using A UDF To Return Values For A Dynamic WHERE IN () Clause

Mar 2, 2006

Greetings,

I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...

The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.

The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()

RETURNS TABLE

AS

BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END

Then I could call this function by doing something such as:

SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())

I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.

Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"

(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()

RETURNS @t1 TABLE (i INT)

AS

BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END

Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.

Any help is very much appreciated.

- Jeff

View 3 Replies View Related

Dynamic Sql Inside Function And Return Value

Apr 10, 2007

Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working...
Thanks
______________________________________________________________________
set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey
from rt_'+@platform+'_CallLegs
where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5))
exec @sql

return @CallLegKey

View 3 Replies View Related

PIVOT To Return Dynamic Rows Horizontally

Jan 13, 2008

Hi,

I have a client with the following table structure:
ItemNumber, Name, Description

containing the following Data (example):






ItemNumber

Name

Value


6473764

SDRAM

4GB


6473764

Xeon

2300 Mhz


6473764

Video

256 MB


6473764

Bus

1300 Mhz


6473759

SDRAM

2GB


6473759

Xeon

2000 Mhz


6473759

Video

128 MB


6473759

Bus

1066 Mhz

I am trying to use PIVOT to convert this into Columns:






Item Number

SDRAM

Xeon

Video

Bus


6473764

4GB

2300 Mhz

256 MB

1300 Mhz


6473759

2 GB

2000 Mhz

128 MB

1066 Mhz

The problem is that I do not know the names of the columns before I run the query. I retrieve them based on a a list of Item Numbers:
SELECT DISTINCT Name FROM TableName WHERE ItemNumber IN (...)

What I was trying to do is the following, but I am not exactly sure what is wrong:

WITH ProductLines (ItemNumber, ItemName, ItemValue)
AS
(
SELECT ItemNumber, Name, Value FROM ProductItems WHERE ItemNumber IN (...)
)

SELECT * FROM ProductLines -- Don't know the column names
PIVOT
(

... -- Not sure what should come here
FOR ItemNumber IN (...)
)

Thanks a lot,

Aric Levin




View 3 Replies View Related

SQL Server 2012 :: Dynamic Return Type In A Function

Mar 3, 2015

I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function

GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]...

The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.

View 7 Replies View Related

How To Put Data From A Recordset Into A Table

Jun 24, 2004

I want to transfer a recordset (derived from an Oracle datasource) into
an SQL2000 Server table using VBScript in a ActiveX Script Task using a DTS.

Currently I use the OPENROWSET (and OPENQUERY) method, however
the lenght of the querytext seems to be limited to 8192 bytes.
At this moment I have reached the limit of this lenght, and I am looking
for a solution.

Futher info: The query returns large recordsets of 100,000s of records with 100s of columns. Because of it's complex structure, the standard data transformation in SLQ2000 is not an option.

Using a substitute like:

varRecords = rst1.GetRows
For intI = 0 To UBound(varRecords, 2)
rst2.AddNew
For intJ = 0 To UBound(varRecords, 1)
rst2(intJ) = varRecords(intJ, intI)
Next
Next
rst2.Update

works fine but is much to slow.

Can anyone help me to find a solution?

View 2 Replies View Related

How Can I Retrieve A Recordset From A Matrix-like Table?

Feb 3, 2004

I have a table defined as (int Row, int Column, money Data); as it were a matrix.

Which is the more efficient way of retrieving a result set with the following form?

Column1 Column2 Column3
---------- ----------- ----------
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...


Thanks a lot in advance.

View 1 Replies View Related

Transact SQL :: Server Getting Return Value

Sep 15, 2015

I wrote the following Scalar Function.

USE [Metadata]
GO
/****** Object: UserDefinedFunction [Event].[BestTBOI] Script Date: 9/15/2015 11:11:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]...

But I cannot figure out how to 'Call' this stored procedure from a query, passing the needed parameters, and use the return value from the stored procedure in one of the query fields.

Cannot find either column "Event" or the user-defined function or aggregate "Event.DynamicBestTBOI", or the name is ambiguous. use Metadata select Event.DynamicBestTBOI(IntegratedTest1.Event.EventTrackUpdate.SequenceNumber,'EventTrackUpdate')
from IntegratedTest1.Event.EventTrackUpdate

View 4 Replies View Related

Transact SQL :: Have 1 Result Set Return

Nov 4, 2015

My desired output is:

abc - 2
def - 2
ghi - 2
jkl - 2

As you can see my query returns all the values from both tables instead of combining them.  This is SQL Server 2008

Create Table #1 (blah varchar(100), cnt int)
Insert Into #1 Values
('abc', 1)
,('def', 1)
,('ghi', 1)
,('jkl', 1)

[Code] ....

View 3 Replies View Related







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