Stored Procedure Problem

Any Sql Server Gurus here today?

[Krapulator]

Hi,

Im a having a problem which I hope is just a small matter of syntax in a sql server 2000 stored procedure:

This is the procedure:

EXEC('SELECT P.sName AS sFile, MR.sResortName, MR.sTitle, MR.lPageId, MR.sShortDesc, MR.tDate, MR.sPageURL
FROM poMediaRelease MR, poMediaReleaseTarget MRT, poTarget T, spmlPage P, poDestination D
WHERE (MR.lMediaReleaseId = MRT.lMediaReleaseId) and (MR.lPageId = P.lPageId) and (MRT.lTargetId in (' + @sTargetId + ')) and (MR.tArchiveDate > getdate()) and (MRT.lTargetId = T.lTargetId) and (D.lId = ' + @vlResort + ')
ORDER BY MR.sTitle')

And the error on the page is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near ')'.

Thanks in advance!

[Slade]

that looks like a runtime error...

have you checked the variables that are being passed in?

the sproc should have been reasonably correct for SQL to allow you to save it(but not always!).

Try running it from query analyzer. Insert a 'select @varname' for each of your variables and look for the possibility of single or double quotes or something else in there that's messing it up.

Alternatively, instead of passing the string to EXEC, call it as just a SELECT statement. That way you can see the exact statement as it is being parsed by SQL.

Sorry, that's all I can offer you.

[duckhunter]

This may not be all of the problem but for one, the poDestination table is not joined to any other table.

Should poDestination join to poTarget on some ID?

[tomasz]

On your SQL looks if you use in it should use sub query
where table1.id in (select id from table2 where ..)

I think it is better to use parameter for security reasons

Create PROCEDURE sp_ExecuteSQL
(
@SQL as varchar(5000)
)

AS
execute(@SQL)
RETURN

and call this proc fom your code

'.Net example
Dim myConnection As SqlConnection = New SqlConnection("your conn string")
Dim myCommand As SqlCommand = New SqlCommand("sp_ExecuteSQL", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parameterSQL As SqlParameter = New SqlParameter("@SQL", SqlDbType.Varchar, 5000)
parameterSQL.Value = sSQL' pass your SQL statment
myCommand.Parameters.Add(parameterSQL)
myConnection.Open()

Dim MyReader As SqlDataReader = myCommand.ExecuteReader()
Do While (MyReader.Read())
'retreve your data
Loop
MyReader.Close()
myConnection.Close()

[Krapulator]

Plenty of food for thought there.

Many thanks for your help. Ill let ya know how it goes