...select count...
SELECT COUNT (in_product_id) FROM order_item WHERE in_product_id = 926528
...like...
SELECT * FROM catalog WHERE vc_catalog_name like '%persol%'
...left join...
SELECT a.in_catalog_id, b.vc_product_name AS vc_something_else, b.in_product_id
FROM catalog_product a
LEFT JOIN product b
ON a.in_product_id = b.in_product_id
...execute a stored procedure...
EXECUTE dbo.list_of_names_sp_report @in_condition = 1, @in_something_else = 2
...create a stored procedure... (CREATE becomes ALTER to update a stored procedure)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[list_of_names_sp_report]
(@in_condition tinyint)
AS
IF(@in_condition = 1)
BEGIN
SELECT * FROM dbo.list_of_names
ORDER BY vc_name DESC
END
...using variables in stored procedures...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[some_table_sp_updates]
(@in_condition tinyint,
@in_id int = 0,
@vc_whatever bit = 0,
@vc_some_info varchar(200) = '',
@vc_description varchar(200) = '',
@vc_thingy varchar(200) = '',
@vc_hidden_concern varchar(3000) = '',
@vc_another_variable_for_something varchar(1000) = '',
@in_number_of_toes int = 0)
AS
DECLARE @dt_when_was_i_last_touched datetime
SET @dt_when_was_i_last_touched = GETDATE()
IF(@in_condition = 1) -- This is a note
BEGIN
UPDATE some_table
SET vc_whatever = @vc_whatever,
vc_some_info = @vc_some_info,
vc_description = @vc_description,
vc_thingy = @vc_thingy,
vc_hidden_concern = @vc_hidden_concern,
vc_another_variable_for_something = @vc_another_variable_for_something,
in_number_of_toes = @in_number_of_toes,
dt_when_was_i_last_touched = @dt_when_was_i_last_touched
WHERE in_id = @in_id
END
...create a table with an ever unique id...
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
CREATE TABLE dbo.mytable
(
myid int NOT NULL IDENTITY (1, 1),
mycopy varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.mytable ADD CONSTRAINT
PK_mytable PRIMARY KEY CLUSTERED
(
myid
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ORMs prefer Guids for unique keys. Outside of ORMing, expect auto-incrementing integers.
...create a view...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[cmn_vw_list_of_names]
AS
SELECT vc_name FROM dbo.list_of_names
WHERE in_abc = 123
GO
...create a function...
USE [MyDatabase]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FlaggedPerson]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FlaggedPerson]
GO
CREATE FUNCTION [dbo].[FlaggedPerson] (@id INT, @altid INT)
RETURNS
@FlaggedTable TABLE
(
NameOfFlaggedPerson VARCHAR(100)
)
AS
BEGIN
DECLARE @Response VARCHAR(100)
SET @Response = 'No'
IF EXISTS(SELECT * FROM [SomeOtherTable] WHERE in_alt_id = @altid AND in_id <> @id AND in_third_id > 0)
BEGIN
SET @Response =
ISNULL(
(SELECT TOP 1
b.vc_first_name + ' ' + b.vc_last_name
FROM
dbo.[SomeOtherTable] a
LEFT JOIN
dbo.other_table_to_use b
ON a.in_third_id = b.in_other_table_to_use_id
WHERE
a.in_alt_id = @altid
AND
b.bt_active = 1
AND
a.in_id <> @id
AND
ISNULL(b.vc_first_name + ' ' + b.vc_last_name, 'x') <> 'x'
ORDER BY
a.dt_order_by_me ),'')
END
INSERT INTO @FlaggedTable (NameOfFlaggedPerson)
VALUES (@Response)
RETURN
END
GO
...access the function in a stored procedure, inside a join, like this...
OUTER APPLY dbo.FlaggedPerson(a.in_id, a.in_alt_id) h
...after having first having defined h like this...
h.[NameOfFlaggedPerson],
...parsing a string of comma seperated integers...
DECLARE @bigString VARCHAR(MAX), @id1 INT, @id2 INT
SET @bigString = @some_other_variable_set_beforehand
IF RIGHT(RTRIM(@bigstring),1) <> ','
BEGIN
SET @bigstring = @bigstring + ','
END
DECLARE @commaPt INT
SET @commaPt = CHARINDEX(',',@bigstring)
IF @commaPt > 1
BEGIN
SET @id1 = LEFT(@bigstring,@commaPt -1)
SET @bigString = RIGHT(@bigstring,LEN(@bigstring) - LEN(@id1) - 1)
WHILE LEN(@bigString) > 0
BEGIN
SET @commaPt = CHARINDEX(',',@bigstring)
SET @id2 = left(@bigstring,@commaPt - 1)
UPDATE
a
SET
in_unique_id = @id1
FROM
dbo.[some_table] a
WHERE
in_other_id = @id2
AND EXISTS(SELECT * FROM dbo.some_other_table WHERE in_third_id = @id1)
IF LEN(@bigstring) > LEN(@id2)
BEGIN
SET @bigString = RIGHT(@bigstring,LEN(@bigstring) - LEN(@id2) - 1)
END
ELSE
BEGIN
SET @bigString = RIGHT(@bigstring,LEN(@bigstring) - LEN(@id2))
END
END
END
END
GO
...update a table...
UPDATE myTable SET vc_greeting = 'hi' WHERE in_id = 123 OR in_id = 456
...where one of many columns contains a value...
SELECT TOP 500 in_id, vc_address, vc_name, vc_url FROM dbo.some_table WHERE CONTAINS((vc_address, vc_name, vc_url), 'match on me') AND vc_something_else = 'yay' ORDER BY vc_url desc, vc_address, vc_name
No comments:
Post a Comment