Function Returning Multiple Values?

Mar 1, 2012

I have a table called tableA and i am fetching 10 rows from table.

select dept_id from tableA where branch = 'Chennai';

I got 10 records.

dept_id
-------
001
002
003
004
005
so n.....

Now i want to pass these dept_ids dynamically to a function parameter.

ie. exec function_name (@dept_id).

How do i write a function?

View 3 Replies


ADVERTISEMENT

Function Returning Middle Values

Mar 7, 2007

I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned.
i.e. SELECT * FROM GetMiddleValues (3);

If anyone has any ideas on how to accomplish this, that would be great.

Thanks

View 1 Replies View Related

Transact SQL :: CASE With Subselect And DATEADD Function Returning NULL Values

Jun 15, 2015

I'm running the following test query on a single table:

SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)    
WHEN 'Monday' then 'Monday'  
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') 
END AS TestCase,

[Code] ....

And here's an example of the output I'm getting:

Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?

View 7 Replies View Related

Query Returning Multiple Wrong Values

Jan 16, 2013

I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)

Code:
SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY
FROM CUSTOMER C
INNER JOIN USERS U ON C.ID = U.ID
INNER JOIN ORDERS O ON C.ID = O.ID
INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID

[Code] .....

The qry pulls like this: (wrong way)

HTML Code:
company partID Qty
Acme Inc ABC123 3
Acme Inc ABC123 4
Acme Inc ABC123 100
Acme Inc KLM444 3
Acme Inc KLM444 4
Acme Inc KLM444 100
Acme Inc QRP456 3
Acme Inc QRP456 4
Acme Inc QRP456 100

It should be:

HTML Code:
Co. part Qty
Acme Inc ABC123 3
Acme Inc KLM444 4
Acme Inc QRP456 100

The Qty field has duplicates. I've tried various ways, but I cannot make this work.

View 14 Replies View Related

Returning Multiple Values From A Stored Procedure.

Feb 7, 2007

my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column.
Is there a way to return the content of these two tables?
Is there a way to return a table from the stored procedure?

Thanks in advance
waamax

View 1 Replies View Related

SELECT Returning Multiple Values In A Stored Proc

Jul 20, 2005

HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam

View 6 Replies View Related

Transact SQL :: Returning Multiple Values From Column In Select Statement?

Nov 26, 2015

I am writing a query and have the bulk of it already written. 

I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.

I'd like for my query to return all lines that have the value R, F, H.

My where clause is written like this 

WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')

I know I'm missing something....

View 3 Replies View Related

Return Multiple Values From A Function

Jun 19, 2007

searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005?

e.g., I want to do this:

select id, data, whatever, dbo.fnMyFunction(id, whatever) from table

and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction

possible? easier way? thanks in advance...

View 4 Replies View Related

How To Return Multiple Table Values From A Function

May 5, 2004

Hi,
We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.

Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),
As
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Return
End

This Works Fine :-
And Gives The Reqd. Results.

But,

If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.



Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int)
As
Begin
Declare @cnt Int
Set @cnt = 1
While @cnt <= 2
If @cnt =1
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Set @cnt = @cnt + 1
End
If @cnt =2
Begin
Insert @k2
Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
On A.intcustomerid = B.intcustomerid
Where Chremail <> ' ' And Chremail Is Not Null
And Intpreferenceid In (6,7,2,3,12,10)
Set @cnt2 = @cnt2 + 1
End
End
Return
End


Can We Return Two Tables Or Is It Not Possible ?
Pls Chk Into This And Tell Me.

Thanks.

View 13 Replies View Related

SQL Server 2008 :: Function That Replaces Multiple Values

Jul 16, 2015

I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.

CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN

[code]...

View 4 Replies View Related

SQL Server 2008 :: Table Valued Function Where Parameter Has Multiple Values

Jan 29, 2015

Is it possible to pass multiple values to a TVF, such as using an IN clause?

View 6 Replies View Related

Function Returning A Table

Nov 16, 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar)

RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype
varchar(100),CDDate datetime, Label varchar(100))
AS

BEGIN

IF @art <>'/'
INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as
'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label,
dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and
track.trackid=cdtrack.trackid and label.labelid=cd.labelid and
shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid
and artist.artist=@art
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate,
dbo.Label.Label, dbo.Shelf.Shelf

if @cd <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @tra <> '/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @gen <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @cdate<>'01/01/1900'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @label<>'/'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
return
end
---------------------------------------------------------------------
upon running executing this function with valid values i am not getting any results.
anything is wrong?
thank you,

View 13 Replies View Related

SQL Function Returning Varchar(max)

Mar 1, 2007

I have a SQL function which returns a varchar(max). This gets truncated when the length is greater than 8000. Could you let me know how do I get the return value in a function without it being truncated.

View 1 Replies View Related

CLR SP Or Function Returning A Table

May 7, 2008

Hi guys,

I have to work with some configuration data that is stored in rows as a comma separated values. Something like this:



Key1
A,1,Z,0;B,2,Y,9;C,,8,X;

Key2
Alpha,101;Beta,102;



Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR.

There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this:



A
1
Z
0

B
2
Y
9

C
3
X
8


But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other).

So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF?

Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function.


THANKS!




Code Snippet
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
return;
StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE (");
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i));
}
SqlCreate.Remove(SqlCreate.Length - 1, 1);
SqlCreate.AppendLine(");");
StringBuilder SqlInsert = new StringBuilder();
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
SqlInsert.Append("INSERT INTO @Output VALUES (");
// busca las diferentes "columns" ~ Charly
foreach (string column in row.Split(SeparatorColumn))
{
SqlInsert.AppendFormat("'{0}',", column);
}
SqlInsert.Remove(SqlInsert.Length - 1, 1);
SqlInsert.AppendLine(");");
}
}
string SqlSelect = "SELECT * FROM @Output;";
cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect;
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
reader.Dispose();
}
conn.Close();
conn.Dispose();
cmd.Dispose();
}
};







Code Snippet
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static IEnumerable fGetConfigurationAsTable(string Key)
{
SqlConnection conn = new SqlConnection("Context Connection=true");
string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key);
SqlCommand cmd = new SqlCommand(SqlCmd, conn);
conn.Open();
string Value = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
conn.Dispose();
cmd.Dispose();
DataTable dt = new DataTable();
if (Value.Length > 0)
{
char SeparatorRow = ';';
char SeparatorColumn = ',';
if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35)
{
// throw exception
}
string ColumnName;
for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++)
{
ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i));
dt.Columns.Add(ColumnName, Type.GetType("System.String"));
}
foreach (string row in Value.Split(SeparatorRow))
{
if (row.Length > 0)
{
dt.Rows.Add(row.Split(SeparatorColumn));
}
}
}
return dt.Rows;
}
};

View 5 Replies View Related

Returning Two Values

Jul 30, 2007

I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT   COUNT(*) AS TodayCount FROM Counter     WHERE     DATEPART(YEAR, visitdate) = Year(GetDate()) AND    DATEPART(MONTH, visitdate) = Month(GetDate()) AND    DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?  

View 6 Replies View Related

Returning Values

Feb 18, 2008

Hi All

I have a stored procedure that returns a numeric value. I wish to call this procedure from within another one and have the returned value stored against a declared variable. I'm struggling with the syntax!!

Any help out there?

Thanks

Xo

View 9 Replies View Related

Returning Values From Sp

Sep 13, 2006

hi
can i return value(bool) from a sp based on the result and receive in my vb code.


suji

View 4 Replies View Related

MS SQL For Returning No Values

Nov 20, 2007

Hello,im having a problem integrating an SQL statement with my program,it works fine when theres a result but throws an error when there isno result,is there anyway in SQL to make up a row if there isnt one that matchesthe query for example in this i could put indicator values in thereturned fields then a simple check to see if its a proper field of myindicator fieldI know ISNULL works with single values but that only works if there isa row returnedDoes anyone know of any SQL syntax for MS SQL that when no row isreturned it makes on up and parses it backThank you in advance for your help!!Ben

View 4 Replies View Related

Function Returning Error During Compilation.....

Feb 12, 2004

Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @t1_sys char(10),@t1_all varchar(11)
declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@cg1,1,2)='Q$')
set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @temp_qcard values(@cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
WHILE @@FETCH_STATUS = 0
BEGIN

insert into @temp_qcard values(@t1_all)

declare @t2_sys char(10),@t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t2_all)

declare @t3_sys char(10),@t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t3_all)
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ....
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.

View 1 Replies View Related

Question About Returning A Smalldatetime From A Function

Jun 6, 2006

I've been working this for a while. Kind of new to SQL Serverfunctions and not seeing what I am doing wrong. I have this functionCREATE FUNCTION dbo.test (@Group varchar(50))RETURNS smalldatetime ASBEGINDeclare @retVal varchar(10)(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE(event_id = 13) AND (group_ =@Group))return convert(smalldatetime, @retVal, 1)ENDThe error I get isServer: Msg 296, Level 16, State 3, Procedure test, Line 6The conversion of char data type to smalldatetime data type resulted inan out-of-range smalldatetime value.1) I tried declaring @retVal as a smalldatetime and get the error "Mustdeclare the variable '@retVal'.'2) If I run that same query in query analyzer (manually inserting theparm) it returns 11/14/2006. That's what I want.If I change the function to this and run itCREATE FUNCTION dbo.test (@Group varchar(50))RETURNS varchar(50) ASBEGINDeclare @retVal varchar(50)(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE(event_id = 13) AND (group_ =@Group))return convert(smalldatetime, @retVal, 1)ENDIt now works but the return value is Nov 14 2006 12:00AMWhat am I doing wrong?TIA

View 6 Replies View Related

EncryptByKey Function Always Returning Null

Aug 8, 2006

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @Temp varbinary(8000);

Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText);

close symmetric key MyKey;

select @Temp as temp;
END


The result I get for this procedure is null. Is there something wrong with this code?

View 5 Replies View Related

Returning Null Values

Jun 4, 2007

I have a simple table on my webpage which shows data from my database.
It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value.
I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work.
If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
 
Oliver

View 6 Replies View Related

Using Identities And Returning Their Values

Aug 31, 2000

I have a table that contains an identity column. This column is updated in a stored procedure that has a linked trigger that updates another 'backup' table. How can I ensure that the value of the new row added in the table with the identity is passed back to calling client. ?

The value of @@identity is incorrect as it reflects the value in the 'backup table' not the original table. and use select max() on the original table within the stored procedure could be violated by other users attempting the same operation

View 2 Replies View Related

Returning Values From A Dynamic SQL

Feb 19, 2006

I have an accounting database which contains data from various years.The frontend is a VB.Net program. At the year end, the program createsnew voucher and transaction tables and creates new stored procedures forthem.I just append the 'new year' at the end and create themie, Vouchers2001, Vouchers2002, Vouchers2003Similarly Transactions2001, Transactions2002.The data for all the years is in the same database.Also, I maintain a table called 'Books' which contains the Years forwhich data is present in the Database. The Structure of the Books tableisBookID BookYear1 20012 20023 20034 2004My Problem is that i need to know the current balance of any ledger forany year. The method to calculate the balance for any year is to startfrom the Minimum year in the Books table and continue upto the requiredyear. The SQL is as follows.DECLARE @iLedgerID AS INT --will be passed as parameterDECLARE @iYear as INT --will be passed as parameterDECLARE @CurrentBalance as MONEYSET @iLedgerID =1DECLARE @MinBook as INTEGERDECLARE @String nVarchar(4000)SELECT @MinBook = Min(BookYear)FROM BooksWHILE @MinBook <= @iYearBEGINSET @String = ' DECLARE @TT Money ' + char(13) +' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOINdbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +'WHERE (LedgerID = @iLedgerID)'EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */SET @MinBook = @MinBook + 1ENDNow this is just a sample code. It may have a few glitches. My questionisa) Do I have to create a dynamic sql if the name of the database is notknown ahead of time. If No thenb) I need to add the balance of each year to the grand total. How do ireturn a value from a dynamic sql.TIA*** Sent via Developersdex http://www.developersdex.com ***

View 6 Replies View Related

Transact SQL :: GetDate Function Not Returning Any Results?

May 27, 2015

I am having a problem with the GETDATE().

WHERE TableName.ColumnNamne = Getdate()

The above SQL function does not return any results whereas the below SQL code returns results. Am I doing anything wrong?

WHERE SalesOrder.New_ActualShipmentDate >= GETDATE()

View 35 Replies View Related

Returning Values From Stored Procedure

Aug 16, 2004

Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?

Thanks,

View 1 Replies View Related

Returning A List Of Different Column Values

Sep 7, 2012

My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2

then run an sql statement and should return you the following
Period
1
2
4
12
31

View 3 Replies View Related

Returning Values From SP To Perl Script.

Apr 2, 2008

I am just starting to learn about stored procs and how great they can make my world. I have been able to create a pro that does some data checking and inserts based on the results but what I have not been able to figure out is how would I pass a message from the proc to my Perl script that calls it? If anyone could point me in the right direction on what commands I should be looking into I would appreciate it.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

View 1 Replies View Related

Returning Values From 2 Queries To A Single Row?

Sep 30, 2014

I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.

select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh
where luis_id in ('I','E')
and aseld.whse_id = 1
and (
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'YK')

[code]....

is it possible to get the numbers from keys 1 & 2 on the same row in a new query?

Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.

View 3 Replies View Related

Returning Records That Exclude Top 10% Of Values

Mar 6, 2008

I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?

2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159

View 3 Replies View Related

Counting Items And Returning Values

Jul 20, 2005

I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?

View 1 Replies View Related

Troubles Returning Values With SQL Statement -

Mar 28, 2006

Hi, I just want to know the following.

Is it posible to write a SQL statement that searches all values in a table where the

e.g:

"Jobid = 1" and sort the result set according to the "CategoryID"?

then if you want to display the values, you can populate them according to the "CategoryID"?



and then also write the result set to a "text file", and catagorize everything according to the "categoryID" in the following format???



Category Number: 8 Description: Site

Date Labour Hours Rate Subtotal

10/05/2005 Artisan 20 95.00 1,900.00
10/06/2005 Labourer 7 45 315.00


Category Total 2,215.00



And then after that the next "categoryid"

Thank You, I really hope you understand what I mean.

your help will be greatly appreaciated.

View 4 Replies View Related

Returning Formated Real Values

Jan 21, 2008



I have some Sp that return real values, I''m using function like SELECT CONVERT(REAL,field) FROM .... to ensure that conversion and rounding is done.

When I got the values in my application (Datagrid) I receive values having even 9 decimals

How can efectively return only 2 decimals from my query?

Thanks

View 3 Replies View Related







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