Solved

inner join inside stored procedure sql2008r2express

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

862 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

22 Experts available now in Live!

Get 1:1 Help Now