Stored Procedure Syntax

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.