Tag Info

Hot answers tagged

16

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.


8

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 ...


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 ...


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

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.


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

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


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 ...


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

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

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

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

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.


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

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

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. ...


2

Create a pfile from the spfile: CREATE PFILE FROM SPFILE; Edit the generated pfile and remove the parameters, then recreate the spfile from the edited pfile. Bounce the database & all should be well. The database might need to be down when you recreate the spfile from the pfile.


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

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

Figured this out, it's easy, you just add :paramName in your query and it automatically asks your for the parameters when you run the query.


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

Either way, that's totally possible, given that all your parameters are of the same data type. EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts. create or replace function test_function(_filter1 text = null , _filter2 text = null ...


2

It's not supported, but you can get around it.. select * from openquery( LinkedServerName, 'exec [database].dbo.sproc @Id=@Id, @PItems=@PItems output'','' @PId bigint, @PItems xml output'', valueOf@PId, valueOf@PItems output' ) There ...


2

It's not possible to bind column names to variables, but you can achieve similar results by using dynamic SQL. DECLARE @datetxt varchar(10) = '2015-01-01' DECLARE @sqltxt nvarchar(1000); SET @sqltxt = N'SELECT dateAdd(second, 1, ' + @datetxt + ') AS StartTime FROM Termination_Call_Detail' Exec (@sqltxt)


2

If I'm understanding you correctly what you're looking to do should be quite simple. I'm not entirely sure how you're setting @date1 but for this to work you will need to set that as a SSIS variable and for this example I'll assum it's a date. Then you need to create your query as a variable and then set it as an expression like so. Then you set your ...



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