Hot answers tagged

20

No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.


12

The explanation seems to tied to a combination of: a detail from the linked blog that was not mentioned in this question, the pragmatics of TVPs fitting within how parameters have always been passed in and out, and the nature of table variables. The missing detail contained in the linked blog post is exactly how variables are passed in and out of Stored ...


11

The parameters supplied to a procedure must be constants or variables. A function or the output of a function or an expression cannot be used. You would have to first set a variable with the required expression: declare @investigation varchar(100); set @investigation = 'Investigation is pending for ['+ @investigationidout +'] '; EXEC ...


9

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION ...


7

Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan. This may or may not be a good thing, but it is what happens. For instance, say you have a table of fruit (100 rows). There are 98 rows that are Apple, and only 2 rows that contain the fruit ...


7

Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects. This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from ...


6

...but executed quickly when run from SSMS (took 5 seconds) Rather annoyingly, the SSMS default is for SET ARITHABORT ON whereas the majority of client libraries (ADO .Net, ODBC, OLE DB) specify SET ARITHABORT OFF. Likely you had a plan "go bad" but when you attempted to replicate via SSMS, the difference in ARITHABORT resulted in a different plan being ...


6

No, there's really no shortcuts here. Examine things in the following order: Check the procedure name. Check the number of parameters. Check the types of the parameters.


6

You simply don't write it in such a way that a user can pass in a structured WHERE clause. This is a recipe for disaster, and I bet at least half of the companies who have been exploited by SQL injection thought they were protected by checking the input for keywords, stripping out semi-colons and comments, etc. They weren't, and there will always be ways ...


5

The SQL Server Customer Advisory Team wrote a blog post that has some information on this setting here. The –E startup flag The SQL Server startup flag –E forces SQL Server to allocate 4 extents at a time to each file, essentially quadrupling the stripe size. In heavy insert scenarios, this drives larger block sizes to the disk. Also, your pages ...


5

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future. One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage. Another point of suffering could be high compilations/second - so in an ...


4

You'd need to add IS_SPECIFIED (or ISSPECIFIED) to the where clause, as some hidden parameters may be set by... other things. A parameter can be removed from an spfile by issuing: ALTER SYSTEM RESET "_some_hidden_parameter" scope = spfile; You will likely have to stop and start the instance(s) to have the changes take effect.


4

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL. An IF statement in one stored proc would be enough too


4

The answer is close to your "stats updates automatically cause dependent query plans to be flushed". They don't "stick around" Flushing a plan from cache is determined by memory pressure. Statistics updates cause plan recompilations See MSDN, "Execution Plan Caching and Reuse" Now, it's unclear what your problem is, but do you have parameter sniffing ...


4

He seems confused. Using the same value from a local variable should be no different to using it from an input parameter. Is your colleague getting the wrong end of the stick with regard to using parametrised queries (sometimes called prepared statements) instead of full ad-hoc SQL? For example: resultSet = dbconnector.getRS('EXEC sp_foo_GET ...


3

Based on a simple test case I just wrote: @Test public void test() throws SQLException { PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); ps.setString(1, "someuser"); ps.executeUpdate(); } I think the error you refer to is probably: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 10 at ...


3

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188 1 (highest) Explicitly set in SQL functions 2 Set by an ALTER SESSION statement 3 Set as an environment variable 4 Specified in the initialization parameter file 5 Default ...


3

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind. You can say: EXEC dbo.myprocedure @foo = 'bar'; But you can't say: EXEC dbo.myprocedure @foo = 'b' + 'ar'; You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a ...


3

But why the recompile didn't help? Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits. ...


3

You should be able to do that using the system view INFORMATION_SCHEMA.PARAMETERS. You'll have there what you need. It "returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information." PS: if the ...


3

For input and output parameters, you can look at sys.parameters, sys.procedures and sys.types. Here is a procedure with various input/output parameters, alias types and even a TVP (2008+ only): USE [tempdb]; GO CREATE TYPE dbo.TVPType AS TABLE(id INT); GO CREATE PROCEDURE dbo.foobar @a INT, @b SYSNAME = N'foo', @c DATETIME = NULL, @d ...


3

To elaborate on gbn's answer, two sprocs is the better way to go here; additionally, I'd go one step further and place the query (sans top clause) in a table-valued function and then have the sprocs be nothing more than a shell which calls the TVF and applies the top clause appropriately (by number of rows or percent). The only caveat here being that if you ...


3

You can't. The return datatype must be specified in a function. It is not an optional part of the grammar. You can return sql_variant though.


3

Yes it's the average size of each row in your table. row size * N rows = table size Row size = 83 B Rows = 22,000,001 Row Size times Number of rows: 83 * 22000001 = 1826000083 Convert result in Giga Bytes: 1826000083 / 1024 / 1024 / 1024 = 1.7 Total = 1.7 GiB Max


3

This ended up being related to parameter sniffing. It just so happened that some oddly formed versions of this query were being executed RIGHT AFTER the stats were rebuilt. So the cached plan was not representative of the majority of the calls. I used the trick of copying the date parameters to local variables and this is working just fine, with little to no ...


2

I did a little bit more research and found the answer to the problem. Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text'). The actual parameter names for stored procedures can ...


2

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters: DECLARE @someParam INT = 5; EXEC LinkedServerName.master.sys.sp_executesql @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;', @params = N'@i INT', @i = @someParam;


2

You could create a function that executes SET ROLE with dynamic SQL, using format to safely insert the role identifier (%I inserts an identifier, placing double quotes around it if necessary, and escaping double quotes in the string by doubling them up if necessary). Something along the lines of CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN ...


2

No, it can't be queried. These values are stored in sqlnet.ora on either or both of the client and the server. In either case, none of the contents of the network configuration files sqlnet.ora, tnsnames.ora, listener.ora etc (eg protocol.ora, snmp.ora, cman.ora) can be queried. If you really need to read the contents of these files and can't solve this ...


2

If you really can limit to alphanumeric characters, then yes, that's fine, IF you are limiting to ANSI alphanumeric characters. In Unicode, because every character is more than one byte, many representations of alphanumeric characters are actually unsafe and could lead to injection. You will need to sanitize the data server-side and make sure the encoding ...



Only top voted, non community-wiki answers of a minimum length are eligible