Solved

inner join inside stored procedure sql2008r2express

Posted on 2013-01-10
5
224 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now