Medium Difficulty SQL statements (stored procedures, pagination, splicing fields, cursors)

First, create a stored procedure

If Exists (select name from sysobjects where name = ' Sp1loginuser ' and type= ' P ')
drop procedure Sp1loginuser
GO
CREATE PROCEDURE [dbo]. [Sp1loginuser]
--Add The parameters for the stored procedure here
@username NVARCHAR (50)
As
BEGIN
DECLARE @identityCount INT
SELECT * FROM dbo. Loginuser where isdelete=0
and [email protected]
SELECT TOP 1 @identityCount = ID from dbo. Loginuser ORDER by ID DESC
Select @identityCount
END
GO

Ii. execution of stored procedures

EXEC sp1loginuser ' admin '

Third, create the function

1.
ALTER FUNCTION [dbo]. [Fn1getrolenane] (
@userid INT
)
Returns varchar (500)
As
Begin
DECLARE @tmp VARCHAR (500)
SELECT @tmp =isnull (@tmp + ', ', ') +ltrim (r.rolename) from Userandrole ar, Role R WHERE R.roleid=ar.roleid and [email protect Ed
RETURN ISNULL (@tmp, ")
END

2.

SELECT * from Split (@strwhere, ', '))
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS on
GO
CREATE FUNCTION [dbo]. [Split]
(
@c VARCHAR (MAX),
@split VARCHAR (50)
)

RETURNS @t TABLE (Col VARCHAR (50))
As
BEGIN
while (CHARINDEX (@split, @c) <> 0)
BEGIN
INSERT @t (COL)
VALUES (SUBSTRING (@c, 1, CHARINDEX (@split, @c)-1))
SET @c = STUFF (@c, 1, CHARINDEX (@split, @c), ")
END
INSERT @t (col) VALUES (@c)
RETURN
END
GO

Iv. SQL Server Date field type go to string
(feetime,120) Username, convert the Date Type field to a string of the specified length

V. Create a paging stored procedure
Create PROCEDURE Usp_paginglarge
@TableNames VARCHAR,--table name, which can be multiple tables, but cannot be aliased
@PrimaryKey VARCHAR,--Primary key, can be empty, but the value cannot be empty when @order is empty
@Fields VARCHAR,--The field to be fetched, which can be a field of more than one table, can be empty, and is an empty representation select*
@PageSize INT,--number of records per page
@CurrentPage INT,--Current page, representing page
@Filter VARCHAR = ',--condition, can be empty, do not fill in where
@Group VARCHAR = ",--group BY, can be empty, do not fill groupby
@Order VARCHAR (200) = '--sort, can be empty, default by primary key in ascending order without filling in order
As
BEGIN
DECLARE @SortColumn VARCHAR (200)
DECLARE @Operator CHAR (2)
DECLARE @SortTable VARCHAR (200)
DECLARE @SortName VARCHAR (200)
IF @Fields = ' '
SET @Fields = ' * '
IF @Filter = ' '
SET @Filter = ' where1=1 '
ELSE
SET @Filter = ' Where ' [email protected]
IF @Group <> '
SET @Group = ' GROUPBY ' [email protected]

IF @Order <> '
BEGIN
DECLARE @pos1 int, @pos2 int
SET @Order =replace (REPLACE (@Order, ' ASC ', ' ASC '), ' desc ', ' desc ')
IF CHARINDEX (' DESC ', @Order) >0
IF CHARINDEX (' ASC ', @Order) >0
BEGIN
IF CHARINDEX (' DESC ', @Order) <charindex (' ASC ', @Order)
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
END
ELSE
SET @Operator = ' <= '
ELSE
SET @Operator = ' >= '
SET @SortColumn =replace (replace (@Order, ' ASC ', '), ' DESC ', '), ', ')
SET @pos1 =charindex (', ', @SortColumn)
IF @pos1 >0
SET @SortColumn =substring (@SortColumn, 1, @pos1-1)
SET @pos2 =charindex ('. ', @SortColumn)
IF @pos2 >0
BEGIN
SET @SortTable =substring (@SortColumn, 1, @pos2-1)
IF @pos1 >0
SET @SortName =substring (@SortColumn, @pos2 +1,@[email protected])
ELSE
SET @SortName =substring (@SortColumn, @pos2 +1,len (@SortColumn) [email protected]
END
ELSE
BEGIN
SET @[email protected]
SET @[email protected]
END
END
ELSE
BEGIN
SET @[email protected]
SET @[email protected]
SET @[email protected]
SET @[email protected]
SET @Operator = ' >= '
END

DECLARE @type varchar (50)
DECLARE @prec int
Select @type =t.name, @prec =c.prec
From sysobjects o
JOIN syscolumns C on o.id=c.id
JOIN systypes T on C.xusertype=t.xusertype
Where [email protected] and [email protected]
IF CHARINDEX (' char ', @type) >0
SET @[email protected]+ ' (' +cast (@prec as varchar) + ') '

DECLARE @TopRows INT
SET @[email protected]* @CurrentPage +1
Print @TopRows
Print @Operator
EXEC ('
DECLARE @SortColumnBegin ' [email protected]+ '
SET ROWCOUNT ' [email protected]+ '
Select @SortColumnBegin = ' [email protected]+ ' from ' [email protected]+ ' [email protected]+ ' [email protected]+ ' or DERBY ' [Email protected]+ '
SET ROWCOUNT ' [email protected]+ '
Select ' [email protected]+ ' from ' [email protected]+ ' [email protected]+ ' and ' [email protected]+ ' [email protected]+ ' @ Sortcolumnbegin ' [email protected]+ ' or DERBY ' [email protected]+ '
‘)
END

GO

Int. TryParse (Areaid.tostring (), out areaid);
Six 、--usage of case
Select (Case cardstatus
When 0 Then ' card release '
When 1 Then ' card postponed '
When 2 Then ' report loss '
When 3 Then ' unlock '
When 4 Then ' reissue '
When 5 Then ' refund '
When 6 and then ' pin the household '
Else ' other '
End) as ' card type ', COUNT (*) as ' quantity '
From Ccardoperation
GROUP BY Cardstatus

--sum Case-When usage
Select
SUM (case cardstatus if 0 then 1 else 0 end) as ' card release ',
SUM (case cardstatus if 1 then 1 else 0 end) as ' card postponed ',
SUM (case Cardstatus if 2 then 1 else 0 end) as ' report loss ',
SUM (case Cardstatus if 3 then 1 else 0 end) as ' unlock ',
SUM (case Cardstatus if 4 then 1 else 0 end) as ' reissue ',
SUM (case Cardstatus if 5 then 1 else 0 end) as ' refund ',
SUM (case Cardstatus If 6 then 1 else 0 end) as ' pin-user '
From Ccardoperation

Vii. Stitching Fields

Select (select COUNT (1) from ccardoperation where cardstatus=0) as ' card release ',
(select COUNT (1) from ccardoperation where Cardstatus=1) as ' card postponed ',
(select COUNT (1) from ccardoperation where cardstatus=2) as ' card lost '

Viii. Creating a cursor

Update [Vw_sale_personcard] A set a.deptid=b.deptid where exists
(
(select B.deptid from [Sale_salerecord] B on A.yktid=b.yktid)

Cursor
Declare Mycusror Cursor Scroll

For select ID, yktid from [Sale_salerecord];

Open Mycusror
DECLARE @GoodsCode int

DECLARE @GoodsName int
Fetch Next from Mycusror
Into @GoodsCode, @GoodsName

while (@ @Fetch_Status = 0)
Begin

Begin
Update [Sale_salerecord] set deptid= (select DeptID from [Vw_sale_personcard] as a where [email protected]) where [email PR Otected]
End

Fetch Next from Mycusror
Into @GoodsCode, @GoodsName

End
Close Mycusror
Deallocate Mycusror

---

Medium Difficulty SQL statements (stored procedures, pagination, splicing fields, cursors)