Difficult SQL problems in the Forum: 17 (character split 2)

Recently, I encountered a lot of difficult SQL problems in the forum. Although I could solve them myself, I found that I couldn't remember them a few days later and forgot the solution. Therefore, I think it is necessary to record the issue. In this way, I will encounter such problems again in the future, and I will be able to get answers from them. 1. Stored Procedure Table A: aidbidstatus110120210

Recently, I encountered a lot of difficult SQL problems in the forum. Although I could solve them myself, I found that I couldn't remember them a few days later and forgot the solution. Therefore, I think it is necessary to record the issue. In this way, I will encounter such problems again in the future, and I will be able to get answers from them. 1. Stored Procedure Table A: aid bid status 1 1 0 1 2 0 2 1 0

Recently, I encountered a lot of difficult SQL problems in the forum. Although I could solve them myself, I found that I couldn't remember them a few days later and forgot the solution.

Therefore, I think it is necessary to record the issue. In this way, I will encounter such problems again in the future, and I will be able to get answers from them.

1. Stored Procedure

Table:
Aid bid status
1 1 0
1 2 0
2 1 0
1 111 0
11 11 0

Each data aid combination bid is unique. How to Write a stored procedure for batch operations.

Input [{aid: 1, bid: 2}, {aid: 11, bid: 11}]
The following table is queried:

Aid bid status
1 2 0
11 11 0

Table creation statement:

CREATE TABLE A(aid      INT, bid      INT, statuss  INT )INSERT A SELECT 1,1,0 UNION ALLSELECT 1,2,0 UNION ALL            SELECT 2,1,0 UNION ALLSELECT 1,111,0 UNION ALLSELECT 11,11,0go

-- 1. string split function if exists (select * from sys. objects where name = 'f _ splitSTR 'and type = 'tf') drop function dbo. f_splitSTRgocreate function dbo. f_splitSTR (@ s varchar (8000), -- string to be split @ split varchar (10) -- delimiter) returns @ re table (-- temporary table col varchar (1000) to be returned) -- columns in the temporary table) asbegin declare @ len int set @ len = LEN (@ split) -- The Delimiter is not necessarily a single character. It may be two characters while CHARINDEX (@ split, @ s)> 0 begininsert into @ re values (left (@ s, charindex (@ split, @ s)-1) set @ s = STUFF (@ s, 1, charindex (@ split, @ s)-1 + @ len, '') -- overwrite: string and separator end insert into @ re values (@ s) return -- return the temporary table endgo create proc dbo. pro_A @ param varchar (100) AS declare @ str varchar (100) declare @ SQL nvarchar (4000) set @ str = ''set @ SQL = ''if OBJECT_ID ('tempdb .. # temp ') is not null drop table # temp -- Insert the split field to the temporary table select LEFT (col, charindex (', ', col)-1) as aid, SUBSTRING (col, charindex (',', col) + 1, LEN (col) as bid into # tempfrom dbo. f_splitSTR (@ param, ';') t -- generate A dynamic statement set @ SQL = 'select * FROM A WHERE exists (SELECT 1 from # temp where # temp. aid =. aid and # temp. bid =. bid) 'exec (@ SQL) goexec Pro_A '1, 2; 11,11'/* aidbidstatuss12011110 */

Another method:

If object_id ('dbo. pro_A ') is not null drop proc pro_agcreate PROC dbo. pro_A @ param varchar (100) AS declare @ str varchar (100) declare @ SQL nvarchar (4000) set @ str = @ paramset @ SQL = ''set @ str = replace (REPLACE (@ str, '[', ''), ']', ''),'},{',';'),'{',''),'}','') set @ str = 'select' + replace (@ str, ';', 'Union select'), ':', '=') if OBJECT_ID ('tempdb .. # temp ') is not null drop table # tempCREATE TABLE # temp (aid INT, bid INT) -- insert data into the temporary table into insert into # temp (aid, bid) exec (@ str) -- generate A dynamic statement set @ SQL = 'select * FROM A WHERE exists (SELECT 1 from # temp where # temp. aid =. aid and # temp. bid =. bid) 'exec (@ SQL) -- print @ strgoexec Pro_A '[{aid: 1, bid: 2}, {aid: 11, bid: 11}] '/* aidbidstatuss12011110 */