44

Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃. The problem you are having here is due entirely to the classification of the character(s) being used. The rules for regular (i.e. non-delimited) identifiers are: First letter must be: A letter as defined by the Unicode ...


35

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.


22

I don't think it's Unicode that's causing the problem; in the case of local variable or parameter names, it's that the character isn't a valid ASCII/Unicode 3.2 character (and there isn't any escaping sequence for variables/parameters like there are for other entity types). This batch works fine, it uses a Unicode character that simply doesn't violate the ...


19

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


18

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


15

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


15

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available. From the execution plan provided, this is exactly what happened in your case. When a ...


11

Pass the array as is using the VARIADIC key word in the call: CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[]) RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT json_extract_path_text(string::json, VARIADIC params)'; Call: SELECT json_extract_path_text('{"f1":{"f2":1,"f3":"foo"}}', VARIADIC '{f1, f3}'); ...


11

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here. CREATE OR REPLACE FUNCTION insert_records_for_notification( _username text , _state text , _district text , _bloodgroup text , _status text , _approverejectstatus text , _emailsubject text , ...


10

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. Check the parameter names.


10

The first argument to the system stored procedure sp_helptext is: [@objname= ] 'name' Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current ...


10

I strongly recommend always using the method that allows you to explicitly set the data type and precision. Yes, this is more work, both up front and if the data type in the database ever changes (which is common when, say, an IDENTITY column exceeds the range of int and it is upsized to bigint). cmd.parameters.Add("@VehicleId", SqlDbType.Int).Value = ...


9

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


9

I was told to implement as demonstrated below as this is how its been done before. ... this smells funny to me. You know what smells funnier than this approach? That line of "reasoning". The old "this is how it's always been done" is simply a means of avoiding thinking about it and discussing it. And that alone should raise a red flag, even if the code is ...


9

The length of @P1 is different in all of those. You're getting different plans because you're not explicitly setting parameter lengths in your code. Any difference in strings, no matter how minor, will generate a new plan. Data type sizes, spaces, comments, all of them will cause new entries in the plan cache. Here's a demo video illustrating the problem.


8

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 if AUTO_UPDATE_STATISTICS is on. Updated statistics only mean that individual statements in a batch plan need an optimality ...


8

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

SET PARSEONLY ON will do this. FMTONLY will generate an error. EG: USE [AdventureWorks] GO set parseonly on go exec [uspGetBillOfMaterials] go set parseonly off


6

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


6

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.


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

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


6

You can simplify the whole function and use an OUT parameter, so the value is returned at the end of the function automatically: CREATE OR REPLACE FUNCTION make_date_from_configuration( wdc_id uuid , from_date date , OUT ret_id int) AS $func$ BEGIN INSERT INTO configuration_dates ( weekly_date_configuration_id, "from", ...


6

You are right, the (@be int) shown applies to parameterized queries. Applications often parameterize the queries using sp_executesql, and then sent them to the sql server. The query will be cached as (variables)QueryText . And ofcourse, the values won't be cached in the text, since the query is parameterized. Parameterized query example Test Data ...


5

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


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

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


5

Community Wiki answer generated from a comment on the question by Martin Smith There is an active Connect item (submitted by Erland Sommarskog) for this: Relax restriction that table parameters must be readonly when SPs call each other The only response by Microsoft so far says (emphasis added): Thanks for the feedback on this. We have received similar ...


5

You're off to a good start with that test setup, but it is missing something that is causing you to misinterpret what is actually happening. If you put in PRINT statements at the beginning, middle, and end of the stored procedure then the additional output will make it clearer as to what is going on here. For example: USE [tempdb]; GO CREATE PROCEDURE ...


5

Does that conversion affect SQL Server performance in the case of an 'INT'? No. Some .NET data types map exactly to SQL Server data types, and int is one of them. And this method is not deprecated. See the docs here The issues caused by not specifying parameter types exactly are usually limited to bad plans. If you pass a parameter with a higher data ...


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