Parsing string in sproc

[ChopinFan]

I'm having problems parsing a string that is passed to this stored procedure. It works fine (creates the view) when only one StudentNumber is passed to it, but when multiple numbers are passed (separated by commas with no spaces), it returns no records. How can I do this differently?

ALTER PROCEDURE sp_SprocName

@StudentNumber varchar(500)

AS

Declare @inCriteria as Varchar(500)
Set @inCriteria = '''' + (Replace(@StudentNumber ,',',''',''' )) + ''''
declare @SQL as varchar (2000)

set @SQL = 'SELECT ... WHERE (StudentNumberField = '+@inCriteria+')'

Declare @MyView as varchar (5000)
Set @MyView ='alter view vw_ViewName as ' +@SQL
Exec (@MyView)

 

[NoCoolHandle]

Check out option 3 of

http://tek-tips.com/faqs.cfm?fid=3979

 

[gmmastros]

Check out htis thread thread183-1167924

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChopinFan]

Thanks! Good info. I'll post again if I run into trouble.

 

[ChopinFan]

Trouble in paradise...
I'm trying out option 1 of nocoolhandle's link first as it seems the easiest. I'm getting an odd message though and I'm reasonably sure it's a dumb mistake on my part.

I made some changes so I'm now passing two variables instead of one. @StudentNumber is a string of parsed four-digit numbers and @Country is a two-letter code.

Here's my front end code passing variables to the sproc:
With Com
.ActiveConnection = Con
.CommandType = adCmdStoredProc
.CommandText = "dbo.sp_MM_TEMPLATE_Update_CreateView"
.Parameters.Append .CreateParameter("@StudentNumber", adVarChar, adParamInput, 500, Trim(StudentNumber.Value))
.Parameters.Append .CreateParameter("@Country", adVarChar, adParamInput, 2, Trim(Country.Value))
End With
Com.Execute , , adExecuteNoRecords

Here is my sproc:
ALTER PROCEDURE sp_MM_TEMPLATE_Update_CreateView

@StudentNumber varchar(500),
@Country varchar(2)
AS

declare @SQL as varchar (5000)
set @SQL = '
Select fieldnames
From tables
WHERE (RIGHT(dbo.tblLDPStudentInfo.LDPStudentID, 4) in('+@StudentNumber+')) and (dbo.vw_LatestPerfGrading.PerfGradeCatID =5) and
(dbo.vw_LatestUpdateInfo.FinalUpdate =0) and (dbo.tblCountry.CountryID in('+@Country+'))'

It's giving me an "invalid column name" error when it gets to the @Country variable. The column exists in the select clause. When I comment out the (dbo.tblCountry.CountryID in('+@Country+'))' line, it works perfectly.

What am I doing wrong?

 

[philhege]

If Country is a character field, you have to inject the quotes around the value, so

(dbo.tblCountry.CountryID in('''+@Country+'''))

should work.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[gmmastros]

Also, you list 4 tables/views in your where clause, but a different table in your from.


dbo.tblLDPStudentInfo
dbo.vw_LatestPerfGrading
dbo.vw_LatestUpdateInfo
dbo.tblCountry

You need to list these tables and views in the from clause and join them on a common field.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChopinFan]

Works great! Thanks for your help!