I have an accounting database which contains data from various years.
The frontend is a VB.Net program. At the year end, the program creates
new voucher and transaction tables and creates new stored procedures for
them.
I just append the 'new year' at the end and create them
The data for all the years is in the same database.
Also, I maintain a table called 'Books' which contains the Years for
which data is present in the Database. The Structure of the Books table
is
BookID BookYear
1 2001
2 2002
3 2003
4 2004
My Problem is that i need to know the current balance of any ledger for
any year. The method to calculate the balance for any year is to start
from the Minimum year in the Books table and continue upto the required
year. The SQL is as follows.
DECLARE @iLedgerID AS INT --will be passed as parameter
DECLARE @iYear as INT --will be passed as parameter
DECLARE @CurrentBalance as MONEY
SET @iLedgerID =1
DECLARE @MinBook as INTEGER
DECLARE @String nVarchar(4000)
SELECT @MinBook = Min(BookYear)
FROM Books
WHILE @MinBook <= @iYear
BEGIN
SET @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 JOIN
dbo.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)'
Now this is just a sample code. It may have a few glitches. My question
is
a) Do I have to create a dynamic sql if the name of the database is not
known ahead of time. If No then
b) I need to add the balance of each year to the grand total. How do i
return a value from a dynamic sql.
TIA
*** Sent via Developersdex http://www.developersdex.com ***
Created a stored procedure which returns Selected table from database. I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure ( @condition varchar(20), @ID bigint, @date1 as datetime, @date2 as datetime )
AS /* SET NOCOUNT ON */ IF @condition LIKE 'all' SELECT CllientEventDetails.* FROM CllientEventDetails WHERE (ClientID = @ID)
IF @condition LIKE 'current_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom <= ISNULL(@date1, EventFrom)) AND (EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE '' SELECT CllientEventDetails.* FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
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?
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!!
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
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.
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
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?
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.
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
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.
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?
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?
SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)
If(@V_Facilities=null)
Set @pOutput = @V_Facilities
Return @pOutput;
Else
Set @pOutput = @V_Facilities
Return @pOutput;
GO
Anyone correct this query , I want return the output from this procedure
Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.
My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'
Please could anyone shed ligth on this problem?
here the code for the stored procedure:
CREATE PROCEDURE dbo.GetTillInfo ( @TillIdR varchar(50), @Password varchar(50) ) AS
declare @TillId int declare @configpath varchar(150)
IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password) BEGIN
set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password) select @TillIdR as 'TId'
set @configpath = (SELECT configpath from customer,tills where tills.customerid = customer.id and tills.id = @login) select @configpath as 'ConfigPath' END ELSE BEGIN set @TillIdR = 0 select @TillIdR as 'TId' set @configpath ='' select @configpath as 'ConfigPath' END GO
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob INsert into b values(1,1,2,2) select 0
(this is a cut up version of the sp just to show about the way it is formated) Please help.
I am having trouble using Output parameters. I have set up an Execute SQL Task to call a Stored Procedure. I am passing an input parameter and indicating 1 global variable for output to retrieve a unique value from the stored procedure call. When I execute the step, it completes successfully but nothing is returned in my output parameter for the unique value??? Below is the call that I use:
EXEC SYS_GENERATE_ID ?
-in the Execute SQL Task I click the Parameters button to set up the input: Status = Parameter1 and the Output Variable Type: Rowset = GUID.
the stored procedure: CREATE PROCEDURE dbo.SYS_GENERATE_ID (@Statusvarchar(20), @GUID uniqueidentifier = NULL OUTPUT) AS BEGIN SET @GUID = NEWID() INSERT INTO dbo.ACTIVITY (GUID, STATUS) VALUES (@GUID, @Status) SET @GUID = convert(varchar(50), @GUID) RETURN @@ERROR END
A table is correctly populated but the GUID value does not make it back to the calling task??? The Global Variable Type under Package Properties has been changed to "Dispatch" and the value is "Not Displayable"??? Any help that is offered will be appreciated GREATLY!!
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
i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected. there are 3 parameters, date range, receipt, po # if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
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?
I need to return row counts for a list of all our users. The problem with the first query is that it doesn’t search for names within the column, it considers a list of email addresses a unique entry. I need to be able to see how many times each email address appears in the database. The second query obviously does that but I don’t want to have to copy and paste 500 usernames. the to_addr_head is a text column if that matters. Thanks!!
SELECT Table1.to_addr_head, COUNT(*) AS "COUNT(*)" FROM Table1 group by Table1.to_addr_head ORDER BY "COUNT(*)" DESC;
select count(*) where Table1.to_addr_head like '%username%'
I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results.
The code is as follows:
<Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean, ByVal username As SqlString)
Dim adspath As New StringBuilder() adspath.Append(LDAP://[.......]/ou=Members/cn=) adspath.Append(username)
If username.ToString().Length > 0 Then Dim uobject As New DirectoryEntry(adspath.ToString(), "", "", System.DirectoryServices.AuthenticationTypes.Anonymous) If Not (uobject Is Nothing) Then exists = True Else exists = False End If End If
End Sub
The same code works fine from an ASP.NET. If I deploy the code and execute it with
exec LDAP_UserExists 'username'
I receive the error
Error converting data type varchar to bit.
And if I right-click and select "Execute Stored Procedure..." I receive @exists = 1 and Return Value = 0, regardless of the value I pass in as the username parameter.
Given that the same code works correctly on the ASP.NET page I suspect that this error has something to do with the <out()> parameter in the stored procedure declaration.
Can anyone suggest the correct method of performing this query?
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
I have a data flow task and trying to transform datas OLTP to STG db and i have lookup tables.
I do lookuping like this
first a lookup that lookup my table with connected input column parameter
second a derived column is connected to lookup's error output for when lookup can't find the value and this derived column returned "0" or "-1" this means that lookuped value can't find and insert this value to my table
third a union that union lookup and derived column
i want to ask this is there any different solution for doing this, because if i more than 5 or 6 lookup in my ssis package i add all of them derived columns and unions and when i change something i have to change or correct the unions step by step.