2

I want to author some .NET code (preferably C#) that determines the number of expected parameters in an arbitrary SQL statement. For example, this statement

EXEC etlp.LogPackageEnd ?,?,?

expects 3 parameters.

As a SQL novice, I spent time reading other posts on how to solve this problem. I've already tried using the TSql100Parser.GetTokenStream, as suggested here, but it returned parse errors for each question mark. I also tried using Microsoft.SqlServer.Management.SqlParser.Parser.Parser.Parse() but got an error of Incorrect syntax near ?

I realize I could use a regex but I'm looking for a generic solution; my understanding is that the parameter format can change so question marks aren't the indicators.

Community
  • 1
  • 1
Craig
  • 1,890
  • 1
  • 26
  • 44
  • Are you trying to find the number of arguments to a stored procedure? – Gordon Linoff Jul 10 '12 at 21:26
  • Not just stored procedures. I believe question marks can be used as placeholders for parameters in OLE-DB SQL statements. Additionally, question marks may appear in a statement like SELECT * FROM Table1 WHERE Column1 = ? I'd like to be handle these types of statements, as well as when executing a stored procedure. – Craig Jul 10 '12 at 22:31
  • the OleDbCommandBuilder provides a DeriveParameters method when the command type is a StoredProcedure, but MSDN docs specifically state you can't use that method against a parameterized query :( – David W Jul 18 '12 at 00:23

1 Answers1

0
SELECT name
FROM sys.parameters
WHERE object_id = OBJECT_ID('YourProcedureName')
Arun Peddakotla
  • 163
  • 8