Solved

inner join inside stored procedure sql2008r2express

Posted on 2013-01-10
5
228 Views
Last Modified: 2013-01-11
Hi Guys,

I have the following stored procedure...
In the middle of my code is my question...


USE [GSS]
GO
/****** Object:  UserDefinedFunction [dbo].[CalcBBSymbol]    Script Date: 01/10/2013 17:00:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CalcBBSymbolv2]
(
	@jeffSymbol nvarchar(50),
	@month nvarchar(50),
	@strike nvarchar(50),
	@accountID varchar(255)
)
RETURNS nvarchar(50)
AS
BEGIN

	DECLARE @monthDigit nvarchar(50)
	DECLARE @monthLetter nvarchar(50)
	DECLARE @year nvarchar(50)
	DECLARE @bbSymbol nvarchar(50)
	DECLARE @position int
	DECLARE @final nvarchar(50)
	DECLARE @tlength int
	DECLARE @noSpaces int
	DECLARE @editSymbol as nvarchar(50)
	DECLARE @lme bit
	DECLARE @optionType as nvarchar(1)
	DECLARE @clearingBroker as nvarchar(50)

	SET @monthDigit = SUBSTRING(@month,5,2)
	
		IF @monthDigit = '01'
				SET @monthLetter = 'F'
		ELSE IF @monthDigit = '02'
				SET @monthLetter = 'G'
		ELSE IF @monthDigit = '03'
				SET @monthLetter = 'H'
		ELSE IF @monthDigit = '04'
				SET @monthLetter = 'J'
		ELSE IF @monthDigit = '05'
				SET @monthLetter = 'K'
		ELSE IF @monthDigit = '06'
				SET @monthLetter = 'M'
		ELSE IF @monthDigit = '07'
				SET @monthLetter = 'N'
		ELSE IF @monthDigit = '08'
				SET @monthLetter = 'Q'
		ELSE IF @monthDigit = '09'
				SET @monthLetter = 'U'
		ELSE IF @monthDigit = '10'
				SET @monthLetter = 'V'
		ELSE IF @monthDigit = '11'
				SET @monthLetter = 'X'
		ELSE IF @monthDigit = '12'
				SET @monthLetter = 'Z'
	
	SET @year = SUBSTRING(@month,4,1)	
	SET @month = @monthLetter + @year
	
--******  QUESTION HERE
-- I want to do the following select statement in here... but do not know how to implement it.  I want to say...
SET @myTEMP_VARIABLE = SELECT [Clearing_Broker] 
FROM dbo.AccountsDB
INNER JOIN
ON
dbo.AccountsDB.Account_ID = @accountID
-- END OF question here *******************

	
	SELECT @bbSymbol = BBM_SYMBOL
	FROM MarketsDB
	WHERE JEFF_SYMBOL = @jeffSymbol
	
	IF @jeffSymbol = 'LME ZSD' Or @jeffSymbol = 'LME NID' Or @jeffSymbol = 'LME PBD' Or @jeffSymbol = 'LME SND' 
		or @jeffSymbol = 'LME AHD' or @jeffSymbol = 'LME CAD'
		SET @lme = 'true'
	
	SET @tlength = LEN(@bbSymbol)
	SET @editSymbol = REPLACE(@bbSymbol,' ', '')
	SET @noSpaces = @tlength - LEN(@editSymbol)
	--if @noSpaces > 1, then this is an option
	
	
	SET @position = CHARINDEX(' ',@bbSymbol)
	
	IF @lme = 'true'
		SET @final = @bbSymbol
	ELSE
		IF @position = 2
			BEGIN
				IF @noSpaces > 1
					BEGIN
						SET @optionType = RIGHT(@jeffSymbol,1)
						SET @final = LEFT(@bbSymbol,@position-1) + ' ' + @month + @optionType + ' ' + @strike + ' ' + RIGHT(@bbSymbol,@tlength-@position-2)	
					END
				ELSE
					SET @final = LEFT(@bbSymbol,@position-1) + ' ' + @month + ' ' + RIGHT(@bbSymbol,@tlength-@position)
			END
		ELSE
			BEGIN
				IF @noSpaces > 1
					BEGIN
						SET @optionType = RIGHT(@jeffSymbol,1)
						SET @final = LEFT(@bbSymbol,@position-1) + @month + @optionType + ' ' + @strike + ' ' + RIGHT(@bbSymbol,@tlength-@position-2)
					END
				ELSE
						SET @final = LEFT(@bbSymbol,@position-1) + @month + ' ' + RIGHT(@bbSymbol,@tlength-@position)
			END

	RETURN @final

END

Open in new window


anyone have any idea how I return that value

hope this question is clear
0
Comment
Question by:solarissf
5 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38765181
I think this is what you want:
SELECT @myTEMP_VARIABLE =  [Clearing_Broker] 
FROM dbo.AccountsDB
WHERE dbo.AccountsDB.Account_ID = @accountID

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 38765600
Not clear what your question is. If you need to assign to the variable, see the previous comment. If you need to return this value then add this statement after assignment:

return @myTEMP_VARIABLE

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38765732
I have the following stored procedure...
I hate to point out the obvious, but that is not a Stored Procedure...

If it is really meant to be a Function than you are going to have to tell us what you are trying to do.
0
 
LVL 1

Expert Comment

by:jinsonaf
ID: 38766505
If you want to inner join something you need to have two tables or something like that.

So it will look like this

SELECT @myTEMP_VARIABLE = [enter fields here]
FROM tbl1
INNER JOIN tbl2
ON tbl1.fld1 = tbl2.fld2
0
 

Author Comment

by:solarissf
ID: 38766898
I think this worked..

SELECT @myTEMP_VARIABLE =  [Clearing_Broker] 
FROM dbo.AccountsDB
WHERE dbo.AccountsDB.Account_ID = @accountID

Open in new window


Thanks!!!

And yes, it was my mistake... I have a stored procedure... that calls THIS FUNCTION.
so I should have said function.

appreciate everyones help!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question