Hot answers tagged

6

No, the SPID (Server Process ID) is assigned the moment an application establishes a connection/session to the database and is retained until the connection/session ends.


5

<TL;DR> Is overloading supported in SQL Server: Nope. But even if it was, you would likely have issues related to source control and/or debugging. Can a subset of parameters be passed in, altering the behavior: Yep, but you will have a single piece of code that won't be easy to work with or maintain. Conclusion: Just create one Stored Procedure or ...


4

You like need to wrap that code in CREATE PROCEDURE ... syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION. GO CREATE PROCEDURE dbo.AssignUserToTicket ( @updateAuthor varchar(100) , @assignedUser varchar(100) , @ticketID bigint ) AS BEGIN BEGIN TRANSACTION; SAVE TRANSACTION MySavePoint; SET ...


4

Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, for example won't return a valid result). Specifically for T-SQL or batch related items there is a different DMV and accompanied system stored procedure. The ...


4

Many of the issues you see are being caused by an inefficient execution plan: Not that the supplied plan and query matches the question, but even so, I'm working with what was provided. Anyway, you should implement the Name column data type changes (from nvarchar(max)) that I mentioned in your previous question. More importantly, you need to add the ...


4

This is untested. Let me know if it works or not. The grammar for exec accepts a @module_name_var which allows the module name to be in a variable. I'm not sure if it accepts three part names or not. If it does then hopefully the below will do the trick. DECLARE @DbName sysname = 'AdventureWorks2012'; DECLARE @module_name_var nvarchar(500) = ...


4

The point of "default" values is that they are the default values. You Don't need to list them in the insert query at all. They'll be inserted automatically. To pass CURRENT_TIMESTAMP as a parameter, you probably need to use the equivalent pseudo-function, CURRENT_TIMESTAMP()... which is the same as NOW() Stop and consider DEFAULT CURRENT_TIMESTAMP ON ...


3

If you want to properly handle nested Stored Procedures that can handle Transactions (whether started from T-SQL or app code) then you should follow the template that I described in the following answer: Are we required to handle Transaction in C# Code as well as in Store procedure You will notice two differences there from what you are attempting here: ...


3

Well, you can kind of do this with numbered procedures, but you do have to change the calling code a little bit. CREATE PROCEDURE dbo.foo;1 @a INT AS SELECT @a; GO CREATE PROCEDURE dbo.foo;2 @a INT, @b INT AS SELECT @a + @b; GO EXEC dbo.foo @a = 1; EXEC dbo.foo;2 @a = 1, @b = 2; ------------^^ have to specify any overload other than ;1 However, ...


3

As other answers have noted, overloading is not supported for SQL Server stored procedures. One workaround could be having your stored procedure accept an xml variable as parameter, so your application could send a "set" of arguments in an xml blob: CREATE PROCEDURE dbo.someproc @params xml AS SELECT tbl.n.value('@value', 'varchar(max)') AS val ...


3

The ability (deprecated) to group Stored Procedures seems to exist for a single (and rather silly) purpose: the ability to mass delete via a single DROP statement. According to the SQL Server 2000 MSDN documentation for Creating a Stored Procedure: Grouping A procedure can be created with the same name as an existing stored procedure if it is given a ...


2

use Approach #1: according to http://www.brainbell.com/tutorials/MySQL/Working_With_Cursors.htm If you do not explicitly close a cursor, MySQL will close it automatically when the END statement is reached.


2

You may want to look at this SO question I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one? which is asking essentially the same thing, with several answers. To build upon the script Alaa Awad posted... this should show the schema and database of the referenced and referencing objects. If you're using many temp tables via ...


2

If you use Openquery to insert the results of the stored procedure into a temp table, you can query the columns for the temp table. Note, you cannot use a @variable in Openquery, so you will need to run it through dynamic sql and an exec. The problem with that is now the temp table doesn't exist outside the dynamic query. But, if you put your temp table ...


2

You should be able to use the new system stored procedure sp_describe_first_result_set for that - see the MSDN docs for details: EXEC sp_describe_first_result_set N'YourStoredProcedureNameHere'


2

You could simply create a Group wihtin the registered servers pane in SSMS and add all servers to that group. Then you are able to run a query against the whole group. Just run the Create procedure (or whatever you need) there and it will be executed on all servers in the group (that could have connected with success). Be aware that select queries ran ...


2

You can use variables: delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT @fn:=`first_name`, @ln:=`last_name`, ...


2

For the 'good' plan, all the table variable cardinality estimates are 1 row. This is the most common outcome when using table variables, unless trace flag 2453 is enabled, or a statement-level recompilation occurs (for example because OPTION (RECOMPILE) is used, or one of the regular tables in the query has passed its recompilation threshold. For the 'bad' ...


1

By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature. Definer and invoker security contexts differ as follows: A stored program or view that executes in definer security context executes with the privileges of the account named ...


1

Simply make these changes: call mysql.insertTables (`Debbie`, `Hopkins`, NULL, NULL, NULL, -- Changed 1, 123456789, 0, NULL, NULL, NULL, 12211985, 12345678, 1) NULL tells the INSERT to invoke the DEFAULTs.


1

The keyword DEFAULT is context sensitive within an INSERT statement only (some systems may support it as well within UPDATE). You can't use DEFAULT within the context of calling a stored procedure. Also, MySQL does not yet support default values for stored procedure parameters either. An alternative can be: delimiter // CREATE PROCEDURE mysql.insertTables ...


1

Yes, we did this type of thing at a company I worked at a few years ago. Even though we did it only for error logging, it was the same general concept. You just need to write the SELECT statement that includes all of the input parameters, use FOR XML, and store that in a variable that can be inserted into your Execution table in a new field: Parameters XML ...


1

ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user. If you want to select constraints of a table which belongs to another schema then you should have select privilege(even if you have dba role assigned to the user) on that table. ...When I copy the whole insert query and run it (with the same user) from sql ...



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