Users - Search - SQLHTTP

Users - Search

Search for public user accounts on Twitter using SQL Server

Prerequisites:

See SQLHTTP easy setup for Twitter
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_Twitter_Users_Search(	
		@Profile nvarchar(100),
		@query varchar(500),
		@page bigint,
		@count bigint,
		@include_entities bit = 0,
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @Text_include_entities varchar(5)

SET @Text_include_entities = 
    CASE
       WHEN @include_entities = 0 THEN 'false'
<span style="color: #ff0000">       ELSE 'true'
<span style="color: #ff0000">    END
 
DECLARE @URL nvarchar(MAX)
DECLARE @QueryString varchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.twitter.com/1.1/users/search.json'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'q', @query,
				      'page', @page,
				      'include_entities', @Text_include_entities,
				      'count', @count

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Twitter_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET'

EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID, 
				@URL = @URL,
				@Method = 'GET',
				@ContentType = 'application/json',
				@Accept = 'application/json',
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_Twitter_Users_Search(

@Profile nvarchar(100),

@query varchar(500),

@page bigint,

@count bigint,

@include_entities bit = 0,

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @Text_include_entities varchar(5)

SET @Text_include_entities =

CASE

WHEN @include_entities = 0 THEN 'false'

<span style="color: #ff0000"> ELSE 'true'

<span style="color: #ff0000"> END

DECLARE @URL nvarchar(MAX)

DECLARE @QueryString varchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.twitter.com/1.1/users/search.json'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'q', @query,

'page', @page,

'include_entities', @Text_include_entities,

'count', @count

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Twitter_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET'

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'GET',

@ContentType = 'application/json',

@Accept = 'application/json',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Twitter_Users_Search
		@Profile = 'My Twitter',
		@query = 'Twitter API',
		@page = '1',
		@count = '5',
		@Response = @Response OUTPUT, 
	  	@StatusCode = @StatusCode OUTPUT, 
	 	@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN

		DECLARE @X xml
		SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonArray/JsonObject'

		SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id]
		      ,T.C.value(N'@name', N'nvarchar(MAX)') AS [name]
		      ,T.C.value(N'@listed_count', N'nvarchar(MAX)') AS [listed_count]
		      ,T.C.value(N'@followers_count', N'nvarchar(MAX)') AS [followers_count]
		      ,T.C.value(N'@location', N'nvarchar(MAX)') AS [location]
		FROM @X.nodes(N'/JsonArray/JsonObject') T(C)

	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Twitter_Users_Search

@Profile = 'My Twitter',

@query = 'Twitter API',

@page = '1',

@count = '5',

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonArray/JsonObject'

SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id]

,T.C.value(N'@name', N'nvarchar(MAX)') AS [name]

,T.C.value(N'@listed_count', N'nvarchar(MAX)') AS [listed_count]

,T.C.value(N'@followers_count', N'nvarchar(MAX)') AS [followers_count]

,T.C.value(N'@location', N'nvarchar(MAX)') AS [location]

FROM @X.nodes(N'/JsonArray/JsonObject') T(C)

END

id name listed_count followers_count location

------------ ------------------ -------------- ---------------- -------------------

6253282 Twitter API 13037 6231477 San Francisco, CA

1225933934 Twitter Ads API 102 12912 San Francisco

141247659 Dropbox API 337 17377 San Francisco, CA

1564061 Perl API library 99 994

777925 Matt Harris 309 9513 United States



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.