Stored Procedure help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure help!

Author

Topic

Chance1234
Starting Member

6 Posts

Posted - 2002-06-06 : 04:24:03

Im getting 3 error messages on the following stored procedure im using SQL Server 7 and im using DBartisan.

line 10 the name vtexC is not permited in this contstants, only constants, experession, or variables allowed here. column names are not permitted
line 19. incorrect syntax near the keyword 'INNER'
line 30. incorrect syntax near the keyword 'INNER'

the basic jist of what iam trying to do is , call this stored procedure from visual basic, pass over an array of values (vtExc) and a coupl d of other arguments, throw the data from vtexc into a temp table, run a query get the results and pull them back in to the VB, go down the pub and celelbrate, unfortunatly thats not happening at the moment :-(

I am very very new to stored procedures so would appreciate any help what so ever

regards

Chance

www.5ylac.s5.com

heres that procedure

CREATE PROCEDURE getDataFromExcelAndBack(@vtExc as var,@strM as string, @intswtch1 as int)

AS

BEGIN

CREATE TABLE #Temp_Universe([IssuerID] INT NOT NULL)

INSERT INTO #Temp_Universe
VALUES(vtexC)

if intswtch1 = 1

select distinct sqrt(((sum([@strM] * [@strM] )*count([@strM] ))-(sum([@strM] )*sum([@strM] )))/(count([@strM] )*(count([@strM] )-1))) AverageSectorValue,ca.effective_date TheDate,count(" & strMetric & ") IssuerCount from cusp_summary cs,cusp_pwr_aggregate pwrag,cusp_aggregate ca,tblDataTemp dt,cusp_ratings cr1,cusp_ratings cr2
where ca.effective_date = pwrag.effective_date And ca.effective_date = dt.[Date]
and ca.issuer=dt.issuer
and ca.issuer=pwrag.issuer and ca.issuer=cs.issuer and cr1.SPRating=dt.SPRating and cr2.MoodyRating=dt.MoodyRating
INNER JOIN ca.issuer ON(#Temp_Universe.IssuerID = ca.issuer)

else

SELECT DISTINCT AVG([tdt].[@strM]) as AverageSectorValue,
tdt.[Date] as TheDate,
count([tdt].[1ssdSpreadWideningRisk]) as IssuerCount
FROM tblDatatemp tdt, tblAnalystUniverse ta
WHERE tdt.Issuer = ta.equityticker
INNER JOIN tdt.Issuer ON (#Temp_Universe.IssuerID = tdt.issuer)
Group by tdt.[date]

End if

DROP TABLE #TempUniverse

Filmmaker, Gentlemen and club promoter

http://www.5ylac.s5.com

http://public.fotki.com/chance1234/

http://www.wrap.4t.com

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-06 : 04:34:41

line 10 Error: All variables MUST be preceded by the @ symbol (Same as the parameter name)

Line 19 and 30 Error: INNER joins MUST come before the WHERE clause.

There are a lot more errors than that in your code.. Have you got BOL (Books on Line)?

DavidM

"SQL-3 is an abomination.."

Chance1234
Starting Member

6 Posts

Posted - 2002-06-06 : 05:32:51

no i havent, also ive made teh above changes now im getting incorrect syntax near drop

Filmmaker, Gentlemen and club promoter

http://www.5ylac.s5.com

http://public.fotki.com/chance1234/

http://www.wrap.4t.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-06 : 05:53:51

That will be trying to use End if within a procedure for sql server, read up on the use of if within T-SQL help... there is no End if.....

Also, looking at the rest of the SQL, I do not know what you are trying to do, but can tell you it will not work.. Perhaps if you tell us what you are trying to do and post the table structures you have, you may get more help...

Peace

Rick

Chance1234
Starting Member

6 Posts

Posted - 2002-06-06 : 06:14:34

i will be the first to admit i have little clue about what iam doing, this is not my area but find myself stuck having to do this, that old IT cliche of "Ah as you know this you must also know this" anyway here is the run down of what i am trying to do,

Firstly i have a excel spreadsheet, though a serious of links, calculations etc etc i end up with a sheet with a list of issuers (stock Market) we have several databases here and hundred and hundreds of tables filled with analytical data relating to the Issuers.

What i want to do is pass my list from excel and get the data from the database for the appropiate issuers,

now originally this was being done with a query like the following

Select distinct avg([tdt].[1ssdSpreadWideningRisk]) as AverageSectorValue,
tdt.[date] as TheDate,
count([tdt].[1ssdSpreadWideningRisk]) as IssuerCount
FROM tblDatatemp tdt, tblAnalystUniverse ta
WHERE tdt.Issuer = ta.equityticker
AND Issuer in ( 'BA/LN', 'BBD/A CN' ,'HO FP','CON GR' ,'FR FP' ,'GT' ,'BMW GR' ,'DCX GR' ,'F' ,'F IM','SCVB SS' ,'UG FP' ,'VOLVB SS' ,'VOW GR' ,'AABA NA' ,'DEXB BB', 'USI FP' ,'ALLD LN' ,'CBRY LN' ,'DGE LN' ,'EEEK GA'
,'KO' ,'BP/ LN' ,'COC' ,'ENI IM' ,'FP FP' ,'' ,'IBM' ,'MOT' ,'BT/A LN' ,'CW/ LN' ,'DTE GR' ,'FON' ,'FTE FP'
,'HTO GA' ,'KPN NA' ,'OL IM' ,'OOM LN' ,'PTC PL' ,'SRA1V FH' ,'T' ,'TDC DC' ,'TEF SM' ,'TEL NO' ,'TI IM' ,'TLS AU'
,'VOD LN' ,'VZ' ,'BATS LN' ,'GLH LN' ,'IMT LN' ,'MO' ,'BAA LN' ,'BAY LN' ,'SGC LN' ,'TPG NA' ,'AGS SM'
,'AWG LN' ,'CNA LN' ,'EDP PL' ,'EDS IM' ,'ELESM' ,'ENEL IM' ,'FUM1V FH' ,'GASSM' ,'IBE SM' ,'IOG LN'
,'KEL LN' ,'LAT LN' ,'NGG LN' ,'RWE GR' ,'SPW LN' ,'SSE LN' ,'SZE FP' ,'UU/ LN'
,'VIE FP')

Group by tdt.[date]

BUT!!! with a much bigger IN statement than above which was causing the server to crash
the things in the in statement 'SPW LN' ,'SSE LN' ,'SZE FP'for example are the issuers which i have in a list in excel,

Now the SQL Line above was also being created by several if then else statements for example where it says [1ssdSpreadWindeningRisk] thats created from a statement similiar to be below in VBA

IF intMySelection = 1 then
strAdd = "1ssdSpreadWindeningRisk"
elseif intmyselection = 2 then
stradd = "GauhProbabilitySect"
else
stradd = "PWRWideningRIsk
end if

StrSQL = "SELECT distinct avg([" & stradd & "])

the "in" statement was being created like the following

For Each Var in Range("A1:A100")
strIn = strin & "'" & Var & "',"
next

STrsql = Strsql & " Issuer IN (" & stroror & ")

Objconnection.execture (strsql)

Thats putting its simply theres actually about 600 lines of code to get to this statement

so what my plan was, was to create a stored procedure, pass the values from teh spreadsheet in an array to the stored procedue, create a tempoary table, then where all the ifs are above for stradd passing them to the stored procedyure (@strm in procedure) running the SQL statement and gettign down the pub. intswtch is a switch becasue depending on an option on the spreadhseet determines which queru is run.

Filmmaker, Gentlemen and club promoter

http://www.5ylac.s5.com

http://public.fotki.com/chance1234/

http://www.wrap.4t.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-06 : 06:27:14

Why not just put your issuers into a SQL table and do

WHERE Issuer IN (SELECT Issuer FROM tblIssuer)

This should solve your problem and you will not have to do much....

And you can always DTS these from the excel spreadsheet to minimise manual input....

Peace

Rick

Chance1234
Starting Member

6 Posts

Posted - 2002-06-06 : 06:42:26

i would love to my original plan was
similiar, just to have a table in sql

and three lines of sql

"DELETE * FROM tbltemp"
"INSERT INTO TBLtemp etc etc
"SELECT * FROM Bigtable where issuer = tbltemp.issuer etc etc"

but i cant do that! they dont want any extra tables in the database.

mine is not to reason why but to find reasons to do not die yardy yardy yar

Filmmaker, Gentlemen and club promoter

http://www.5ylac.s5.com

http://public.fotki.com/chance1234/

http://www.wrap.4t.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-06 : 06:48:46

They don't want a table for the issuers??????

Do they already have one? Surely they must have.... If so ask them to create a view for these issuers -- Give them a loooonnnggg list and watch their faces drop, always works for me......

Peace

Rick

Chance1234
Starting Member

6 Posts

Posted - 2002-06-06 : 07:05:36

yeah they have several but each time this spreadsheet is run (daily) a new list of Issuers need to be analysed. this spreadsheet basically takes in movers and shakers of the previous day etc etc

personally i dont see why they analyse them anyway as im sure you would get the same results if you got a copy of the FT and throw a dart at it.

Filmmaker, Gentlemen and club promoter

http://www.5ylac.s5.com

http://public.fotki.com/chance1234/

http://www.wrap.4t.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-06 : 07:13:35

Keeps people in jobs, they just hate people like us who see the farce in it...

Go back to the Admin and tell him/them that you either:-

a) create a temp table for this which you will delete at the end of running the program - Another DROP TABLE ....

b) Create a table in Access (maybe link all the tables you need from SQL) and use this instead...

or

C) Ask him to creae a DB on that or another server just for your one table...

Peace

Rick

Chance1234
Starting Member

6 Posts

Posted - 2002-06-06 : 07:41:09

will do,

thanks for the help anyway

Filmmaker, Gentlemen and club promoter

http://www.5ylac.s5.com

http://public.fotki.com/chance1234/

http://www.wrap.4t.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources