CODEWITHSUNDEEP
sql-serversql-server-2005stored-procedures
test
test

Reputation: 2127

Stored Procedure Syntax

My stored procedure is called as below from an SQL instegartion package within SQL Server 2005

EXEC ? = Validation.PopulateFaultsFileDetails ? , 0

Though i'm not sure what the ? means

Upvotes: 1

Views: 3469

Answers (3)

test
test

Reputation: 2127

Thanks I appreciate the answer.

I was able to successfully execute the stored procedure using DECLARE @FaultsFileName varchar DECLARE @FaultsFileID int EXEC @FaultsFileID = Validation.PopulateFaultsFileDetails 'SameMonth Test.txt' , @FaultsFileID SELECT @FaultsFileID

But when I pass the input parameter as 'SameMonth Test.txt' in the Integration Package I get an error which says:

Parameter names cannot be a mixture of ordinal and named types.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Upvotes: 0

AJ.
AJ.

Reputation: 13761

When this SQL statment is called, both question marks (?) will be replaced. The first will be replaced by a variable which will receive the return value of the stored procedure. The second will be replaced by a value which will be passed into the stored procedure. The code to use this statement will look something like this (pseudocode): 

dim result
SQL = "EXEC ? = Validation.PopulateFaultsFileDetails ? , 0"
SQL.execute(result, 99)    // pass in 99 to the stored proc
debug.print result

This gives you 3 advantages:

  1. you can re-use the same bit of SQL with different values
  2. you can pick up the return value and test for success/error
  3. if the value you are passing in is a string, it should be correctly escaped for you, reducing the risk of SQL injection vulnerabilities in your app.

Upvotes: 1

Rob
Rob

Reputation: 45761

The ? stands fora variable, to be precise, a parameter. The first ? is the return value of the stored prcoedure and the second one is the first parameter of the stored procedure

Upvotes: 1

Related Questions