Use database inside a stored procedure
Dynamic SQL CREATE PROCEDURE spTestProc AS EXEC (‘USE [database1]; CREATE USER [userLogin] FOR LOGIN [userLogin]’) EXEC (‘USE [database2]; CREATE USER [userLogin] FOR LOGIN [userLogin]’) GO
tsql
How do I declare and use variables in PL/SQL like I do in T-SQL?
Revised Answer If you’re not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables: var myname varchar2(20); exec :myname := ‘Tom’; SELECT * FROM Customers WHERE Name = :myname; In many tools (such as Toad and SQL Developer), omitting the var and exec … Read more
When would you use a table-valued function? [closed]
Table-valued functions are “just” parameterized views. This makes them extremely powerful for encapsulating logic that would otherwise be hidden behind an opaque stored procedure. Here’s an example: Inline Table-valued Function: create function dbo.GetClients ( @clientName nvarchar(max) = null ) returns table return ( select * from dbo.Clients as a where ((a.ClientName = @clientName) or a.ClientName … Read more
What happens when you forget to close and deallocate cursor?
It depends on whether you declared the cursor locally or globally (and what the default is in your environment – default is global but you can change it). If the cursor is global, then it can stay “alive” in SQL Server until the last piece of code is touched in the scope in which it … Read more
How to create a pivot query in sql server without aggregate function
SELECT * FROM ( SELECT [Period], [Account], [Value] FROM TableName ) AS source PIVOT ( MAX([Value]) FOR [Period] IN ([2000], [2001], [2002]) ) as pvt Another way, SELECT ACCOUNT, MAX(CASE WHEN Period = ‘2000’ THEN Value ELSE NULL END) [2000], MAX(CASE WHEN Period = ‘2001’ THEN Value ELSE NULL END) [2001], MAX(CASE WHEN Period = … Read more
What is the optimal way to compare dates in Microsoft SQL server?
Converting to a DATE or using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: What is the most efficient way to trim time from datetime? Posted by Aaron Bertrand From that article: DECLARE … Read more
Create view with primary key?
You cannot create a primary key on a view. In SQL Server you can create an index on a view but that is different to creating a primary key. If you give us more information as to why you want a key on your view, perhaps we can help with that.
How to get Return Value of a Stored Procedure
Add a parameter, using ParameterDirection.ReturnValue. The return value will be present in the paramter after the execution.
T-SQL Conditional WHERE Clause
I changed the query to use EXISTS because if there’s more than one location associated with a POST, there’d be duplicate POST records that’d require a DISTINCT or GROUP BY clause to get rid of… The non-sargable This will perform the worst of the possible solutions: SELECT p.* FROM POSTS p WHERE EXISTS(SELECT NULL FROM … Read more
Return sql rows where field contains ONLY non-alphanumeric characters
SQL Server doesn’t have regular expressions. It uses the LIKE pattern matching syntax which isn’t the same. As it happens, you are close. Just need leading+trailing wildcards and move the NOT WHERE whatever NOT LIKE ‘%[a-z0-9]%’