Get the returned value from a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get the returned value from a SP

Author

Topic

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-24 : 13:43:06

Hi all,. I have a SP that gets a particular value from table as below. This works fine.

@ColumnName varchar(50), @ReturnValue Decimal(18,4) = NULL OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT @returnVar = '+@ColumnName+' FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),
[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'
EXEC sp_executeSql @SQL, N'@returnVar Decimal(18,4) OUTPUT', @ReturnValue OUTPUT

SELECT @ReturnValue
END

I call it using

DECLARE @outputVar Decimal(18,4)
EXEC A_GetBasicEsc 'a', @outputVar OUTPUT

However the way that I am trying to pass it to another select statement is not working. See below. The value passed as columnName is retrieved from a field within the select statement

@EscPercent = (EXEC A_GetBasicEsc Left(Review_Test.[Paterson Grade],1), @outputVar OUTPUT),

DECLARE @EscPercent Decimal(18,4)
DECLARE @Band Varchar(1)
DECLARE @outputVar Decimal(18,4)
DECLARE @SQL NVARCHAR(4000)

UPDATE Review_Test

SET @Band = Left(Review_Test.[Paterson Grade],1), --Get Band

@EscPercent = (EXEC A_GetBasicEsc Left(Review_Test.[Paterson Grade],1), @outputVar OUTPUT), --Get escalation for band

[MKT Basic Salary Min] = dbo.Alpha_CalcEscMarket(Market_Data.Min,@EscPercent), --Return mkt min escalated for selected band

[MKT Basic Salary Mid] = dbo.Alpha_CalcEscMarket(Market_Data.Mid,@EscPercent), --Return mkt mid escalated for selected band

[MKT Basic Salary Max] = dbo.Alpha_CalcEscMarket(Market_Data.Max,@EscPercent) --Return mkt max escalated for selected band

FROM
Review_Test INNER JOIN Market_Data ON Review_Test.[Market Structure] = Market_Data.[Market Structure] AND Review_Test.[Paterson Grade] = Market_Data.[Paterson Grade]

WHERE (Market_Data.[Structure Type] = 'Basic') AND (Market_Data.Range = 'Lower Quartile (25th Percentile)')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-24 : 13:46:23

Why do you need the variable @EscPercent at all? you cant directly use the variable @outputVar which gets value returned by SP automatically in all statements after that.

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-24 : 14:45:35

Hi
The table review_test contains a field called Paterson Grades and the records saved are A1 to A3, B1 to B5 and goes on till F5. Each row will have its own grade. These Grades are broken down into Bands. 'A' band will be A1 to A3 and so on. Now in the review_test there is no column for bands so to get the band I use LEFT(Paterson_Grade,1) to return only the letter. I then pass this letter to another SP to get a decimal value from another table called BasicEsc_tbl

This table has 12 records Each record contains the following columns Date|A|B|C|D|E|F|. If you cross reference the required date with the selected column it gives you a decimal value. This value is then returned for usage in a calculation

Each record in review_test table has a different grade so every record will have a different calculation as the update query updates.

I have written the formula to do this in VB.Net but when you have 500 or more record it takes forever to do the involved calculations. The few calculations you see in the SP above is not even a third of what it does and the formula retrieves data from at least 10 different tables. some I can Modt of them I can create relationships others I cannot.

I hope this exlpains what I am trying to achieve.

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-24 : 16:07:40

Hi
Solution found.

@EscPercent = (SELECT dbo.GetBasicEsc(LEFT(Review_Test.[Paterson Grade],1)) FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)),

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 00:13:02

quote:
Originally posted by mobile@digitaltrendz.co.z

Hi
Solution found.

@EscPercent = (SELECT dbo.GetBasicEsc(LEFT(Review_Test.[Paterson Grade],1)) FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)),

for this to work correctly, you shouls ensure that select statement returns only a single record always.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources