Hot answers tagged

10

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


9

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


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


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


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?


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


2

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


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



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