?
Solved

inner join inside stored procedure sql2008r2express

Posted on 2013-01-10
5
Medium Priority
?
233 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

752 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