The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in … Read more
SqlParameters[1] = new SqlParameter(“Date1”, SqlDbType.SqlDateTime); SqlParameters[1].Value = DBNull.Value; SqlParameters[1].Direction = ParameterDirection.Input; …then copy for the second.
@michal kralik – unfortunately there’s a bug with the MySQL C API that PDO uses which means that running your code as above with some versions of MySQL results in the error: “Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable”. You can … Read more
Advanced features like VARIADIC or even polymorphic input types and dynamic SQL are very powerful. The last chapter in this answer provides an advanced example: Refactor a PL/pgSQL function to return the output of various SELECT queries For a simple case like yours, you can just use default values for function parameters. It all depends … Read more
Simplest way to distinguished between your parameter and column (if both name is same) is to add table name in your column name. UPDATE customers SET customers.Name = Name; Even you can also add database prefix like UPDATE yourdb.customers SET yourdb.customers.Name = Name; By adding database name you can perform action on more than 1 … Read more
The user contributions on the php.net have a write up on how to execute a stored procedure using the sqlsrv-prepare. In case that is removed from the php.net user contributions in the future here is what it had(has) listed: $procedure_params = array( array(&$myparams[‘Item_ID’], SQLSRV_PARAM_OUT), array(&$myparams[‘Item_Name’], SQLSRV_PARAM_OUT) ); // EXEC the procedure, {call stp_Create_Item (@Item_ID = … Read more
You can do this in sql only, like this: SELECT * FROM tUsers WHERE 1 = 1 AND (@userID IS NULL OR RTRIM(Name) = @userID ) AND (@password IS NULL OR RTRIM(Password) = @password) AND (@field2 IS NULL OR Field2 = @field2) …. If any parameter passed to the stored procedure with a NULL value … Read more
Add a parameter, using ParameterDirection.ReturnValue. The return value will be present in the paramter after the execution.
Apparently CREATE LOGIN only accepts literals. You could try wrapping it in an exec and building it as a string: EXEC(‘CREATE LOGIN ‘ + quotename(@username) + ‘ WITH PASSWORD = ‘ + quotename(@password, ””)) edit: added quotename for safety from sql injection attacks
Bozho’s 2nd revised answer was close but not quite there. It did lead me to the answer though. Taking the code example I started with we end up with: CallableStatement proc = connection.prepareCall(“{ ? = call dbo.mySproc() }”); proc.registerOutParameter(1, Types.INTEGER); proc.execute(); int returnValue = proc.getInt(1); The key pieces here are the “? =” in front … Read more