[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

inner join inside stored procedure sql2008r2express

Posted on 2013-01-10
5
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 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 25

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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