Tag Info

Hot answers tagged

9

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.


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


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


5

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


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


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

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

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


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.


1

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


1

The normal way is to make sure that the various keywords from SQL aren't permitted in the string the user enters. However there are many cases to check. Quotations can be avoided by something like this: SELECT CHAR(97) + CHAR(100) + CHAR(109) + CHAR(105) + CHAR(110) Returns admin and the technique of adding characters together in that manner can be used ...


1

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


1

For an ad hoc batch, you need to type the date values and assign them to the variables: DECLARE @fromperiod varchar(8), @toperiod varchar(8); SELECT @fromperiod = '20130101', @toperiod = '20130131'; An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the ...


1

Just the cross join bit: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( SELECT CASE WHEN O.DATE < the_param - 21 DAYS THEN 1 WHEN O.DATE >= the_param - 21 DAYS AND O.DATE < the_param - 14 DAYS THEN 2 WHEN O.DATE >= the_param - 14 ...


1

Came across an interesting article this morning which adds the ever present "it depends" to the answer... [See this post]: http://erinstellato.com/2012/01/statistics-recompilations/ for details, it depends on settings for Auto Update Stats. If disabled apparently the answer is NO the query will not recompile.


1

The Filter condition is in Me.Filter: you use it above. You'd have to parse this, but you must have set it previously to be able to apply it. Ignoring SQL Injection risk (I just got this off the interwebs), you know in this snippet you are filtering on ProductCatID Me.Filter = "ProductCatID = """ & Me.cboShowCat & """" Me.FilterOn = True You can ...


1

Looking at your use case, it looks as if you want a stored procedure, so here's one I wrote for you: CREATE PROC the_script ( @param1 int = null, @param2 int = null ) AS BEGIN select col_1, col_2, etc from table where cond1 > @param1 and cond2 < @param2 END Now, being in isql, you can run it: exec ...


1

I don't think it can be done positionally from within isql. Using a shell script wrapper, you could do something like #!/bin/bash PARAM1=$1 PARAM2=$2 isql -u whoever -p whatever -s myserver << EOF select * from mytable where mycolumn > ${PARAM1} and mycolumn < ${PARAM2} go EOF



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