Wednesday, May 1, 2013

[SQL Server] Sql Server Stored Procedure varaible declaration

[SQL Server] Sql Server Stored Procedure varaible declaration

Sql Server Stored Procedure varaible declaration

Posted: 01 May 2013 12:02 AM PDT

i want to search data with different criteria.one is from "date" and other is from itemid and so on.in last part of procedure i used if else statement and im passing my variable "option" to select my search criteria.if i search by"date" it simple means i am not sending "itemid" value from my application to procedure .i want some advise about this how to get what i want.Option 1 Search by dateOption 2 Seatch by itemidand so one [code]PROCEDURE [dbo].[PROC_GET_SOH_REPORTOptions]( @ItemName VARCHAR(200) = '%', @To Date, @From Date, @ItemIdd int, @Option Int )ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; CREATE TABLE #tempSOH ( ItemID INT, ItemCode VARCHAR(50), ItemName VARCHAR(200), ItemLocation VARCHAR(50), created_date date, SOH NUMERIC(18,2) ) INSERT INTO #tempSOH ( ItemID, ItemCode, ItemName, ItemLocation,created_date ) SELECT Item_Master.Item_ID, Item_Master.Item_Code, Item_Master.Item_Name, Item_Location_Master.Rack_Name,Item_Master.Created_Date FROM Item_Master INNER JOIN Item_Location_Master ON Item_Master.Rack_ID = Item_Location_Master.Rack_ID WHERE Item_Master.Item_Name LIKE +'%'+ @ItemName +'%' UPDATE #tempSOH SET SOH = ISNULL((SELECT SUM( Stock_Quantity) FROM Stock_Header WHERE Stock_Type NOT IN ('Stock Out') AND Item_ID = #tempSOH.ItemID),0) UPDATE #tempSOH SET SOH = SOH - ISNULL((SELECT SUM( Stock_Quantity) FROM Stock_Header WHERE Stock_Type = 'Stock Out' AND Item_ID = #tempSOH.ItemID),0) if (@option)='1' SELECT ItemID, ItemName, ItemLocation, SOH,created_date FROM #tempSOH where created_date>=@From and created_date<=@to ORDER BY ItemName else if(@Option ='2') SELECT ItemID, ItemName, ItemLocation, SOH,created_date FROM #tempSOH where ItemID>=@ItemIdd ORDER BY ItemName DROP TABLE #tempSOH [/Code]

Stored procedure to transfer Applications into Loans mismatching number of BEGIN and COMMIT statementserror:

Posted: 30 Apr 2013 11:11 PM PDT

I had a problem when transfering an active application and to a loan table where i have to calculate the monthly payment. When i try to execute the stored procedure i get this error:Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1 Here is the code use in the procedure. When i executed is says commited succesfully. So i dont know why i get this message. I tried several things i seen on google but this getting this annoying message. I will very thankfull if someone can help me with this. ALTER Procedure [dbo].[Transfer_ApprovedApp_intoLoans]@FirstPaymentDate as DateAS Begin Begin Transaction DECLARE @RowCount As Int; DECLARE @ApplicationID as char(5); DECLARE @LoanTypeID as char(5); DECLARE @LoanAmount As Numeric(8,4); DECLARE @LoanRate As Numeric(12,8); DECLARE @LoanYears As Int; DECLARE @NumberOfMonths As Int = 12; /**Number of months**/ DECLARE @MonthlyRate As Numeric(12,8); /**To use to convert the anual rate to monthly rate **/ DECLARE @NumberOfPeriods As Int; /**Months multiplied by the number of years**/ DECLARE @MonthlyPayment As Numeric(8,4); DECLARE @RateinPercent AS Integer = 100 DECLARE @Status AS Char(1) /**Check if application is active and approved**/ SELECT * FROM Applications WHERE ApplicationID = @ApplicationID AND Status = 'A' /**Check if Loan exists**/ SELECT @RowCount = Count(*) FROM Applications WHERE ApplicationID = @ApplicationID IF @RowCount = 0 BEGIN PRINT 'Applicación' + @ApplicationID + ' no está aprobada o activa'; Return; END; Else /**Declare cursor**/ Declare AppTransferLoanCursor CURSOR for Select ApplicationId,LoanTypeId,LoanAmount,LoanRate,LoanYears, [Status] From Applications Where Status = 'A' and Transferred = 'N'; /** Open Cursor **/ Open AppTransferLoanCursor Fetch Next from AppTransferLoanCursor Into @ApplicationID, @LoanTypeID, @LoanAmount, @LoanRate, @LoanYears, @Status While @@FETCH_STATUS = 0 Begin /**Calculate monthly payment**/ Set @MonthlyRate =(@LoanRate/@RateinPercent)/@NumberOfMonths Set @NumberOfPeriods = @LoanYears* @NumberOfMonths Set @MonthlyPayment=@LoanAmount/(power(1+@LoanRate,@NumberOfPeriods)-1)*(@LoanRate*power(1+@LoanRate,@NumberOfPeriods)) /*Mark the application as transferred**/ Update Applications Set Transferred = 'Y' Where ApplicationId = @ApplicationId /**Transfer values into Loans**/ Insert into Loans(LoanType,LoanAmount,LoanRate,LoanYears,MonthlyPayment,Status) Values(@LoanTypeID,@LoanAmount,@LoanRate,@LoanYears,@MonthlyPayment, @Status) /**To fetch next row**/ Fetch Next from AppTransferLoanCursor Into @ApplicationID, @LoanTypeID, @LoanAmount, @LoanRate, @LoanYears, @Status EndCommit Transaction Close ApplicationTransferCursor DEALLOCATE ApplicationTransferCursor End

Query for non-existent entries

Posted: 30 Apr 2013 08:07 PM PDT

Hi,I'd like to build a query that produces the missing entries in a scores per week table. Say that you have a table with the following structure:[code="other"]ID|Name|noWeek|Score1 John 1 22 John 2 33 John 3 14 John 5 7..................[/code]Let's say the maximum week number is 5 for all possible people in this table. What I'd like is a query that lists the missing entries. Sth like this: - John 4 -I've got the number of weeks (1-2...5) in a lookup table, if that helps. So I guess we're looking at some kind of CROSS JOIN, or cartesian product, or the like. Any ideas of how this could be done? Thanks,

Exclude rows with blanks in a particular field

Posted: 30 Apr 2013 07:33 PM PDT

I know this is a popular one and I've Googled it but I haven't found anything that has worked yet, so apologies if this is incredibly obvious.I'm trying to amend a view to exclude records that are either null or blank in a field called CURRENT_POINT[code="other"]...and CURRENT_POINT is not null and DATALENGTH(CURRENT_POINT)> 0[/code]The nulls are fine, but I'm still getting the blanks. I hoped DATALENGTH would resolve that but it hasn't.As I said, I'm sorry if it's obvious. Can anybody advise me please?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog