You can’t use a function directly as a stored procedure parameter. You can do the following: DECLARE @now DateTime SET @now = GETDATE() DECLARE @return_value int EXEC @return_value = my_stored_procedure @MyId = 1, @MyDateField = @now SELECT ‘Return Value’ = @return_value GO
You could do this, though probably not as slick as you’d like: declare type nt_type is table of number; nt nt_type := nt_type (1, 3, 5); begin for i in 1..nt.count loop dbms_output.put_line(nt(i)); end loop; end; If you create a type in the database: create type number_table is table of number; then you can do … Read more
Default values are only used if the arguments are not specified. In your case you did specify the arguments – both were supplied, with a value of NULL. (Yes, in this case NULL is considered a real value :-). Try: EXEC TEST() Share and enjoy. Addendum: The default values for procedure parameters are certainly buried … Read more
There are multiple ways to do this. You can use a sub query: SET @some_var = (SELECT COUNT(*) FROM mytable); (like your original, just add parenthesis around the query) or use the SELECT INTO syntax to assign multiple values: SELECT COUNT(*), MAX(col) INTO @some_var, @some_other_var FROM tab; The sub query syntax is slightly faster (I … Read more
It has to be a constant – the value has to be computable at the time that the procedure is created, and that one computation has to provide the value that will always be used. Look at the definition of sys.all_parameters: default_value sql_variant If has_default_value is 1, the value of this column is the value … Read more
The syntax is EXEC mySchema.myPackage.myProcedure@myRemoteDB( ‘someParameter’ );
Your second attempt is the right approach: GRANT EXECUTE ON PROCEDURE myDB.spName TO ‘TestUser’@’localhost’; but if that is not working, verify … a) you (the user from which you are running all these command) have grant rights [i.e WITH GRANT OPTION]. If you are root, then you have grant rights. b) the user exists to … Read more
There are two ways to solve this: set the connection’s noAccessToProcedureBodies=true property For example as part of the connection string: jdbc:mysql://ipaddress:3306/test?noAccessToProcedureBodies=true The JDBC driver will then create “INOUT” strings for the arguments without requiring meta data like the exception says. Grant SELECT privileges on mysql.proc to the database user For example in the mysql prompt: … Read more
To find out whether no matching rows exist you can use NOT EXISTS. Which can be more efficient than counting all matching rows IF NOT EXISTS(SELECT * FROM …) BEGIN PRINT ‘No matching row exists’ END