Array / Table As Return Type

Aug 16, 2006

Hello All,

I have a scenario in which my stored procedure has to return few
variables with their value and also the collection. Now in SQL their is
no such as array, so the best is to return the table in place of.

I am execting the stored procedure by having sql command in place.
and created the various parameters(variables) those i need the values
of and secondly wondering how should i be creating the parameter as a
table returntype.

Any help on this would be a million worth useful.

I can excerpt the code if required.

Regards
Sandesh Kadam

View 1 Replies


ADVERTISEMENT

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

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table

Nov 1, 2007

I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
 
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' 
 
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return.  Same goes for the last_name field.   Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..

View 7 Replies View Related

Type Mismatch Rs In Array

May 6, 2006

I have a problem using 3 tables. The first recordset is my own invention (that means a very simple one). It reads product features from a table:

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

'then I read the recordset into arrays like this:

While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
' every id has a corresponding featurename
mfnavn(mte(i))=Rsx("featurename")
Rsx.movenext
Wend

'this part works very well

Now comes the difficult part that the SQLTeam figured out (this combines an order base with an itemorder base):

Set Rs = Server.CreateObject("ADODB.RecordSet")
sSQL="select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features"
Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

'Then I write the content of the products with product features - the complexity of the sql is due to a use of many other operations:

While Not Rs.eof
response.write Rs("catalogid") & "features:" & Rs("features") & "Name:" & mfnavn(rs("features"))
Rs.movenext
Wend

The query runs, and starts well, writing 3 lines:

239 features:221 Name:Standard bil max 5m lang og 1,9 m høy
240 features:270 Name:Liggestol (gratis)
240 features:271 Name:C1 Enkeltseng i delt lugar u. bildekk. Kun vask


but it is then aborted with this message:

Microsoft VBScript runtime error '800a000d' Type mismatch: 'Rs(...)'
I know that the problematic part is the array
mfnavn(rs("features"))
...anyone with experience in this?

View 6 Replies View Related

Proc Argument Type Array

Jul 20, 2005

I have ...CREATE PROC InvoiceDeleteExample2(@InvoiceList1 VARCHAR(255),@InvoiceList2 VARCHAR(255) = '',...@InvoiceListN VARCHAR(255) = '')AS....GOI would like....CREATE PROC InvoiceDeleteExample2(SELECT * FROM table;)AS....GOI hope help... thx

View 1 Replies View Related

What Type Of Field Should I Use To Store An Array Or Datatable?

Feb 25, 2005

Hi - I'm using Session variables to store information (sort of webshop).

The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.

I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?

I'm using SQL Server 2000.

Thanks for any help,

Mark

View 3 Replies View Related

Array Data Type In SQL Server 2005

Aug 9, 2006

Hi,

I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can´t find how to fix it. In my stored procedure in PL/SQL exists this lines:

TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();

Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.

Thank you

David



View 1 Replies View Related

Parse Array Data Type To Rows

Jun 7, 2007

Hi,



We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.



Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.



Any Ideas?



Thanks



Emilio

View 6 Replies View Related

Array Data Type In SQL Server Stored Procedures?

Aug 17, 2000

Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem?
Thanks,
Dan Collins

View 6 Replies View Related

Another Noob Question... What Field Type Is Best For Storeing An Array?

Feb 1, 2008

Is there a special field type I should be using, instead of a NVARCHAR for storing comma separated words?

I could, construct an array from the field value... but not sure if this would be the "right" way to do it?

View 4 Replies View Related

Dose Sqlserver 2000 Support Array Type?

Mar 31, 2006

Any one know about it?

View 9 Replies View Related

Using An Optimised Method To Insert A Variant Type Array To DB

Feb 13, 2008

Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!

View 1 Replies View Related

Help With Error 'value Of Type Byte Canot Be Converted To 1 Dimensional Array Of Byte'

Mar 26, 2008

hi
i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code.
can anyone help me correct the error and let me know ow to solve it
thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString")
myConnection.Open()
 Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id")
cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
dr.Read()
context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error
End Sub

View 1 Replies View Related

RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.

Jan 25, 2007

All,

I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.

"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."

Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.

Machelle

View 10 Replies View Related

'UNION' Return Type

Oct 31, 2007

Hi,i need an opinion on this,...i am using UNION ALL quite a bit in my queries..most of the time these are like 1 line select query,returning "different" type of stuff ..like say query 1 is returning one EmpName  and query 2 is DOB and so on(keeping it simple for example)...i am combining it b/c i find it cumbersome  to call 10 dfferent query to populate  1 structure(table etc) ,rather i wud call one and manipulate the result to fill up that one structure..Is that okay to combine different kind of return types in one (here first row of multi-select query result is empName,second row is DOB and so on...)?????thanks

View 5 Replies View Related

Return Value Data Type In Asp.net

Feb 24, 2005

I am calling a stored procedure and have an output parameter that I pass back to my asp page. The datatype of the value is nvarchar. When I run the page I get an error "Syntax error converting the nvarchar value 'OK' to a column of data type int". Can a return parameter be anything else other than an integer? Below is my code. Thanks for the help.

Parem = cmd.parameters.add("@Retval",SqlDbType.nvarchar,50)
Parem.Direction = ParameterDirection.Output

View 3 Replies View Related

Return Type Of COALESCE?

Jul 23, 2005

Hi everybody,VARCHAR has a higher precedence than CHAR. If you have a querySELECT COALESCE(c1,c2) FROM T1where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the returntype to be varchar(10) similiar to UNION. However it turns out tobe CHAR(10). Does anyone know why that is so?Thanks,Steffen

View 3 Replies View Related

Trick With Return Type

Jul 2, 2007

Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.

I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)

Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)

The problem is that the function should return Integer and if the value is zero I need to return empty string.

It does't say any thing about returning the string but when it trying to use the format on a string it give me a worning and print #ERORR in the cell.

is there a solution for this?
Do you know the syntax to format the number in the function before the return?
(Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).

Thanks a lot!

View 3 Replies View Related

How Would I Send A String Array As A Integer Array?

Jun 25, 2007

I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200) 
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
 Thanks

View 5 Replies View Related

Return Datetime Type Variable From SP

Feb 13, 2007

How can I return a datetime type variable from a stored procedure in SQL Server to C# code?

View 4 Replies View Related

Return XML To Response.write From SQL XML Type

Jul 23, 2007

Hi,
I am trying to retrive some XML from my SQL server 2005 database. The XML is in a table called "myxml" and it is being stored a native XML. The Field type is "XML". The VB code returns nothing when I select SELECT xml. How ever if i SELECT NAME it displays the correct name from the name field. how do I return the XML to be viewed as XML throught the response.write?
 Thanks!
Here is my Table definition:




Column Name
Data Type
Allow Nulls

Id
Int
 

xml
xml
Yes

NAME
Nvarchar(50)
yes
My XML that is stored in the XML field:
<ROOT>
<CHAPTER>
<TITLE>This is a test</TITLE>
</CHAPTER>
</ROOT> 
Here is my VB code:
   Dim myconnection As SqlConnection
Dim mycommand As SqlCommand
myconnection = New SqlConnection()
myconnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString

Dim strSQL As String = "SELECT [xml] FROM myxml WHERE id = 76"

' create SQL command instance
mycommand = New SqlCommand(strSQL, myconnection)

Try
' open database connection
myconnection.Open()
'execute T-SQL command
Dim dr = mycommand.ExecuteReader()
While dr.read()
Response.Write("<p>" & dr(0).ToString() & "</p>")
End While
Response.Write("GOT IT")

Catch ex As Exception
Response.Write(ex.Message)
Response.Write(strSQL)
Response.End()


End Try
' Close connection
myconnection.Close()
 
 

View 1 Replies View Related

Getting Return Type From Stored Procedure

Sep 7, 2006

Hi all,

I am trying to figure out a way to analyze a stored procedure to deduce its output type.

A stored procedure can return values throught one of the following:

1) Using the Return keyword

2) Returning a recordset

3) Throught its Output parameters

4) A combination of 1, 2 or 3

Using the Information_Schema, you can access the SQL code of the stored procedure from, say, VB.Net. From there, I want to know what output type the stored procedure has.

A) for 1), I guess I can parse the code for the 'Return' keyword, but that wouldn't be efficient, for the 'Return' word could be inserted into string values...

B) for 2), it would be difficult to analyze the stored procedure's code to know FOR SURE whether a recordset is returned. You just can't parse for SELECT keywords in complex code...

C) 3) should be easy enough, getting the return parameters types from the Information_Schema.



So, is there an easy way that I missed for achieving this, some object in the .Net Framework that I could use to analyze SQL queries perhaps, or am I doomed to do it the hard way?

Thanks,

-Etienne

View 8 Replies View Related

Creating Sql-clr Function With Void Return Type

Mar 18, 2008



hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script


CREATE FUNCTION dbo.Amex ()

RETURNS NULL

AS CALLER

WITH EXECUTE

AS

EXTERNAL

NAME [AMEX].[UserDefinedFunctions].[Function1];

GO


but i am getting error.
there is something wrong in my sql syntax..can anybody help me?



when creating funtion having signature


public static void Function1()


i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1

can anybody help me?

thanks in advance

Chetan S. Raut

View 4 Replies View Related

Using TEXT Data Type With Carriage Return

Jul 29, 2007

Hi,

I'm using quite odd combination of technology for my project, I'm using PHP and MSSQL 2000, at one certain page, I want to insert to a table where one of the column is TEXT data type, and I want to get the value from the TEXTAREA at the page, of course, with carriage return captured, I manage to get it done in MySQL, where it automatically store the carriage return keyed in by user at the TEXTAREA, while for MSSQL I no luck in finding solution for this, is there any settings I can set or I need to convert the carriage return keystroke to HTML tag at my PHP?

Thanks

View 1 Replies View Related

Convert Table Data Into Array Or Maybe Something Else?!?

Apr 27, 2008

I am using vs2008 with c#.I have table in my database that has email addresses stored in a column.  I would like somehow to take each email address from every row and paste them into a textbox that can be copied and pasted with a comma delimiter into outlook so I can send a mass email.I have no idea how I can accomplish this task. 

View 3 Replies View Related

Inserting And Updating And Array Into An SQL Table

Dec 23, 2003

I have an asp.net project that displays a timesheet based on a fortnightly system.

It has a Y and X axis, i.e. Sun-Mon, Sun-Mon across the top and Categories of hour types accross the Y axis, i.e. Holidays, Overtime, Maintenance.

I was using a datagrid to gather the rows, but I need to have the whole grid in edit mode which is something that requires more coding. So I have swapped to a datalist.

It will take me 240 fields to store all the data in their corresponding fields. So I basically need to know if there is an easier way to store two dimensional array type data into an SQL table using a minimum of fields.

View 1 Replies View Related

How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type

Dec 14, 2007

I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?

View 1 Replies View Related

ADO Command Call Stored Return Type Name Is Invalid

May 19, 2004

Hi all,
I have a stored like this

CREATE PROCEDURE fts_insert_service_tasks( @status_no int output, @status_text nvarchar(255) output, @fts_employee char(100) , @fts_SCCode bigint, @fts_TaskDescription ntext) AS

declare @str_err nvarchar(255)
declare @err_no int

set @err_no=0

if ( isnumeric(@fts_SCCode) = 0 )
begin
set @str_err ='The fts Sccode is not a number'
set @status_text = @str_err
set @err_no=@err_no+1
return
end

if ( @fts_SCCode = '' )
begin
set @str_err ='The fts Sccode can not be null '
set @status_text = @str_err
set @err_no=@err_no+1
return
end


if ( len(@fts_employee) > 100)
begin
set @str_err ='Maximum Employee length allowed is 100 characters'
set @status_text = @str_err
set @err_no=@err_no+1
return
end


if ( @fts_employee = '' )
begin
set @str_err ='The employee fiedl can not be null'
set @status_text = @str_err
set @err_no=@err_no+1
return
end

if (@err_no=0)
begin

INSERT INTO fts_ServiceTasks (fts_employee , fts_Sccode, fts_taskdescription)
VALUES(@fts_employee, @fts_SCCode, @fts_taskdescription)

set @status_no=0
set @status_text = 'Add Service Task Ok'
end

else

begin
set @status_no=@err_no
set @status_text = @str_err
end
GO


and I called it from the ASP

<%function Add_Service_Task(fts_employee,fts_sccode, fts_TaskDescription)
cm.ActiveConnection = m_conn
cm.CommandType = 4
cm.CommandText = "fts_insert_service_tasks"
cm.Parameters.refresh
cm.Parameters(3).Value = fts_employee
cm.Parameters(4).Value = fts_sccode
cm.Parameters(5).Value = fts_TaskDescription
on error resume next
cm.Execute
if cm.Parameters(1)=0 then
exec_command=cm.Parameters(2).Value
else
call obj_utils.ErrMsg(cm.Parameters(2).Value,3000)
Response.End
end if
if err.number <> 0 then
call obj_utils.ErrMsg("System error at " & err.number & err.Description & ", please contact the administrator", 5000)
Response.End
end if
Add_Service_Task=exec_command
end function%>


I test with SQL 2k, Win2k3 OK
But with Win2k i got:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E30)
Type name is invalid.
/fmits/classes/cls_servicecall.asp, line 256


Please help me!

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

Execute SQl Task Return Decimal Type Result

Dec 3, 2007



I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.


The query in the Execute SQL Task is:
select 735.234, 2454.123

I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab

I don't want to use a for loop enumerator since there is a single row returned.

I simply want to assign these two values to SSIS Double variables (double is the closest match)


I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.

Thanks for the help

View 1 Replies View Related

Passing An Array To Query In A Table Adapter

May 23, 2007

I'm looking for a way to pass an array of values as a parameter to a query in a table adapter.  For example I want to run a query something like:SELECT * FROM menu WHERE menu_role IN (@roles)And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.For some reason I can't figure out a way to do this.  Any help would be greatly appericated.Thanks,Ryan. 

View 6 Replies View Related

Carriage Return Inside A Field Of Text Data Type?

Nov 24, 2004

how can i insert a carriage return when i update the field?

say i want to put the following inside a field:
firstline
secondline

how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'

the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines

i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'

but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline

thanks

View 3 Replies View Related







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