New answers tagged

1

According with the Mysql documentation https://dev.mysql.com/doc/refman/5.6/en/handler-scope.html you can create a handler with two handlers and the same scope, be aware of the precedence Example CREATE PROCEDURE p1() BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SELECT 'SQLSTATE handler was activated' AS msg; DECLARE CONTINUE HANDLER FOR ...


0

SELECT g(f(date)) AS 'week of', AVG(...) AS 'moving average' FROM tbl WHERE date > CURDATE() - INTERVAL 52 WEEK GROUP BY f(date) Where f() is a function that turns a date (or datetime or timestamp) into a number. Something like FLOOR((TO_DAYS(date) - 3) / 7), where the 3 is chosen based on what day a week "starts" on. (I doubt if ...


8

What are the best or most helpful Stored Procedures for a New DBA to have in their tool kit, for SQL 2012/2014? You are asking Tools that are most useful to DBA In no particuliar order ... Brent Ozar Crew has sp_Blitz®, sp_BlitzCache®, sp_BlitzIndex®, sp_AskBrent®, sp_BlitzRS™, sp_BlitzTrace™ - This helps to do health check for sql servers. To ...


8

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are: Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be ...


5

Sounds like you are looking for the IDENTITY() function: Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. USE [MYDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spSchedula_Scadenzario] as begin drop table MYDB.dbo.tmpTable select -- Create new ...


0

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). ...


0

You have three cursors here, but one is implicit in the for loop. Your code indicates that for each row in the testnirmal table you want a column name from the table and then a row from the testnirmal table that has a literal value of that column name. It is unclear what you are trying to accomplish, but here is one interpretation: CREATE OR REPLACE ...


2

This solution would replace the need for having 100 + procs doing the same thing. you have a proc and a function. The function splits all your medical codes from a string to a table which can be use in a CROSS APPLY in the new proc. This way you only have to call the one proc. Of course you'd have to update all the code calling the other procs to use ...


9

This should work for you: CREATE VIEW MyView AS SELECT <colList> FROM <TableOne> INNER JOIN <TableTwo> ON ... AND ..... AND ..... LEFT JOIN <TableThree> ON ... AND ..... AND ..... WHERE ..... AND ..... AND ..... Then replace in Procs with: ... FROM MyView WHERE MedicalPlanCode IN ('abc', 'def', 'ghi')


0

Adam Machanic sent me this in email DECLARE @lineno INT BEGIN TRY SET @lineno = 1/0 END TRY BEGIN CATCH SET @lineno = ERROR_LINE() END CATCH It does qualify as a one line in my book.


1

Create a second procedure that uses two nested cursors. Cursors in stored procedures allow you to do a very non-SQL-like thing: iterate through a result set one row at a time, putting the selected column values into variables and doing things with them. They are easily misused, since SQL, being declarative rather than procedural, should usually not need ...


1

So what you want is an insert, an update and a delete procedure for each table. Then you want a "wrapper" procedure that tracks the inserts/updates/deletes and responds accordingly to errors. A "transaction" would be the batch and "rollbacks" would be explicit. I put these in quotes because you don't want to encompass all of the transactions into a ...


1

Import-Module SQLPS $ErrorActionPreference = "Stop" Set-StrictMode -Version Latest $serverName = "VM-WIN81" $instanceName = "SQL2014" # or DEFAULT $jobName = "syspolicy_purge_history" $jobSuffix = "_Old" $script = Get-ChildItem SQLSERVER:\SQL\$serverName\$instanceName\JobServer\Jobs | Where { $_.Name -eq $jobName } | %{ $_.Script() } $script = ...


1

It would be a large script as a Job consists of data entries in multiple tables/views: msdb.dbo.sysjobs_view msdb.dbo.sysjobsteps msdb.dbo.sysjobschedules -- you probably dont need these as you are copying to the same server msdb.dbo.sysschedules msdb.dbo.syscategories sys.server_principals msdb.dbo.sysOperators Create Jobs using following SPs: ...


0

So you want to concatenate multiple rows and columns into a single string, just to parse it later. Overhead 1. Next, you want to write PL/SQL for something that can be easily done in plain SQL. Overhead 2. Something like this (I just used random views for the 7 data source): select listagg(x, '|') within group (order by null) from ( select 'COL11' || ...


0

Why write a loop? Skip the count and the loop, simply perform this once: SET @sql1=CONCAT("update ",tab1," set ",col1,"='565656'");


0

I was not able to solve this issue in the method I had wanted. I used an if statement as suggested by Serg. if ((:NEW.ONSALE = 'T') AND (:OLD.ONSALE = 'F')) then :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT1; elsif ((:NEW.ONSALE = 'T') and (:OLD.ONSALE = 'T')) then :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT2; end if; This was ...


0

Most likely the issue is with the first dynamic query, the one you are storing into @sql0: set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1); You are concatenating the value of @i into the query, but @i has not been defined yet at this point – so it is null. Concatenating a null with other string literals gives you a null as well. So @sql0 ends ...


0

Please tried this. to solve Concatenate, PREPARE and EXECUTE statements as bellow.. CREATE DEFINER=`products`@`localhost` PROCEDURE `generateMeritList`( IN `mastercategory_id` INT(11), IN `masterschools_id` INT(11) ) NO SQL begin declare total int default 0; declare conditions varchar(255) default ''; declare finalQuery varchar(60000) ...


0

I think you want to change your table structure. Discounts and items are related but are not always the same. Today you discount T-Shirts by 15% tomorrow you realize you have way too many and want to change that to 20%. Sure you can update the table but then you loose all records of what the discount was. When a customer calls to say they only got a 15% ...


0

Short Answer: Try changing the default database of the dev user to their particular database. In the Query window within SS Studio, make sure they're using the right database context within the Query window via the database drop-down list or T-SQL USE [DBNAME]. Long Answer: I cannot speak for every circumstance but I know a case where this definitely ...


2

Interesting one - difficult to pin this one down. Have you thought about looking at the public role? sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s' Does that bring you back anything?


4

Amazingly broad question but a couple of simple answers. Indexing like you suggested. Make sure the appropriate indexes exist and if the need is great enough make sure they are all covering. Get rid of the *. Specify the columns you need. If you are pulling across 100 columns over a million rows that's going to be a LOT of data. If you only need 3 ...


0

You don't need a return statement, because it is using OPEN and FOR... It is the same like RETURN... The error is in the name of the table, you put it in "" double quotes. Remove that, use simply the name of the table, and use the type SYS_REFCURSOR like this: CREATE OR REPLACE PROCEDURE ProcSelectEveryThing(cursor_ OUT SYS_REFCURSOR) AS BEGIN OPEN ...


0

One of the "select into" statements is returning more than one result. Those statements can only return a single row.


3

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


2

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


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.


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.


0

When declaring HEX strings such as UUIDs, or when declaring other strings with limited character set needs, such as country_code, postal_code, zip_code, etc, declare them to be CHARACTER SET ascii. It can be applied just to the column, letting the rest of your columns be utf8mb4 or whatever. And be consistent between tables so that JOIN works efficient. ...


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


0

Here is my thought: Create a table with all the fields that exist in the csv file. Create a trigger (After insert on the table created in step 1) that calls your procedure that you already have, or let the trigger itself insert new values to the corresponding tables. Load the CSV file to the table you created in step 1 The other tables will be populated ...


1

Unlike sysadmin which bypasses checks, the built in database roles aren't so special that they can't be overridden with a DENY. Try looking at Exec sp_helpprotect Null, 'Username' and seeing what DENY records show up.


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


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



Top 50 recent answers are included